First page Back Continue Last page Graphics

Nested Loops Example


Notes:

This TKPROF excerpt shows that CBO chose a nested loops join for this query, with table SMALL as the driving table. Note the filter on the small table produces just 26 rows from the small table and just 13312 rows in the result set. With this relatively small number of rows, CBO chose the nested loops join to skip the overhead of the pre-processing associated with hash and sort merge joins (we will cover hash and sort merge joins later in this lesson). Without the filter, CBO chooses a hash join:
select small.created, big.owner
from small, big
where small.object_id=big.object_id
Rows Row Source Operation
------- --------------------------------------------
486400 HASH JOIN
1000 TABLE ACCESS FULL SMALL
486400 TABLE ACCESS FULL BIG
Refer to the supplied files nested_loops.sql, nested_loops_trace.txt and nested_loops10053.sql for a demonstration of this and other nested loops concepts.