Re: NOT IN is very inefficient

not in will do full table scan, use not exists!

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
Received on Mon Sep 28 1998 - 00:00:00 CDT