Re: antijoin

They are not likely using the same execution paths.

Try running this statement first:

alter session set events '10046 trace name context forever, level 8';

and then run your queries.

Be sure to first run the one that will return rows, in case you need to stop the other query.

The trace file should contain both execution paths.

In general, the 'not exists' is good for queries that are returning only a few rows from a table, as long as the table is properly indexed.

'not in' would be more useful where a large part of the table is being read anyway, and an FTS is the best execution plan.

Depending on the version of Oracle and accompanying optimizer, the execution paths may differ.

Just for fun, try this form:

select distinct a.id
from tab a, b
where a.id = b.id(+)
and b.id is null;

Also, why are you using the 'distinct'?

Jared

On Sat, 2003-08-16 at 14:39, Sai Selvaganesan wrote:
> 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: Jared Still
  INET: jkstill_at_cybcon.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).