Join Operation Hints¶
Join operation hints are also paired:
USE_HASH
/NO_USE_HASH
USE_MERGE
/NO_USE_MERGE
USE_NL
/NO_USE_NL
These hints allow you to instruct the optimizer to use a hash join, a sort-merge join, or nested loops, respectively.
Hash joins support input swapping, which we have discussed when we talked about left-deep and right-deep join trees.
This can be accomplished with SWAP_JOIN_INPUTS
or prohibited with NO_SWAP_JOIN_INPUTS
.
The left-deep join tree can be enforced with the following hints:
1/*+ LEADING( t1 t2 t3 t4 )
2 USE_HASH( t2 )
3 USE_HASH( t3 )
4 USE_HASH( t4 )
5 NO_SWAP_JOIN_INPUTS( t2 )
6 NO_SWAP_JOIN_INPUTS( t3 )
7 NO_SWAP_JOIN_INPUTS( t4 ) */
We could have also written USE_HASH( t4 t3 t2 )
instead of three separate hints.
So, how do we go from a left-deep join ( ( T1 → T2 ) → T3 ) → T4 to a right-deep join T4 → ( T3 → ( T2 → T1 ) )? Remember the steps we had to perform, especially the swaps? The process to go from the left-deep join tree to the right-deep join tree is to swap the order in the following sequence: T4, T3, and T2. We can thus obtain the right-deep join tree by taking the left-deep join tree as a template and providing the necessary swaps:
1/*+ LEADING( t1 t2 t3 t4 )
2 USE_HASH( t2 )
3 USE_HASH( t3 )
4 USE_HASH( t4 )
5 SWAP_JOIN_INPUTS( t2 )
6 SWAP_JOIN_INPUTS( t3 )
7 SWAP_JOIN_INPUTS( t4 ) */
The LEADING
hint refers to the situation before all the swaps.
Important to know is that the left-deep join tree is always the starting point.
Oracle occasionally bumps into bushy trees when views cannot be merged. Bushy trees can, however, be practical in what is sometimes referred to as a snowstorm schema, but we shall not go into more details here. In instances where a bushy join is known to be advantageous you may have to rewrite your query. For example, you can force Oracle to perform the bushy join ( T1 → T2 ) → ( T3 → T4 ) by writing the query schematically as follows:
1SELECT /* LEADING ( v12 v34 )
2 USE_HASH( v34 )
3 NO_SWAP_JOIN_INPUTS( v34 ) */
4 *
5FROM
6 (
7 SELECT /*+ LEADING( t1 t2 )
8 NO_SWAP_JOIN_INPUTS( t2 )
9 USE_HASH( t2 )
10 NO_MERGE */
11 *
12 FROM
13 t1 NATURAL JOIN t2
14 ) v12
15NATURAL JOIN
16 (
17 SELECT /*+ LEADING( t3 t4 )
18 NO_SWAP_JOIN_INPUTS( t4 )
19 USE_HASH( t4 )
20 NO_MERGE */
21 *
22 FROM
23 t3 NATURAL JOIN t4
24 ) v34
25;
You may have noticed that we have sneaked in the NO_MERGE
hint, which we shall describe in somewhat more detail below.
What is more, we have used a NATURAL JOIN
to save space on the ON
or USING
clauses as they is immaterial to our discussion.
Can you force Oracle to do a bushy join without rewriting the query?
Unfortunately not. The reason is that there is no combination of swaps to go from a left-deep join tree to any bushy join tree. You can do it with a bunch of hints for a zigzag trees, because only some of the inputs are swapped, but bushy trees are a nut too tough to crack with hints alone.
When you use USE_MERGE
or USE_NL
it is best to provide the LEADING
hint as well.
The table first listed in LEADING
is generally the driving row source.
The (first) table specified in USE_NL
is used as the probe row source or inner table.
The syntax is the same for the sort-merge join: whichever table is specified (first) is the inner table of the join.
For instance, the combination /*+ LEADING( t1 t2 t3 ) USE_NL( t2 t3 ) */
causes the optimizer to take T1 as the driving row source and use nested loops to join T1 and T2.
Oracle then uses the result set of the join of T1 and T2 as the driving row source for the join with T3.
For nested loops there is also the alternative USE_NL_WITH_INDEX
to instruct Oracle to use the specified table as the probe row source and use the specified index as the lookup.
The index key must be applicable to the join predicate.