Database Reference
In-Depth Information
Choosing the Join Method
To choose a join method, you must consider the following issues:
•
The optimizer goal—that is, first-rows and all-rows optimization
•
The type of join to be optimized and the selectivities of the predicates
•
Whether to execute the join in parallel
The next sections discuss, based on these three criteria, how to choose a join method—or more specifically,
how to choose among a nested loops join, a merge join, and a hash join.
First-Rows Optimization
With first-rows optimization, the overall response time is a secondary goal of the query optimizer. The response time
to return the first rows is far and away its most important goal. Therefore, for a successful first-rows optimization, joins
should return rows as soon as the first matches are found and not after all rows have been processed. For this purpose,
nested loops joins are often the best choice. Hash joins, which support partial execution to only some extent, are
useful now and then. In contrast, merge joins are rarely suitable for a first-rows optimization.
All-Rows Optimization
With an all-rows optimization, the response time to return the last row is the most important goal of the query
optimizer. Therefore, for a successful all-rows optimization, joins should be completely executed as fast as possible.
To choose the best join method, it must be considered whether, to reduce the number of logical reads to the minimum,
it's necessary to take advantage of an index to apply the join condition. If applying the join condition through an index
is necessary, a nested loops join has to be used. Otherwise, a hash join is often the best choice. Generally speaking,
merge joins are considered only when either the result sets are already sorted or hash joins can't be used because
of technical limitations (see the next section). Another case where a merge join could be interesting is when a sort
operation can be avoided. This happens when the merge join returns the rows in the order specified by the
ORDER BY
clause.
Supported Join Methods
To choose a join method, you have to know which type of join has to be executed. In fact, not all join methods support
all types of joins. Table
14-1
summarizes which methods are available in which situation.
Table 14-1.
Types of Joins Supported by Each Join Method
Join
Nested Loops Join
Hash Join
Merge Join
Cross join
•
•
Theta join
•
•
Equi-join
•
•
•
Semi/anti-join
•
•
•
Outer join
•
•
•
Partitioned outer join
•
•
