Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: IN vs NOT IN
Susan,
Matt was right on target with the explanation of why your NOT IN query runs forever. I have a suggestion for another way to do it that might be faster: use MINUS, as follows:
select count(*) from
(select objectid from struct
MINUS
select objectid from detail where status != 0);
Logically, this is the same thing: it will give you the count of objectid's in struct that are not found in DETAIL with a STATUS other than 0.
This will still do a full table scan of both tables, which is unavoidable. However, the query above will only have to do a full table scan on each table ONCE, whereas in your NOT IN query, it was doing a full scan on DETAIL for each row in STRUCT, which was why it ran forever.
Give it a try. Let me know if it helps!
-Dave
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Thu Dec 07 2000 - 12:52:32 CST