HINT

A hint is code embedded into a SQL statement suggesting to Oracle how it should be processed. Some examples: ALL_ROWS, FIRST_ROWS, CHOOSE, RULE, INDEX, FULL, ORDERED, STAR.

 Hint syntax
Hints must be included into a comment followed by a +-sigh 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 a FULL TABLE SCAN method:

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