Re: IN vs NOT IN

Hi Susan

Instead of NOT IN, which should be avoided at all costs (as should NOT EXISTS if possible) try:

select count(*)
  from struct S, detail D

 where S.objectid = D.objectid (+)
   and D.status (+) != 0
   and D.objectid IS NULL;

If you can construct your query using outer joins like this you will improve perfomance by orders of magnitude.
For more examples of this technique see Chapter 11 of 'Oracle8: The Complete Reference' by Koch & Loney (Oracle Press)

Regards

Nick

<susana73_at_hotmail.com> wrote in message news:90ohic$n8h$1_at_nnrp1.deja.com...
> 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.
Received on Sat Dec 09 2000 - 06:16:35 CST