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
