Thank you for the feedback.
The short answer to your question is that we may not have all possible transformations and your example is one transformation we don’t have in the product. We love to hear suggestions on this area so that we can keep improving the optimization capability to better help our users to solve SQL problem.
The long answer is that when we consider transformation, we will concern if the changes can be applied to all scenarios or there will be special cases where the transformed SQL is not equivalent to the original SQL. In other words, we will have to make sure a transformation will return the same data as your original SQL or else we will not transform it. In your specific example, we cannot make this transformation because the second SQL is not necessary equivalent to the first SQL. I changed your second SQL to the below to show what in the data of table_a and table_b:
with table_a as (select 1 id from dual
select 1 from dual),
table_b as (select 1 id from dual
select 1 from dual
select 2 from dual)
select a.id from table_a a where exists
(select 1 from table_b b where a.id = b.id)
If you run this SQL, you will see that 2 duplicate rows will be returned. You first SQL (with the DISTINCT option) guarantees that there is no duplicate rows but the second SQL doesn’t really enforce this. If you added back the DISTINCT in your second SQL, then you probably will see a similar plan (and performance) as your first SQL.
Hope this explains why this particular transformation is not in our product.