Join Order Hints¶
The optimizer lists all join orders to choose the best one. What it does not do is an exhaustive search.
In case you believe a different join order to be useful, you can use one of the join order hints: ORDERED
or LEADING
.
The latter is more versatile and should thus be preferred.
ORDERED
takes no parameters and instructs the optimizer to join the tables in the order as they appear in the FROM
clause.
Because the ORDERED
hint is so basic and you do not want to move around tables in the FROM
clause, Oracle has provided us with the LEADING
hint.
It takes the table names or aliases (if specified) as parameters, separated by spaces.
In the optimizer’s rock-paper-scissors game, ORDERED
beats LEADING
when both are specified for the same statement.
Moreover, if two or more conflicting LEADING
hints are provided, Oracle ignores all of them.
Similarly, any LEADING
hints are thrown into the bin when they are incompatible with dependencies in the join graph.