Database Reference
In-Depth Information
What Are Hints?
While working on a SQL statement, the query optimizer may have to take a lot of execution plans into account. In
theory, it should consider all possible execution plans. In practice, except for simple SQL statements, it's not feasible
to consider too many combinations in order to keep the optimization time reasonable. Consequently, the query
optimizer excludes some of the execution plans
a priori
. Of course, the decision to completely ignore some of them
may be critical, and the query optimizer's credibility is at stake in doing so.
Whenever you specify a hint, your goal is to either change the execution environment, activate or deactivate
a specific feature, or reduce the number of execution plans considered by the query optimizer. Except when you
change the execution environment, with a hint you tell the query optimizer which operations should or shouldn't be
considered for a specific SQL statement. For instance, let's say the query optimizer has to produce the execution plan
for the following query:
SELECT *
FROM emp
WHERE empno = 7788
If the
emp
table is a heap table and its
empno
column is indexed, the query optimizer considers at least two
execution plans. The first is to completely read the
emp
table through a full table scan:
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| EMP |
----------------------------------
The second is to do an index lookup based on the predicate in the
WHERE
clause (
empno = 7788
) and then,
through the rowid found in the index, to access the table:
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP |
| 2 | INDEX UNIQUE SCAN | EMP_PK |
----------------------------------------------
In such a case, to control the execution plan provided by the query optimizer, you could add a hint specifying
to use either the full table scan or the index scan. The important thing to understand is that you can't tell the query
optimizer, “I want a full table scan on the
emp
table, so search for an execution plan containing it.” However, you can
tell it, “If you have to decide between a full table scan and an index scan on the
emp
table, take a full table scan.” This
is a slight but fundamental difference. Hints can allow you to influence the query optimizer when it has to choose
between several possibilities.
To further emphasize this essential point, let's take an example based on the decision tree shown in Figure
11-1
.
Note that even if the query optimizer works with decision trees, this is a general example not directly related to Oracle
Database. In Figure
11-1
, the aim is to descend the decision tree by starting at the root node (1) and ending at a leaf
node (111-123). In other words, the goal is to choose a path going from point A to point B. Let's say that, for some
reason, it's necessary to go through node 122. To do so, two hints, in the Oracle parlance, are added to prune the paths
from node 12 to the nodes 121 and 123. In this way, the only path going on from node 12 leads to the node 122. But
