Re: NOT IN is very inefficient

In article <6uefph$7ii$1_at_nnrp1.dejanews.com>,   bjohnsto_usa_net_at_my-dejanews.com wrote:
>
>
> I have a query:
>
> SELECT my_field FROM my_table1
> WHERE my_field NOT IN
> (SELECT my_field FROM my_table2)
>
> Oracle (v7.3.4) is doing a table scan of the my_table2 (and my_table1) even
> though there is an index on table2. Performance is terrible.
> If I replace NOT IN with IN the performance is fine.
>
> Is Oracle doing something stupid or am I? How can I get the results a smarter
> (faster) way? Is there something inherent in NOT IN which would stymie all
> databases or is the Oracle optimiser uniquely limited (again).
>
> Brendan Johnston
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>

The ORACLE Optimizer is a program, so by definition it is stupid. But you can get it to behave better. The NOT IN clause is one of the easiest performance problems to fix. Simply rephrase the query to use NOT EXISTS:

SELECT my_field FROM my_table1
WHERE NOT EXISTS

      ( SELECT 'ANYTHING' FROM my_table2
        WHERE my_table2.my_field = my_table1.my_field ) ;

Now the subquery can use any index that exists on my_field for my_table2 to speed the lookup. You can also rephrase the query using an Outer Join:

SELECT a.my_field FROM my_table1 a, my_table2 b WHERE a.my_field = b.my_field(+)
  AND b.my_field IS NULL ;

The syntax looks convoluted, but if you read it carefully you can see the meaning. Just think about what Outer Join does. Again, this enables the Optimizer to use indices on the lookup.

Keep these forms of query handy. You never know when you'll need them.

Happy Optimizing!

--
Ed Prochak
Magic Interface, Ltd.
440-498-3702

-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/rg_mkgrp.xp   Create Your Own Free Member Forum