Re: Why does NOT IT take so long

An operator is quicker...try minus.
Craig

In <3vaat9$j6v_at_inet-nntp-gw-1.us.oracle.com> dharris_at_uk.oracle.com (Dave Harris) writes:
>
>snowden_at_haven.ios.com (Roger Snowden) wrote:
>
>>Jim Smith (Jim_at_jimsmith.demon.co.uk) wrote:
>>: 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?  

>>: ... where this_id not in
>>: (select other_id from other table...)
 

>>I wonder if you could clarify this for me. I have a similar
 situation
>>where the sub-query takes less than two seconds in sqlplus to get 250
 

>>rows. The main table has about 100,000 rows. Does the subquery
 actually
>>execute for each row of the main table? For example:
 

>>select count(*) from dbown.donor where
>>dbown.donor.donorid not in (
>>select * from dbown.flagview)
 

>>dbown.flagview only has 250 rows. Does this not get chached and
 compared
>>to dbown.donor?
 

>>Roger
>
>the example:
>
>select count(*) from dbown.donor where
>dbown.donor.donorid not in (
>select * from dbown.flagview)
>
>this sql statement will cause 2 full table scans.
>the sql will be executed as follows:
>
>for every dbown.donor row perform a full table scan on
>dbown.flagview
>
>try using:
>
>select count(*)
>from dbown.donor d
>where not exists
> (select 1
> from dbown.flagview f
> where d.donorid = f.donorid)
>
>if the donorid is indexed on the dbown.flagview table then a full
>table scan can be avoided.
>
>Dave Harris
>
Received on Fri Jul 28 1995 - 00:00:00 CEST