A hint is code snippet that is embedded into a SQL statement to suggest to Oracle how the statement should be executed. Some examples: ALL_ROWS, FIRST_ROWS, CHOOSE, RULE, INDEX, FULL, ORDERED, STAR.
Hints should only be used as a last-resort if statistics were gathered and the query is still following a sub-optimal execution plan.
Hints must be included into a comment followed by a +-sign right after the first keyword of the statement. Examples:
--+RULE /*+RULE */
Some of the more useful hints are:
- ORDERED - usually with USE_NL to get Oracle to not hash join
- INDEX(t index_name) - where Oracle chooses the wrong index over the correct one
- NO_INDEX - prevent an index from being used
- INDEX_COMBINE - merging bitmap indexes (use when Oracle does not merge bitmap indexes)
- FIRST_ROWS(n) - when you only want the first few rows
- PARALLEL - to force parallel query on certain specific queries
- GATHER_PLAN_STATISTICS - used as a handy sql trace
- DYNAMIC_SAMPLING - used as alternative to statistics for large-scale warehouse queries
- OPT_PARAM - used to control optimizer behavior at query level (added in 10.2.0.3)
- QB_NAME - specify query block name when tuning complex queries. It helps in finding a particular query for troubleshooting (10 and up)
- CARDINALITY - give the optimizer better information
Example suggesting that a FULL TABLE SCAN method be used:
SELECT /*+ FULL(x) */ FROM tab1 x WHERE col1 = 10;
Suggest that Oracle uses a specific index:
SELECT /*+ INDEX(x emp_idx1) */ ... FROM scott.emp x...
Suggest that Oracle DOES NOT USE a specific index:
SELECT /*+ NO_INDEX(x emp_idx1) */ ... FROM scott.emp x...
|Glossary of Terms|