Re: NOT IN is very inefficient

Use Exists instead of IN cluase.

Select my_field from my_table1 A
 where not exists
( select 1 from my_table2 B

    where A.my_field = B.my_field)

It will be faster than before query.

Thanks.

Young-chul,Jun
ycjun_at_hotmail.com
OCP bjohnsto_usa_net_at_my-dejanews.com ÀÌ(°¡) <6uefph$7ii$1_at_nnrp1.dejanews.com> ¸Þ½ÃÁö¿¡¼­ ÀÛ¼ºÇÏ¿´½À´Ï´Ù...
>
>
>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
Received on Fri Sep 25 1998 - 00:00:00 CDT