| Hint | Description/format | Example |
|---|---|---|
| ALL_ROWS | Tells the optimiser to user the cost-based approach: minimum total resource consumption | select /*+ ALL_ROWS */ |
| APPEND | Tells to optimiser to insert new data above the high water mark, speeding up the insert | insert /*+ APPEND*/ into [tablename] |
| CACHE | ||
| CHOOSE | Deprecated from 11g onward | |
| CLUSTER | ||
| CURSOR_SHARING_EXACT | ||
| DRIVING_SITE | Define where a query is executed when run over a database link | select /*+ DRIVING_SITE( [table_name] ) */ ... |
| DYNAMIC_SAMPLING | ||
| FACT | ||
| FIRST_ROWS( n ) | Get the first (n) rows as fast as possible: not relevant where a query requires all rows to process, e.g. ORDER BY, GROUP BY | select /*+ FIRST_ROWS(10) */ |
| FULL | ||
| HASH | ||
| INDEX_ASC | As per INDEX hint, but search in ascending order | |
| INDEX_COMBINE | ||
| INDEX_DESC | As per INDEX hint, but search in descending order | |
| INDEX_FFS | ||
| INDEX_JOIN | ||
| INDEX_SS_ASC | ||
| INDEX_SS_DESC | ||
| INDEX_SS | ||
| INDEX | Use a specific index: /*+ INDEX( [table_name] [index_name]) */ | SELECT /*+ INDEX (countries countries_name_ix)*/ country_code, country_name FROM countriesWHERE country_name like 'AL%' |
| LEADING | ||
| MERGE | ||
| MODEL_MIN_ANALYSIS | ||
| MONITOR | ||
| NATIVE_FULL_OUTER_JOIN | ||
| NO_EXPAND | ||
| NO_FACT | ||
| NO_INDEX_FFS | ||
| NO_INDEX_SS | ||
| NO_INDEX | ||
| NO_MERGE | ||
| NO_MONITOR | ||
| NO_NATIVE_FULL_OUTER_JOIN | ||
| NO_PARALLEL_INDEX | ||
| NO_PARALLEL | ||
| NO_PUSH_PRED | ||
| NO_PUSH_SUBQ | ||
| NO_PX_JOIN_FILTER | ||
| NO_QUERY_TRANSFORMATION | ||
| NO_RESULT_CACHE | ||
| NO_REWRITE | ||
| NO_STAR_TRANSFORMATION | ||
| NO_UNNEST | ||
| NO_USE_HASH | ||
| NO_USE_MERGE | ||
| NO_USE_NL | ||
| NO_XML_QUERY_REWRITE | ||
| NO_XMLINDEX_REWRITE | ||
| NOAPPEND | ||
| NOCACHE | ||
| OPT_PARAM | ||
| ORDERED | Process tables in order of position in sql | select /*+ ORDERED */ ... |
| PARALLEL_INDEX | ||
| PARALLEL | ||
| PQ_DISTRIBUTE | ||
| PUSH_PRED | ||
| PUSH_SUBQ | ||
| PX_JOIN_FILTER | ||
| QB_NAME | ||
| RESULT_CACHE | ||
| REWRITE | ||
| RULE | Deprecated from 11g onward; the hint FIRST_ROWS might achieve similar behaviour | |
| STAR_TRANSFORMATION | ||
| UNNEST | ||
| USE_CONCAT | ||
| USE_HASH | ||
| USE_MERGE | ||
| USE_NL | ||
| USE_NL_WITH_INDEX |