First page Back Continue Last page Graphics

Nested Loops Anti-Join

Notes:

This TKPROF excerpt shows the use of a nested loops anti-join. Removing the filter on OBJECT_TYPE produces a hash anti-join:
select /* example2 */ small.object_id, small.owner
from small
where small.object_id not in
(select big.object_id from big)
call count cpu elapsed disk query
------- ------ -------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0
Execute 1 0.00 0.00 0 0
Fetch 5 0.48 0.48 0 1152
------- ------ -------- ---------- ---------- ----------
total 7 0.48 0.48 0 1152
Rows Row Source Operation
------- ------------------------------------------------
50 HASH JOIN ANTI
1000 TABLE ACCESS FULL SMALL
486400 INDEX FAST FULL SCAN BIG_OBJECT_ID (object id 33

Refer to the supplied script anti_joins.sql for a copy of this demonstration and anti_joins_trace.txt for a copy of my TKPROF output.