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

As usual in this group this is a post with way too few clues. What we would need is
- the exact version of Oracle you are running (the answer will definitely depend on this) - whether you are using the cost-based or the rule based optimizer - the exact explain plan of the affected statements.

This group is a volunteer business. Answering on such broad generic questions without an adequate background would result in abstracting a tuning book.
In that case it would be better you either read the Oracle Performance Tuning Manual, or the books of Richard Niemic and/or Guy Harrison, both of which contain discussions on this type of construct. Right now, any answer is a shot in the dark.

Regards,

Sybrand Bakker, Oracle DBA

"Frank Andersen" <frank.andersen_at_oslo.online.no> wrote in message news:hWZK5.2617$Yy1.49240_at_news1.online.no...
> 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 Sun Oct 29 2000 - 13:50:11 CST