Re: Why does NOT IT take so long

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