Hint

From Oracle FAQ
Jump to: navigation, search

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.

Contents

[edit] Hint syntax

Hints must be included into a comment followed by a +-sign right after the first keyword of the statement. Examples:

--+RULE
/*+RULE */

[edit] Available hints

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

[edit] Some examples

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...

[edit] Also see

Glossary of Terms
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #