Database Reference
In-Depth Information
When more than two sets of data must be joined, the query optimizer evaluates
join trees
. The types of join trees
employed by the query optimizer are described in the next four sections.
Left-Deep Trees
A
left-deep tree,
as shown in Figure
14-2
, is a join tree where every join has a table (that is, not a result set generated by
a previous join) as its right input. This is the join tree most commonly chosen by the query optimizer.
Figure 14-2.
In a left-deep tree, the right input is always a table
The following execution plan illustrates the join tree depicted in Figure
14-2
. Note that the second child (that is,
the right input) of each join operation (that is, lines 5, 6, and 7) is always a table:
-------------------------------------
| Id | Operation | Name |
-------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN | |
| 2 | HASH JOIN | |
| 3 | HASH JOIN | |
| 4 | TABLE ACCESS FULL| T1 |
| 5 | TABLE ACCESS FULL| T2 |
| 6 | TABLE ACCESS FULL | T3 |
| 7 | TABLE ACCESS FULL | T4 |
-------------------------------------
