There are several methods (points of view) of categorizing hints. Personally, I like to group them in the following categories:
• Initialization parameter hints overwrite the setting of some initialization parameters defined at the system or session level. I classify the following hints in this category: all_rows, cursor_sharing_exact, dynamic_sampling, first_rows, gather_plan_ statistics, no_cpu_costing, optimizer_features_enable, opt_param, (no_)result_cache, and rule. I’ll cover these hints in the section “Altering the Execution Environment” later in this chapter, and I covered the hint gather_plan_statistics in Chapter 6. Note that these hints always overwrite the values set at the instance or session level when they are specified.
• Query transformation hints control the utilization of query transformation techniques during the logical optimization. I put the following hints in this category: (no_)eliminate_join, no_expand, (no_)merge, (no_)outer_join_to_inner , (no_)push_pred, (no_)push_subq, no_query_transformation, (no_)rewrite, (no_)unnest, no_xmlindex_ rewrite, no_xml_query_rewrite, and use_concat. I’ll cover some of these hints later in this chapter and some others in Chapter 10 and Chapter 11.
• Access path hints control the method used to access data (for example, whether an index is used). I classify the following hints in this category: cluster, full, hash, (no_)index, index_asc, index_combine, index_desc, (no_)index_ffs, index_join, (no_)index_ss, index_ss_asc, and index_ss_desc. I’ll cover these hints along with access methods in Chapter 9.
• Join hints control not only the join method but also the order used to join tables. I put the following hints in this category: leading, (no_)nlj_batching, ordered, (no_)star_transformation, (no_)swap_join_inputs, (no_)use_hash, (no_)use_merge, use_merge_cartesian, (no_)use_nl, and use_nl_with_index. I’ll cover these hints along with the join methods in Chapter 10.