RE: antijoin

[Jamadagni, Rajendra]

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-----
From: Sai Selvaganesan [mailto:[EMAIL PROTECTED]
Sent: Saturday, August 16, 2003 5:39 PM
To: Multiple recipients of list ORACLE-L
Subject: antijoin

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

 

*********************************************************************This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*********************************************************************1