Re: Why does NOT IT take so long

In <806530334snz_at_jimsmith.demon.co.uk> Jim Smith <Jim_at_jimsmith.demon.co.uk> writes:
>
>In article <3uu80q$2nj_at_explorer.csc.com> tconder_at_csc.com "Tom Conder"
 writes:
>
>> When writing Oracle SQL, why does the NOT IN statement take so long?
>>
>>
>I assume you mean something like
>
>... where this_id not in
> (select other_id from other table...)
>
>It has to process the entire sub-query. Depending on other parts of
 your
>query it may process the sub-query once for each row returned by the
 outer
>query.
>
>The performance of the sub-query will depend on its where clause (if
 any)
>and on whether or not the query can use an index

You can always rewrite a NOT IN query as a WHERE NOT EXISTS query too, which will run noticable faster. An example would be...

SELECT *
FROM my_table mt
WHERE NOT EXISTS (SELECT 'x' FROM other_table WHERE ot.primary_key = mt.foreign_key);

-- 
Chuck Hamilton
chuckh_at_ix.netcom.com

Incoming fire has the right of way!