Database Reference
In-Depth Information
-------------------------------------
| 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 |
-------------------------------------
1 - access("T3"."ID"="T4"."T3_ID")
2 - access("T2"."ID"="T3"."T2_ID")
3 - access("T1"."ID"="T2"."T1_ID")
4 - filter("T1"."N"=19)
The processing of this type of execution plan is summarized here:
t1
is read through a full scan, the
n = 19
restriction is applied, and a hash table
containing the resulting rows is created.
•
Table
t2
is read through a full scan, and the hash table created in the previous step is probed.
Then a hash table containing the resulting rows is created.
•
Table
t3
is read through a full scan, and the hash table created in the previous step is probed.
Then a hash table containing the resulting rows is created.
•
Table
t4
is read through a full scan, and the hash table created in the previous step is probed.
The resulting rows are returned. The first row can be returned only when the
t1
,
t2
, and
t3
tables have been fully processed. Instead, it isn't necessary to fully process table
t4
in order to
return the first row.
•
Table
One peculiar property of hash joins is that they also support right-deep and zig-zag trees. The following execution
plan is an example of the former (refer to Figure
14-3
for a graphical representation). Compared to the previous
example, only the hints specified in the SQL statement are different. Notice that in this case, the
leading
hint doesn't
directly specify the order in which the tables are accessed (which is
t1
➤
t2
➤
t3
➤
t4
). Instead, it specifies the order
before applying the
swap_join_inputs
hints that request to swap the left and right inputs:
SELECT /*+ leading(t3 t4 t2 t1) use_hash(t1 t2 t4) swap_join_inputs(t1)
swap_join_inputs(t2) */ t1.*, t2.*, t3.*, t4.*
FROM t1, t2, t3, t4
WHERE t1.id = t2.t1_id
AND t2.id = t3.t2_id
AND t3.id = t4.t3_id
AND t1.n = 19
