Database Reference
In-Depth Information
this isn't enough to ensure that the path goes through node 122. In fact, if at node 1 it goes through node 11 instead
of node 12, the two hints would never have an effect. Therefore, to lead the path through node 122, you should add
another hint pruning the path from node 1 to node 11.
Figure 11-1.
Pruning of a decision tree
Something similar may happen with the query optimizer as well. In fact, hints are evaluated only when they
apply to a decision that the query optimizer has to take. No more, no less. For this reason, as soon as you specify a
hint, you may be forced to add several of them to ensure it works. And, in practice, as the complexity of the execution
plans increases, it's more and more difficult to find all the necessary hints that lead to the desired execution plan.
Specifying Hints
Hints are an Oracle extension. To not jeopardize the compatibility of the SQL statements with other database
engines, Oracle decided to add them as a special kind of comment. The only differences between comments and
hints are the following:
DELETE
,
INSERT
,
MERGE
,
SELECT
, and
UPDATE
keywords.
In other words, they can't be specified anywhere in the SQL statement like comments can.
•
Hints must follow immediately after
+
).
•
The first character after the comment delimiter must be a plus sign (
Syntactical errors in hints don't raise errors. If the parser doesn't manage to parse them, they're simply
considered real comments. Sometimes mixing comments and hints is also possible. Here are two examples that
show how to force a full table scan on the
emp
table for the query discussed in the previous section:
SELECT /*+ full(emp) */ *
FROM emp
WHERE empno = 7788
