RE: antijoin

select distinct id
from (select id from table_a minus select id from table_b)
/
 

should also work better in most cases compared to exists or not in.  

Raj

-----Original Message-----
Sent: Saturday, August 16, 2003 5:39 PM
To: Multiple recipients of list ORACLE-L

hi  

i am trying to figure out which is a more efficient method to do an antijoin between two tables. The version is 9i and optimiser is choose.stats are the lates.  

here is the query with not in and not exitsts: case 1:
select distinct a.id from tab a
where a.id not in (select b.id from b)
/

case 2:
select distinct a.id from tab a
where not exists (select b.id from b where .id=a.id)
/
 

the subquery returns 4.8 mill rows in the first case and the o/p never comes out in the first case.
the second case works better and gives me a result. but what is interesting is the explain plan is the same.  

can some explain this to me? the other question i have will the second query behave the same way if the sub-query returns 4.8 mill records. is there a better way to write this code. it is find distinct ids in tab which dont exist in b.bot tables are bound to grow. at this moment tab has 10000 rows and b 4.8 million rows.
thanks
sai  

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



text/plain attachment: ESPN_Disclaimer.txt