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 - 19:52:32 CET