Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Performance of (NOT) IN vs. (NOT) EXISTS

Re: Performance of (NOT) IN vs. (NOT) EXISTS

From: whoever <fred_at_nowhere.net>
Date: Mon, 30 Oct 2000 08:03:23 -0600
Message-ID: <8tjv3b$c6$0@dosa.alt.net>

Depends on what you are trying to do here, but can you use an outer join A=B(+) and checking where objectid=NULL ? This should be considerably faster than NOT IN.

Frank Andersen wrote in message ...
>Hi all,
>
>I have a query where I want to select records from (A) that exist (and
>in a later query, don't exist) in another table (B) like:
>
>SELECT COUNT(*) FROM A
>WHERE OBJECTID IN (
> SELECT FK_OBJEKT_ID
> FROM B);
>
>This works fine and is actually faster than:
>
>SELECT COUNT(*) FROM A
>WHERE EXISTS (
> SELECT 1
> FROM B
> WHERE B.FK_OBJECTID = A.OBJECTID);
>
>A.OBJECT_ID is PK and I have an index on B.FK_OBJECT_ID.
>
>However, if I want the records from A that don't exist in B I run into
>problems using NOT IN like:
>
>SELECT COUNT(*) FROM A
>WHERE OBJECTID NOT IN (
> SELECT FK_OBJEKT_ID
> FROM B);
>
>This statement takes forever to execute and the optimizer doesn't seem
>to use a temporary, indexed collection of FK_OBJECTID values, but
>rather using a full scan for each record in A. If the collection of
>FK_OBJECTID's was ordered/indexed, it really should not take more time
>to do a NOT IN than an IN. Btw, NOT EXISTS uses appr. the same amount
>of time as EXISTS because it is based on the same join, but I would
>appreciate if someone could shed some light on this NOT IN issue and
>its lack of performance.
>
>Cheers,
>Frank
>
>
Received on Mon Oct 30 2000 - 08:03:23 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US