IN vs NOT IN

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 Thu Dec 07 2000 - 11:31:56 CST