Database Reference
In-Depth Information
SELECT /*+ full(emp) you can add a real comment after the hint */ *
FROM emp
WHERE empno = 7788
However, mixing comments and hints don't always work. For example, a comment added before a hint
invalidates it. The following query shows such a case:
SELECT /*+ but this one does not work full(emp) */ *
FROM emp
WHERE empno = 7788
Because comments can invalidate hints, I don't advise you to mix comments and hints. It's much better to
separate them.
Categories of Hints
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
,
optimizer_
features_enable
, and
opt_param
. I cover these hints in the section “Altering the Execution
Environment” later in this chapter, and I cover the
gather_plan_statistics
hint in Chapter
10. Note that these hints always overwrite the values set at the instance or session level when
they're 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_
)
expand_table
, (
no_
)
fact
, (
no_
)
merge
, (
no_
)
outer_join_to_inner
,
(
no_
)
push_pred
, (
no_
)
push_subq
, (
no_
)
native_full_outer_join
,
no_query_transformation
,
(
no_
)
rewrite
, (
no_
)
star_transformation
, (
no_
)
unnest
,
no_xmlindex_rewrite
,
no_xml_
query_rewrite
, and
use_concat
. I cover some of these hints later in this chapter and some
others in Chapters 14 and 15.
•
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 cover these hints along with access methods in Chapter 13.
•
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_
)
swap_join_
inputs
, (
no_
)
use_cube
, (
no_
)
use_hash
, (
no_
)
use_merge
,
use_merge_cartesian
, (
no_
)
use_nl
,
and
use_nl_with_index
. I cover these hints along with the join methods in Chapter 14.
