Oracle 10g Hint List

Oracle 10g Hint List

Oracle Tips

Oracle hints are optimizer directives to change the default execution plan for SQL.

  • Finally hints should be replaced by adjusting schema statistics (using dbms_stats).
  • Hints such as ordered, and the main optimizer hints (first_rows, all_rows) may be left inside production SQL.

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 undocumented hints should always be carefully researched before using in production.
  • You can also log a service request with MetaLink 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

See Also:
SQL Hints jp(Japanese)

Reference:
http://otndnld.oracle.co.jp/products/database/oracle10g/performance/htdocs/burleson_cbo_pt2/listings/burleson_cbo_pt2_l1.html
http://www.dba-oracle.com/menu_sql_hints_list.htm