Database Reference
In-Depth Information
As described in Chapter 10, the
HASH JOIN
operation is of type unrelated combine. This means that the two
children are processed at most once and independently of each other. In this case, the processing of the execution
plan can be summarized as follows:
•
All rows of table
t1
are read through a full scan, the
n = 19
restriction is applied, and a hash
table is built with the resulting rows. To build the hash table, a hash function is applied to the
columns used as the join condition (
id
).
All rows of table
•
t2
are read through a full scan, the hash function is applied to the columns
used as the join condition (
t1_id
), and the hash table is probed. If a match is found, the
resulting row is returned.
The most important limitation of the
HASH JOIN
operation (as for other unrelated-combine operations) is the
inability to take advantage of indexes to apply join conditions. This means that indexes can be used as the access path
only if restrictions are specified. Consequently, in order to choose the access path, it's necessary to apply the methods
discussed in Chapter 10 to both tables. For instance, if the
n = 19
restriction provides strong selectivity, it could be
useful to create an index like the following one to apply it:
CREATE INDEX t1_n ON t1 (n)
In fact, with this index in place, the following execution plan might be used. Note that table
t1
is no longer
accessed through a full table scan:
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 |
|* 3 | INDEX RANGE SCAN | T1_N |
| 4 | TABLE ACCESS FULL | T2 |
---------------------------------------------
1 - access("T1"."ID"="T2"."T1_ID")
3 - access("T1"."N"=19)
Four-Table Joins
The following execution plan is an example of a typical left-deep tree implemented with hash joins (refer to
Figure
14-2
for a graphical representation). The example also shows how to force a hash join by using the
leading
and
use_hash
hints:
SELECT /*+ leading(t1 t2 t3 t4) use_hash(t2 t3 t4) */ 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
