Re: NOT IN is very inefficient

On Thu, 24 Sep 1998 22:06:41 GMT, 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).

What is you do an outer join, filter the result set to just those rows where the second table's key column contains NULL, and leave all of the second table's columns out of the SELECT clause?

  SELECT T1.my_field
  FROM my_table1 T1
    LEFT OUTER JOIN my_table2 T2
      ON (T1.my_field = T2.my_field)
  WHERE (T2.my_field IS NULL)

Joins are often faster in execution than a NOT IN query (not speaking for any particular database brand or version, just in general).

//////////////////////////////////////////////////////////////////////////
Steve Koterski                      "The knowledge of the world is only to
Technical Publications              be acquired in the world, and not in a
INPRISE Corporation                 closet."
http://www.inprise.com/delphi          -- Earl of Chesterfield (1694-1773)