Anti-Join - A test case showing difference between NOT IN and NOT EXISTS on a 9i database

This test case is built based on Tom Kyte's book "Effective Oracle by Design" p477.

Summary of logical reads of anti-join queris when using deffierent structure

---------------------------------------------------------
Structure    NOT IN       NOT EXISTS      Outer join
---------------------------------------------------------
9i               160           20133          160
10g              155            155            155
----------------------------------------------------------

 The test case is as follows: 

 
rem script: anti_join_cbo.sql
rem    
rem    anti-join: used to return rows from a table that are not 
rem               present in some other row source
rem 

set echo on
drop table t1;
drop table t2;

create table t1 as select *
from all_objects where rownum <=10000;


create table t2 as select *
from all_objects where rownum <=9950;

create index t2_idx on t2(object_id);



begin
      dbms_stats.gather_table_stats(
            user,
            't1',
           cascade => true
      );
end;
/

begin
      dbms_stats.gather_table_stats(
            user,
            't2',
           cascade => true
      );
end;
/

alter session set tracefile_identifier = anti_join;
alter session set timed_statistics=true;
alter session set events '10046 trace name context forever, level 12';

-- 1. NOT IN
select count(*) from t1 cbo
where object_id not in (select object_id from t2);


-- 2. NOT EXISTS

select count(*) from t1 cbo
where not exists (select null from t2 where t2.object_id = cbo.object_id );


-- 3. OUTER JOIN

select count(*) from t1, t2 cbo
where  t1.object_id = cbo.object_id(+) 
 and  cbo.object_id is NULL;


alter session set events '10046 trace name context off'; 
exit;

set doc off
doc

------- 9.2.0.8   optimizer_mode=choose  ------

1. NOT IN

   select count(*) from t1 cbo
   where object_id not in (select object_id from t2)

   call     count cpu  elapsed  disk  query  current   rows
   ------- ------ ---- -------- ----- ------ --------  -----
   Parse        1 0.01     0.00     0      0        0      0
   Execute      1 0.00     0.00     0      0        0      0
   Fetch        2 0.04     0.03     0    160        0      1
   ------- ------ ---- -------- ----- ------ --------  -----
   total        4 0.05     0.03     0    160        0      1

   Misses in library cache during parse: 1
   Optimizer goal: CHOOSE
   Parsing user id: 178

   Rows     Row Source Operation
   -------  ---------------------------------------------------
  1  SORT AGGREGATE
 50   HASH JOIN ANTI
     10000    TABLE ACCESS FULL T1
      9950    INDEX FAST FULL SCAN T2_IDX (object id 443635)





2. NOT EXISTS

select count(*) from t1 cbo
where not exists (select null from t2 where t2.object_id = cbo.object_id )

call     count    cpu  elapsed   disk  query current   rows
------- ------  ----- -------- ------ ------ -------  -----
Parse        1   0.00     0.00      0      0       0      0
Execute      1   0.00     0.00      0      0       0      0
Fetch        2   0.17     0.16      0  20133       0      1
------- ------  ----- -------- ------ ------ -------  -----
total        4   0.17     0.16      0  20133       0      1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 178

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE
     50   FILTER
  10000    TABLE ACCESS FULL T1
   9950    INDEX RANGE SCAN T2_IDX (object id 443635)


3. OUTER JOIN

select count(*) from t1, t2 cbo
where  t1.object_id = cbo.object_id(+)
 and  cbo.object_id is NULL

call     count    cpu   elapsed  disk  query  current   rows
------- ------  ----- --------- ----- ------ --------  -----
Parse        1   0.01      0.00     0      0        0      0
Execute      1   0.00      0.00     0      0        0      0
Fetch        2   0.04      0.04     0    160        0      1
------- ------  ----- --------- ----- ------ --------  -----
total        4   0.05      0.04     0    160        0      1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 178

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE
     50   FILTER
  10000    HASH JOIN OUTER
  10000     TABLE ACCESS FULL T1
   9950     INDEX FAST FULL SCAN T2_IDX (object id 443635)

------- 10.2.0.1   optimizer_mode=all_rows  ------


1. NOT IN 
   select count(*) from t1 cbo
   where object_id not in (select object_id from t2)

   call     count    cpu  elapsed disk   query  current  rows
   ------- ------  ----- -------- ---- ------- --------  ----
   Parse        1   0.01     0.11    0       0        0     0
   Execute      1   0.00     0.03    0       0        0     0
   Fetch        2   0.01     0.02    0     155        0     1
   ------- ------  ----- -------- ---- ------- --------  ----
   total        4   0.03     0.17    0     155        0     1

   Misses in library cache during parse: 1
   Optimizer mode: ALL_ROWS
   Parsing user id: 62

   Rows     Row Source Operation
   -------  ---------------------------------------------------
  1  SORT AGGREGATE (cr=155 pr=0 pw=0 time=25632 us)
 50   HASH JOIN ANTI (cr=155 pr=0 pw=0 time=25386 us)
     10000    TABLE ACCESS FULL T1 (cr=129 pr=0 pw=0 time=30170 us)
      9950    INDEX FAST FULL SCAN T2_IDX (cr=26 pr=0 pw=0 time=29952 us)(object id 58211)

2. NOT EXISTS

    Same as "NOT IN"


3. Outer join

    Same as "NOT IN"

#