Re: IN vs NOT IN

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.

Try re-phrasing your SQL as MINUS or NOT EXISTS and you may get some benefit...

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)

"Some days you're the pigeon, some days you're the statue"