A hint is code snippet embedded into an SQL statement to persuade the Oracle optimizer to take a specific approach when rendering an execution plan.
In most, if not any cases, the Oracle optimizer will provide a useful execution plan on it’s own when proper statistics are available. Trying to override the optimizes decisions may result in overall inflexibility.
The optimizer may decide not to show any reaction to a given hint at all if it doesn’t feel any sense in the hint.
Hints have to be included as a comment followed by a +-sign right after the first keyword of the statement.
Some useful hints are:
- ORDERED – usually used with USE_NL to prevent Oracle from using hash joins
- INDEX(t index_name) – suggest using this index (and not a different one)
- NO_INDEX – discourage index usage
- INDEX_COMBINE – suggest merging bitmap indexes
- FIRST_ROWS(n) – suggest query makes sense to use the first rows of result
- PARALLEL – suggest parallel query
- GATHER_PLAN_STATISTICS – sql trace
- CARDINALITY – provide better information
- BIND AWARE – force bind aware cursor sharing
There is a data dictionary view which lists all available hints since 11.2:
Suggest a FULL TABLE SCAN method to be used:
Suggest usage of a specific index:
Suggest not to use a specific index: