Hints are CBO directives. If Oracle can use the hint, it will only produce explain plans that contain the hint directive. This article examines the main hints that control diriving tables. Knowing how to use these hints can help improve performance tuning.
Main hints that control driving tables
The main hints that control the driving table of a SQL statement include:
- FULL(table [table] …)
- ORDERED (being depreciated, may still work)
- LEADING(table [table] …)
- USE_NL(table table)
- USE_MERGE(table table)
- USE_HASH(table table)
The ORDERED hint gives the user the ability to shuffle the tables on the FROM clause. Directing Oracle to join the tables in a specific order will alter the order of the joins.
This is the first thing that should be done when tuning SQL. Put this hint in and shuffle the tables around on the FROM clause. You should notice that some SQL performs better than others. Start your tuning process with the SQL that performs the best from this exercise.
The LEADING hint is similar to the ORDERED except that it tells Oracle to drive from this table and to let the CBO make the other decisions as to join order. You can list more than one table.
The ORDERED hint also orders the sub queries where the LEADING hint just works with the table join order.
The USE_* drives the CBO to use nested loops, merge joins or hash joins.
Examples of driving table hints
In this section, we’ll look at an example with a full hint, an example without an ordered hint and an example that show benefit from an ordered hint.
The example below is a FULL hint illustration:
The example below does not have the ORDERED hint:
Finally, consider that the SQL in the example below benefited from the ORDERED hint.