Question: I placed
a hint in my SQL but when I check the explain plan the hint is bring
ignored. I was taught that hints could not be ignored, so
why does Oracle SQL ignore my hint?
Answer: First,
remember that hints are a last resort measure and you should only use the
"good hints" like ordered and ordered_predicates, hints
that give the SQL optimizer information that it cannot easily gather from
the dbms_stats statistics.
Also, see my tips on
using hints properly.
You are correct that a SQL hint is an "optimizer
directive" and that, in theory, hints cannot be ignored, but in the real
world, hints are ignored frequently. Oracle even has a hidden
parameter called
_optimizer_ignore_hints
to make the optimizer ignore valid hints.
There are three main reasons why an invalid
hint attempt is being ignored, syntax errors, semantic errors and invalid
arguments:
Syntax error hints are ignored
Oracle does not report when a hint is mis-spelled
and because hints are embedded into comments, syntax errors are common, and
your hint attempt will be ignored. If the hint name spelled
incorrectly, then the hint will not be ignored. Here we see a query with a
misspelled hint name:
select /*+ indrex(erp, dept_idx) */ * from emp;
Semantic error hints are ignored
If a hint argument is spelled incorrectly, and not
found in the data dictionary then a semantic hint error will cause the hint
will not be ignored. Here we see a query with a misspelled table name,
erp instead of emp:
select /*+ index(erp, dept_idx) */ * from emp;
A hint will also be ignored if it is specified
improperly, with missing arguments. For example, the
table name is mandatory for an index hint. For example, the following hint
will be ignored because the table name is not specified in the index hint:
select /*+ index(dept_idx) */ * from emp;
Incompatible hints are ignored
Incongruent hints are ignored, such as a hint
that does not "make sense". In the context of the query, an
incompatible is indeed ignored, such as a query that specifies incompatible
access plans. For example, the following hints are
inconsistent and one of the hints will be ignored:
Ignored: Parallel hint with index hint
Ignored: Full hint with index hint
Hints with bad parameters are ignored
It's possible to have a valid hint (syntax and
semantics) that is ignored because of a lack of server resources. For
example, the use_hash hint is dependent upon there being enough RAM (as
defined by pga_aggregate_target or hash_area_size).
If these settings prohibit the optimizer from invoking a hash join, the
use_hash hint will be ignored.
select /*+
use_hash(e,b) parallel(b, 15) */
e.ename,
hiredate,
b.comm
from
smalltab e,
largetab b
where
e.ename = b.ename;
Here are other cases where an invalid hint will be
ignored:
Hint
|
When Ignored
|
cluster
|
When used with a noncluster table
|
hash
|
When used with a noncluster table
|
hash_aj
|
When no subquery exists
|
index
|
When the specified index does not exist
|
index_combine
|
When no bitmapped indexes exist
|
merge_aj
|
When no subquery exists
|
parallel
|
When a plan other than TABLE ACCESS FULL is
invoked
|
push_subq
|
When no subquery exists
|
star
|
When improper indexes exists on the fact table
|
use_concat
|
When no multiple or conditions exist in
the where clause
|
use_nl
|
When indexes do not exist on the tables
|
For complete steps on using hints and other
examples of hints that are ignored, see my book
"Oracle
Tuning: The Definitive Reference".
|
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|