Re: IN vs NOT IN

Just accept the fact the NOT IN is probably the all-time slowest test and you should avoid using it.

Cheers,
BB

In article <90ohic$n8h$1_at_nnrp1.deja.com>,   susana73_at_hotmail.com wrote:
> Hi there,
>
> I have 2 queries which have same logical meaning. However, the one
 with
> 'NOT IN' takes forever and the one with 'IN' takes just couple
 seconds.
> I did a EXPLAIN on them and it looks like the one with 'NOT IN' is
 doing
> a full table scan for both table that's why it takes so long. Could
> anyone explain to me why?
>
> There are about 100k objectid.
>
> Thanks in advance.
>
> Susan
>
> ********** Take forever **********
> SQL> select count(*) from struct where objectid NOT IN
> 2 (select objectid from detail where status !=0);
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 SORT (AGGREGATE)
> 2 1 FILTER
> 3 2 TABLE ACCESS (FULL) OF 'STRUCT'
> 4 2 TABLE ACCESS (FULL) OF 'DETAIL'
>
> ********** Take 30 seconds **********
> SQL> select count(*) from struct where objectid IN
> 2 (select objectid from detail where status = 0);
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 SORT (AGGREGATE)
> 2 1 NESTED LOOPS
> 3 2 VIEW OF 'VW_NSO_1'
> 4 3 SORT (UNIQUE)
> 5 4 TABLE ACCESS (FULL) OF 'OBJECT'
> 6 2 INDEX (RANGE SCAN) OF 'PKEY_STRUCT_IDX' (NON-UNIQU
> E)
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Dec 07 2000 - 19:38:55 CET