Hint

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.

Hint syntax

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

--+RULE
/*+RULE */

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

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

Also see

• Cost Based Optimizer
• DBMS_STATS, package used to gather optimizer statistics.

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	#