Toad World® Forums

Really, No Better SQL?

I’m using SQL Optimizer for Oracle 8.7.1. Entered a query. It suggested a wack of alternatives. But nothing better. How can this be?. I know a better one on my own. Shown below. Why did this tool not know this and suggest this?

(cost 1035)

select distinct a.id from table_a a
join table_b b on a.id = b.id

(cost 325)

select a.id from table_a a where exists
(select 1 from table_b b where a.id = b.id)

Hi Van,

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
union all
select 1 from dual),
table_b as (select 1 id from dual
union all
select 1 from dual
union all
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.

Thanks,

Alex

Thanks for your response. I understand your explanation. Would the fact that the id column I refer to in my queries is the primary key in both tables a and b be something to make this suggestion possible for the alternative engine to see and thus create? Both of my queries did produce the same number of records. I should have mentioned this in my original post.

With the id column being the primary key (or being under a unique constraint) will definitely make the second statement equivalent to the first one. Actually, you can also simply remove the DISTINCT in the first SQL.

To answer your question: No, you still would not see this transformation generated even the id column is primary key as this transformation is not in the product. This is what I was trying to say in the “short” answer. I guess my “long” answer have confused you. Sorry about the confusion.