John D. Burger wrote:
>> select *
>> from people
>> where id not in
>> (
>> select id
>> from class_registration
>> )
>
> In my experience, queries like the OUTER LEFT JOIN version posted
> earlier are usually much more efficient than NOT IN queries like the
> above. The planner seems to be pretty smart about turning (positive)
> IN queries into joins, but NOT IN queries usually turn into nested
> table scans, in my experience.
Interesting, I am aware that each DBMS query optimizer does better with
some expressions, and worse with others. When I was a DB2 DBA, DB2 would
change from release to release the expressions it most preferred.
I imagine the above formulation is what many people would try initially,
until they encounter experiences such as yours. I checked the TO-DO
list and I don't see anything pending to address this. Bruce and/or
Tom, are there any far-off intentions to do anything to improve "not in"
execution? Or perhaps to rewrite it to an equivalent expression that
already works well?
--
Guy Rouillier