Oracle Hint List Oracle Tips by Burleson Consulting

Oracle hints are optimizer directives that are used in SQL de-bugging to change the default execution plan for SQL.  Once the SQL has been optimized by adding the hint, the Oracle professional will adjust schema statistics (using dbms_stats) to replicate the execution plan without the hint. 

The exceptions are hints that may be left inside the SQL, hints such as ordered, which tell the optimizer the optimal table join order and the main optimizer hints (first_rows, all_rows).  These hints may be left inside production SQL. 

To learn how to use hints to tune your SQL, see my book "Oracle Tuning: The Definitive Reference". You can buy it at the link above for 30%-off and get immediate access to the code depot.

Here is a complete list of all documented Oracle 10g hints:

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

Oracle also has undocumented hints, hints which can be very useful but are not in the Oracle documentation.  You should always carefully research any undocumented hints and test it carefully before using any undocumented hints in production.  You can also log a service request with MOSC when considering using an undocumented hint.  Here is a list of Oracle undocumented hints:

bypass_recursive_check
bypass_ujvc
cache_cb
cache_temp_table
civ_gb
collections_get_refs
cube_gb
cursor_sharing_exact
deref_no_rewrite
dml_update
domain_index_no_sort
domain_index_sort
dynamic_sampling
dynamic_sampling_est_cdn
expand_gset_to_union
force_sample_block
gby_conc_rollup
global_table_hints
hwm_brokered
ignore_on_clause
ignore_where_clause
index_rrs
index_ss
index_ss_asc
index_ss_desc
like_expand
local_indexes
mv_merge
nested_table_get_refs
nested_table_set_refs
nested_table_set_setid
no_expand_gset_to_union
no_fact
no_filtering
no_order_rollups
no_prune_gsets
no_stats_gsets
no_unnest
nocpu_costing overflow_nomove
piv_gb
piv_ssf
pq_map
pq_nomap
remote_mapped
restore_as_intervals
save_as_intervals
scn_ascending
skip_ext_optimizer
sqlldr
sys_dl_cursor
sys_parallel_txn
sys_rid_order
tiv_gb
tiv_ssf
unnest
use_ttt_for_gsets