Monday, January 19, 2009

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"

#

No comments: