Re: IN vs NOT IN

Hello Susan,

I don't have an answer to your question, but I DO know, that the behaviour of the optimizer is sometimes strange.

In stead of IN / NOT IN, you could use (NOT) EXISTS, and the queries may (not always, strange enough!) be much faster.

So, in your case, try the following:

SELECT COUNT(*)
FROM struct
WHERE EXISTS
   (SELECT 1
   FROM detail
   WHERE status = 0
   );

or

SELECT COUNT(*)
FROM struct
WHERE NOT EXISTS
   (SELECT 1
   FROM detail
   WHERE status != 0
   );

Dick Doornbos
The Netherlands

<susana73_at_hotmail.com> schreef in bericht 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 Thu Dec 07 2000 - 12:32:27 CST