Search notes:
Oracle: V$SQL_HINT
select
lower(shi.class ) hint_class,
lower(shi.name ) hint_name,
lower(shi.inverse ) inverse_hint_name,
fea.description description,
lower(shi.sql_feature) feature
from
v$sql_hint shi left join
v$sql_feature fea on shi.sql_feature = fea.sql_feature
order by
shi.class,
shi.name
;
--
-- access and_equal Index and-equal access path qksfm_and_equal
-- access bitmap_tree Bitmap tree access path qksfm_bitmap_tree
-- access cluster SQL Cost Based Optimization qksfm_cbo
-- access full Full table scan qksfm_full
-- access hash A Universal Feature qksfm_all
-- access index no_index Index qksfm_index
-- access index_asc no_index Index (ascending) qksfm_index_asc
-- access index_combine Combine index for bitmap access qksfm_index_combine
-- access index_desc no_index Use index (descending) qksfm_index_desc
-- access index_ffs Index fast full scan qksfm_index_ffs
-- access index_join Index join qksfm_index_join
-- access index_rrs SQL Cost Based Optimization qksfm_cbo
-- access index_rs_asc Index range scan qksfm_index_rs_asc
-- access index_rs_desc Index range scan descending qksfm_index_rs_desc
-- access index_ss no_index_ss Index skip scan qksfm_index_ss
-- access index_ss_asc no_index_ss Index skip scan ascending qksfm_index_ss_asc
-- access index_ss_desc no_index_ss Index skip scan descending qksfm_index_ss_desc
-- access nlj_batching no_nlj_batching SQL EXECUTION qksfm_execution
-- access nlj_prefetch no_nlj_prefetch SQL EXECUTION qksfm_execution
-- access no_nlj_batching nlj_batching SQL EXECUTION qksfm_execution
-- access no_nlj_prefetch nlj_prefetch SQL EXECUTION qksfm_execution
-- access num_index_keys SQL Cost Based Optimization qksfm_cbo
-- access queue_curr SQL Cost Based Optimization qksfm_cbo
-- access queue_rowp SQL Cost Based Optimization qksfm_cbo
-- access rowid SQL Cost Based Optimization qksfm_cbo
-- antijoin antijoin Query Transformation qksfm_transformation
-- antijoin hash_aj SQL Cost Based Optimization qksfm_cbo
-- antijoin merge_aj SQL Cost Based Optimization qksfm_cbo
-- antijoin nl_aj SQL Cost Based Optimization qksfm_cbo
-- append append noappend SQL Cost Based Optimization qksfm_cbo
-- append noappend append SQL Cost Based Optimization qksfm_cbo
-- append_values append_values noappend SQL Cost Based Optimization qksfm_cbo
-- bind_aware bind_aware no_bind_aware Cursor sharing qksfm_cursor_sharing
-- bind_aware no_bind_aware bind_aware Cursor sharing qksfm_cursor_sharing
-- bitmap bitmap SQL Cost Based Optimization qksfm_cbo
-- buffer buffer no_buffer SQL Cost Based Optimization qksfm_cbo
-- buffer no_buffer buffer SQL Cost Based Optimization qksfm_cbo
-- bypass_recursive_check bypass_recursive_check A Universal Feature qksfm_all
-- bypass_ujvc bypass_ujvc SQL Cost Based Optimization qksfm_cbo
-- cache cache nocache SQL EXECUTION qksfm_execution
-- cache cache_temp_table nocache A Universal Feature qksfm_all
-- cache nocache cache SQL EXECUTION qksfm_execution
-- cache_cb cache_cb nocache SQL Cost Based Optimization qksfm_cbo
-- cardinality cardinality Optimizer statistics qksfm_stats
-- change_dupkey_error_index change_dupkey_error_index DML qksfm_dml
-- check_acl_rewrite check_acl_rewrite no_check_acl_rewrite Check ACL Rewrite qksfm_check_acl_rewrite
-- coalesce_sq coalesce_sq no_coalesce_sq coalesce subqueries qksfm_coalesce_sq
-- coalesce_sq no_coalesce_sq coalesce_sq coalesce subqueries qksfm_coalesce_sq
-- connect_by_cb_whr_only connect_by_cb_whr_only no_connect_by_cb_whr_only Query Transformation qksfm_transformation
-- connect_by_cb_whr_only no_connect_by_cb_whr_only connect_by_cb_whr_only Query Transformation qksfm_transformation
-- connect_by_combine_sw connect_by_combine_sw no_connect_by_combine_sw A Universal Feature qksfm_all
-- connect_by_combine_sw no_connect_by_combine_sw connect_by_combine_sw A Universal Feature qksfm_all
-- connect_by_cost_based connect_by_cost_based no_connect_by_cost_based Query Transformation qksfm_transformation
-- connect_by_cost_based no_connect_by_cost_based connect_by_cost_based Query Transformation qksfm_transformation
-- connect_by_elim_dups connect_by_elim_dups no_connect_by_elim_dups A Universal Feature qksfm_all
-- connect_by_elim_dups no_connect_by_elim_dups connect_by_elim_dups A Universal Feature qksfm_all
-- connect_by_filtering connect_by_filtering no_connect_by_filtering A Universal Feature qksfm_all
-- connect_by_filtering no_connect_by_filtering connect_by_filtering A Universal Feature qksfm_all
-- cost_xml_query_rewrite cost_xml_query_rewrite no_cost_xml_query_rewrite Cost Based XML Query Rewrite qksfm_cost_xml_query_rewrite
-- cpu_costing cpu_costing no_cpu_costing CPU costing qksfm_cpu_costing
-- cpu_costing no_cpu_costing cpu_costing CPU costing qksfm_cpu_costing
-- cube_gb cube_gb SQL Cost Based Optimization qksfm_cbo
-- cursor_sharing_exact cursor_sharing_exact SQL Cost Based Optimization qksfm_cbo
-- dbms_stats dbms_stats Statistics gathered by DBMS_STATS qksfm_dbms_stats
-- db_version db_version A Universal Feature qksfm_all
-- deref_no_rewrite deref_no_rewrite A Universal Feature qksfm_all
-- dml_update dml_update SQL Cost Based Optimization qksfm_cbo
-- domain_index_filter domain_index_filter no_domain_index_filter SQL Cost Based Optimization qksfm_cbo
-- domain_index_sort domain_index_no_sort domain_index_sort SQL Cost Based Optimization qksfm_cbo
-- domain_index_sort domain_index_sort domain_index_no_sort SQL Cost Based Optimization qksfm_cbo
-- driving_site driving_site A Universal Feature qksfm_all
-- dst_upgrade_insert_conv dst_upgrade_insert_conv no_dst_upgrade_insert_conv A Universal Feature qksfm_all
-- dst_upgrade_insert_conv no_dst_upgrade_insert_conv dst_upgrade_insert_conv A Universal Feature qksfm_all
-- dynamic_sampling dynamic_sampling Dynamic sampling qksfm_dynamic_sampling
-- dynamic_sampling_est_cdn dynamic_sampling_est_cdn Estimate CDN using dynamic sampling qksfm_dynamic_sampling_est_cdn
-- eliminate_join eliminate_join no_eliminate_join Table Elimination qksfm_table_elim
-- eliminate_join no_eliminate_join eliminate_join Table Elimination qksfm_table_elim
-- eliminate_oby eliminate_oby no_eliminate_oby Order-by Elimination qksfm_obye
-- eliminate_oby no_eliminate_oby eliminate_oby Order-by Elimination qksfm_obye
-- expand_gset_to_union expand_gset_to_union no_expand_gset_to_union Query Transformation qksfm_transformation
-- expand_gset_to_union no_expand_gset_to_union expand_gset_to_union Query Transformation qksfm_transformation
-- expand_table expand_table no_expand_table Table Expansion qksfm_table_expansion
-- expand_table no_expand_table expand_table Table Expansion qksfm_table_expansion
-- expr_corr_check expr_corr_check SQL Cost Based Optimization qksfm_cbo
-- fact fact no_fact Star Transformation qksfm_star_trans
-- fact no_fact fact Star Transformation qksfm_star_trans
-- factorize_join factorize_join no_factorize_join Join Factorization qksfm_joinfac
-- factorize_join no_factorize_join factorize_join Join Factorization qksfm_joinfac
-- fbtscan fbtscan SQL Cost Based Optimization qksfm_cbo
-- force_xml_query_rewrite force_xml_query_rewrite no_xml_query_rewrite XML Rewrite qksfm_xml_rewrite
-- force_xml_query_rewrite no_xml_query_rewrite force_xml_query_rewrite XML Rewrite qksfm_xml_rewrite
-- full_outer_join_to_outer full_outer_join_to_outer no_full_outer_join_to_outer SQL Cost Based Optimization qksfm_cbo
-- full_outer_join_to_outer no_full_outer_join_to_outer full_outer_join_to_outer SQL Cost Based Optimization qksfm_cbo
-- gather_plan_statistics gather_plan_statistics Gather plan statistics qksfm_gather_plan_statistics
-- gby_conc_rollup gby_conc_rollup Query Transformation qksfm_transformation
-- gby_pushdown gby_pushdown no_gby_pushdown A Universal Feature qksfm_all
-- gby_pushdown no_gby_pushdown gby_pushdown A Universal Feature qksfm_all
-- hwm_brokered hwm_brokered SQL Cost Based Optimization qksfm_cbo
-- ignore_optim_embedded_hints ignore_optim_embedded_hints A Universal Feature qksfm_all
-- ignore_row_on_dupkey_index ignore_row_on_dupkey_index DML qksfm_dml
-- ignore_where_clause ignore_where_clause A Universal Feature qksfm_all
-- include_version include_version A Universal Feature qksfm_all
-- inline inline materialize Query Transformation qksfm_transformation
-- inline materialize inline Query Transformation qksfm_transformation
-- inline_xmltype_nt inline_xmltype_nt A Universal Feature qksfm_all
-- join use_hash no_use_hash Hash join qksfm_use_hash
-- join use_merge no_use_merge Sort-merge join qksfm_use_merge
-- join use_merge_cartesian Merge join cartesian qksfm_use_merge_cartesian
-- join use_nl no_use_nl Nested-loop join qksfm_use_nl
-- leading leading Join order qksfm_join_order
-- like_expand like_expand Query Transformation qksfm_transformation
-- local_indexes local_indexes SQL Cost Based Optimization qksfm_cbo
-- merge merge no_merge Complex View Merging qksfm_cvm
-- merge no_merge merge Complex View Merging qksfm_cvm
-- merge_const_on merge_const_on SQL Cost Based Optimization qksfm_cbo
-- mode all_rows All rows (optimizer mode) qksfm_all_rows
-- mode choose Choose (optimizer mode) qksfm_choose
-- mode first_rows First rows (optimizer mode) qksfm_first_rows
-- mode rule SQL Rule Based Optimization qksfm_rbo
-- model_compile_subquery model_compile_subquery Query Transformation qksfm_transformation
-- model_dontverify_uniqueness model_dontverify_uniqueness Query Transformation qksfm_transformation
-- model_dynamic_subquery model_dynamic_subquery Query Transformation qksfm_transformation
-- model_min_analysis model_min_analysis Query Transformation qksfm_transformation
-- model_min_analysis model_no_analysis A Universal Feature qksfm_all
-- model_push_ref model_push_ref no_model_push_ref Query Transformation qksfm_transformation
-- model_push_ref no_model_push_ref model_push_ref A Universal Feature qksfm_all
-- monitor monitor no_monitor A Universal Feature qksfm_all
-- monitor no_monitor monitor A Universal Feature qksfm_all
-- mv_merge mv_merge Query Transformation qksfm_transformation
-- native_full_outer_join native_full_outer_join no_native_full_outer_join A Universal Feature qksfm_all
-- native_full_outer_join no_native_full_outer_join native_full_outer_join A Universal Feature qksfm_all
-- nested_table_fast_insert nested_table_fast_insert A Universal Feature qksfm_all
-- nested_table_get_refs nested_table_get_refs A Universal Feature qksfm_all
-- nested_table_set_setid nested_table_set_setid A Universal Feature qksfm_all
-- no_access no_access A Universal Feature qksfm_all
-- no_cartesian no_cartesian A Universal Feature qksfm_all
-- no_check_acl_rewrite no_check_acl_rewrite check_acl_rewrite Check ACL Rewrite qksfm_check_acl_rewrite
-- no_cost_xml_query_rewrite no_cost_xml_query_rewrite cost_xml_query_rewrite Cost Based XML Query Rewrite qksfm_cost_xml_query_rewrite
-- no_domain_index_filter no_domain_index_filter domain_index_filter SQL Cost Based Optimization qksfm_cbo
-- no_index no_index index Index qksfm_index
-- no_index_ffs no_index_ffs index_ffs Index fast full scan qksfm_index_ffs
-- no_index_ss no_index_ss index_ss Index skip scan qksfm_index_ss
-- no_load no_load SQL EXECUTION qksfm_execution
-- no_monitoring no_monitoring A Universal Feature qksfm_all
-- no_order_rollups no_order_rollups Query Transformation qksfm_transformation
-- no_partial_commit no_partial_commit SQL Cost Based Optimization qksfm_cbo
-- no_prune_gsets no_prune_gsets Query Transformation qksfm_transformation
-- no_qkn_buff no_qkn_buff SQL Cost Based Optimization qksfm_cbo
-- no_query_transformation no_query_transformation Query Transformation qksfm_transformation
-- no_sql_tune no_sql_tune A Universal Feature qksfm_all
-- no_stats_gsets no_stats_gsets A Universal Feature qksfm_all
-- no_substrb_pad no_substrb_pad SQL EXECUTION qksfm_execution
-- no_use_hash no_use_hash use_hash Hash join qksfm_use_hash
-- no_use_merge no_use_merge use_merge Sort-merge join qksfm_use_merge
-- no_use_nl no_use_nl use_nl Nested-loop join qksfm_use_nl
-- no_xml_dml_rewrite no_xml_dml_rewrite XML Rewrite qksfm_xml_rewrite
-- old_push_pred old_push_pred Old push predicate algorithm (pre-10.1.0.3) qksfm_old_push_pred
-- opaque_transform opaque_transform Query Transformation qksfm_transformation
-- opaque_xcanonical opaque_xcanonical Query Transformation qksfm_transformation
-- opt_estimate opt_estimate Optimizer estimates qksfm_opt_estimate
-- optimizer_features_enable optimizer_features_enable A Universal Feature qksfm_all
-- opt_param opt_param A Universal Feature qksfm_all
-- ordered ordered SQL Cost Based Optimization qksfm_cbo
-- ordered_predicates ordered_predicates SQL Cost Based Optimization qksfm_cbo
-- or_expand or_expand OR expansion qksfm_or_expand
-- outer_join_to_anti no_outer_join_to_anti outer_join_to_anti SQL Cost Based Optimization qksfm_cbo
-- outer_join_to_anti outer_join_to_anti no_outer_join_to_anti SQL Cost Based Optimization qksfm_cbo
-- outer_join_to_inner no_outer_join_to_inner outer_join_to_inner Join Conversion qksfm_outer_join_to_inner
-- outer_join_to_inner outer_join_to_inner no_outer_join_to_inner Join Conversion qksfm_outer_join_to_inner
-- outline outline A Universal Feature qksfm_all
-- outline_leaf outline_leaf A Universal Feature qksfm_all
-- overflow_nomove overflow_nomove SQL Cost Based Optimization qksfm_cbo
-- parallel_index no_parallel_index parallel_index Parallel Query qksfm_pq
-- parallel_index parallel_index no_parallel_index Parallel Query qksfm_pq
-- piv_gb piv_gb A Universal Feature qksfm_all
-- piv_gb tiv_gb A Universal Feature qksfm_all
-- piv_ssf piv_ssf A Universal Feature qksfm_all
-- piv_ssf tiv_ssf A Universal Feature qksfm_all
-- place_distinct no_place_distinct place_distinct Distinct Placement qksfm_dist_plcmt
-- place_distinct place_distinct no_place_distinct Distinct Placement qksfm_dist_plcmt
-- place_group_by no_place_group_by place_group_by Group-By Placement qksfm_place_group_by
-- place_group_by place_group_by no_place_group_by Group-By Placement qksfm_place_group_by
-- pq_distribute pq_distribute PQ Distribution method qksfm_pq_distribute
-- pq_map pq_map pq_nomap PQ slave mapper qksfm_pq_map
-- pq_map pq_nomap pq_map PQ slave mapper qksfm_pq_map
-- precompute_subquery precompute_subquery Query Transformation qksfm_transformation
-- preserve_oid preserve_oid A Universal Feature qksfm_all
-- pull_pred no_pull_pred pull_pred pull predicates qksfm_pull_pred
-- pull_pred pull_pred no_pull_pred pull predicates qksfm_pull_pred
-- push_pred no_push_pred push_pred Push filter predicates qksfm_filter_push_pred
-- push_pred push_pred no_push_pred Push filter predicates qksfm_filter_push_pred
-- push_subq no_push_subq push_subq Query Transformation qksfm_transformation
-- push_subq push_subq no_push_subq Query Transformation qksfm_transformation
-- px_join_filter no_px_join_filter px_join_filter Bloom filtering for joins qksfm_px_join_filter
-- px_join_filter px_join_filter no_px_join_filter Bloom filtering for joins qksfm_px_join_filter
-- qb_name qb_name A Universal Feature qksfm_all
-- rbo_outline rbo_outline SQL Rule Based Optimization qksfm_rbo
-- ref_cascade_cursor no_ref_cascade ref_cascade_cursor SQL Cost Based Optimization qksfm_cbo
-- ref_cascade_cursor ref_cascade_cursor no_ref_cascade SQL Cost Based Optimization qksfm_cbo
-- remote_mapped remote_mapped A Universal Feature qksfm_all
-- restore_as_intervals restore_as_intervals SQL Cost Based Optimization qksfm_cbo
-- restrict_all_ref_cons restrict_all_ref_cons A Universal Feature qksfm_all
-- result_cache no_result_cache result_cache SQL EXECUTION qksfm_execution
-- result_cache result_cache no_result_cache SQL EXECUTION qksfm_execution
-- retry_on_row_change retry_on_row_change DML qksfm_dml
-- rewrite no_basetable_multimv_rewrite rewrite A Universal Feature qksfm_all
-- rewrite no_multimv_rewrite rewrite A Universal Feature qksfm_all
-- rewrite no_rewrite rewrite Query Transformation qksfm_transformation
-- rewrite rewrite no_rewrite Query Transformation qksfm_transformation
-- rewrite rewrite_or_error Query Transformation qksfm_transformation
-- save_as_intervals save_as_intervals SQL Cost Based Optimization qksfm_cbo
-- scn_ascending scn_ascending A Universal Feature qksfm_all
-- semijoin hash_sj SQL Cost Based Optimization qksfm_cbo
-- semijoin merge_sj SQL Cost Based Optimization qksfm_cbo
-- semijoin nl_sj SQL Cost Based Optimization qksfm_cbo
-- semijoin no_semijoin semijoin Query Transformation qksfm_transformation
-- semijoin semijoin no_semijoin Query Transformation qksfm_transformation
-- semijoin_driver semijoin_driver SQL Cost Based Optimization qksfm_cbo
-- set_to_join no_set_to_join set_to_join Transform set operations to joins qksfm_set_to_join
-- set_to_join set_to_join no_set_to_join Transform set operations to joins qksfm_set_to_join
-- shared no_parallel shared SQL Cost Based Optimization qksfm_cbo
-- shared noparallel shared Parallel table qksfm_parallel
-- shared shared no_parallel Parallel table qksfm_parallel
-- skip_ext_optimizer skip_ext_optimizer SQL Cost Based Optimization qksfm_cbo
-- skip_unq_unusable_idx skip_unq_unusable_idx SQL Cost Based Optimization qksfm_cbo
-- sqlldr sqlldr SQL Cost Based Optimization qksfm_cbo
-- star star Star Transformation qksfm_star_trans
-- star_transformation no_star_transformation star_transformation Star Transformation qksfm_star_trans
-- star_transformation star_transformation no_star_transformation Star Transformation qksfm_star_trans
-- statement_queuing no_statement_queuing statement_queuing Parallel table qksfm_parallel
-- statement_queuing statement_queuing no_statement_queuing Parallel table qksfm_parallel
-- streams streams SQL Cost Based Optimization qksfm_cbo
-- subquery_pruning no_subquery_pruning subquery_pruning SQL Cost Based Optimization qksfm_cbo
-- subquery_pruning subquery_pruning no_subquery_pruning SQL Cost Based Optimization qksfm_cbo
-- swap_join_inputs no_swap_join_inputs swap_join_inputs SQL Cost Based Optimization qksfm_cbo
-- swap_join_inputs swap_join_inputs no_swap_join_inputs SQL Cost Based Optimization qksfm_cbo
-- sys_dl_cursor sys_dl_cursor SQL Cost Based Optimization qksfm_cbo
-- sys_parallel_txn sys_parallel_txn SQL Cost Based Optimization qksfm_cbo
-- sys_rid_order sys_rid_order A Universal Feature qksfm_all
-- table_lookup_by_nl no_table_lookup_by_nl table_lookup_by_nl Table Lookup By Nested Loop qksfm_table_lookup_by_nl
-- table_lookup_by_nl table_lookup_by_nl no_table_lookup_by_nl Table Lookup By Nested Loop qksfm_table_lookup_by_nl
-- table_stats column_stats Optimizer statistics qksfm_stats
-- table_stats index_stats Optimizer statistics qksfm_stats
-- table_stats table_stats Optimizer statistics qksfm_stats
-- tracing tracing SQL EXECUTION qksfm_execution
-- transform_distinct_agg no_transform_distinct_agg transform_distinct_agg Query Transformation qksfm_transformation
-- transform_distinct_agg transform_distinct_agg no_transform_distinct_agg Query Transformation qksfm_transformation
-- unnest no_unnest unnest unnest query block qksfm_unnest
-- unnest unnest no_unnest unnest query block qksfm_unnest
-- use_anti use_anti SQL Cost Based Optimization qksfm_cbo
-- use_concat no_expand use_concat Or-optimization qksfm_use_concat
-- use_concat use_concat no_expand Or-optimization qksfm_use_concat
-- use_hash_aggregation no_use_hash_aggregation use_hash_aggregation A Universal Feature qksfm_all
-- use_hash_aggregation use_hash_aggregation no_use_hash_aggregation A Universal Feature qksfm_all
-- use_hash_gby_for_pushdown no_use_hash_gby_for_pushdown use_hash_gby_for_pushdown A Universal Feature qksfm_all
-- use_hash_gby_for_pushdown use_hash_gby_for_pushdown no_use_hash_gby_for_pushdown A Universal Feature qksfm_all
-- use_invisible_indexes no_use_invisible_indexes use_invisible_indexes Index qksfm_index
-- use_invisible_indexes use_invisible_indexes no_use_invisible_indexes Index qksfm_index
-- use_nl_with_index use_nl_with_index no_use_nl Nested-loop index join qksfm_use_nl_with_index
-- use_semi use_semi SQL Cost Based Optimization qksfm_cbo
-- use_ttt_for_gsets use_ttt_for_gsets Query Transformation qksfm_transformation
-- use_weak_name_resl use_weak_name_resl A Universal Feature qksfm_all
-- vector_read vector_read SQL Cost Based Optimization qksfm_cbo
-- vector_read_trace vector_read_trace SQL Cost Based Optimization qksfm_cbo
-- xdb_fastpath_insert no_xdb_fastpath_insert xdb_fastpath_insert A Universal Feature qksfm_all
-- xdb_fastpath_insert xdb_fastpath_insert no_xdb_fastpath_insert A Universal Feature qksfm_all
-- x_dyn_prune x_dyn_prune SQL Cost Based Optimization qksfm_cbo
-- xml_dml_rwt_stmt xml_dml_rwt_stmt XML Rewrite qksfm_xml_rewrite
-- xmlindex_rewrite no_xmlindex_rewrite xmlindex_rewrite XMLIndex Rewrite qksfm_xmlindex_rewrite
-- xmlindex_rewrite no_xmlindex_rewrite_in_select xmlindex_rewrite_in_select XMLIndex Rewrite qksfm_xmlindex_rewrite
-- xmlindex_rewrite xmlindex_rewrite no_xmlindex_rewrite XMLIndex Rewrite qksfm_xmlindex_rewrite
-- xmlindex_rewrite xmlindex_rewrite_in_select no_xmlindex_rewrite_in_select XMLIndex Rewrite qksfm_xmlindex_rewrite
-- xmlindex_sel_idx_tbl xmlindex_sel_idx_tbl A Universal Feature qksfm_all