Oracle Hints

Optimizer hints can be used with SQL statements to alter execution plans

Hints let you make decisions usually made by the optimizer. As an application designer, you might know information about your data that the optimizer does not know. Hints provide a mechanism to direct the optimizer to choose a certain query execution plan based on the specific criteria

Types of Hints

Hints can be of the following general types:

• Single-table

  Single-table hints are specified on one table or view. INDEX and USE_NL are examples of single-table hints.

• Multi-table

  Multi-table hints are like single-table hints, except that the hint can specify one or more tables or views. LEADING is an example of a multi-table hint. Note that USE_NL(table1 table2) is not considered a multi-table hint because it is a shortcut for USE_NL(table1) and USE_NL(table2).

• Query block

  Query block hints operate on single query blocks. STAR_TRANSFORMATION and UNNEST are examples of query block hints.

• Statement

  Statement hints apply to the entire SQL statement. ALL_ROWS is an example of a statement hint.

all_rows
first_rows
first_rows_1
first_rows_100
choose
rule
full
rowid
cluster
hash
hash_aj
index
no_index
index_asc
index_combine
index_join
index_desc
index_ffs
no_index_ffs
index_ss
index_ss_asc
index_ss_desc
no_index_ss
no_query_transformation
use_concat
no_expand
rewrite
norewrite
no_rewrite
merge
no_merge
fact
no_fact
star_transformation
no_star_transformation
unnest
no_unnest
leading
ordered
use_nl
no_use_nl
use_nl_with_index
use_merge
no_use_merge
use_hash
no_use_hash
parallel
noparallel / no_parallel
pq_distribute
no_parallel_index
append
noappend
cache
nocache
push_pred
no_push_pred
push_subq
no_push_subq
qb_name
cursor_sharing_exact
driving_site
dynamic_sampling
spread_min_analysis
merge_aj
and_equal
star
bitmap
hash_sj
nl_sj
nl_aj
ordered_predicates
expand_gset_to_union