Yesterday, while reading a swiss italian dialect tranlation of Le petit prince (entitled Ul principe pinin), I noticed a very interesting discussion between the little prince and the king (the inhabitant of the first planet visited by the little prince…). Here is the most important part of that discussion (you can read the whole text, in English, here):
“If I ordered a general to fly from one flower to another like a butterfly, or to write a tragic drama, or to change himself into a sea bird, and if the general did not carry out the order that he had received, which one of us would be in the wrong?” the king demanded. “The general, or myself?”
“You,” said the little prince firmly.
“Exactly. One must require from each one the duty which each one can perform,” the king went on.
When I read that part of the text, I immediately thought to the most frequent mistake that people do when they use Oracle hints. I.e. asking through a hint something impossible to the query optimizer and, then, wondering why it does not work as expected. Who is wrong? The person who wrote the hint or the query optimizer? Most of the time, alas, the person who wrote the hint. To further emphasize this point, below you find the introductory text that I published in TOP (pages 252-254) to introduce hints.
Hope this helps the people who are still looking for an answer to the question “What are hints?”.
*****
According to the Merriam-Webster online dictionary, a hint is an indirect or summary suggestion. In Oracle’s parlance, the definition of a hint is a bit different. Simply put, hints are directives added to SQL statements to influence the query optimizer’s decisions. In other words, it is something that impels toward an action, not merely suggesting one. It seems to me that Oracle’s choice of this word was not the best when naming this feature. In any case, the name is not that important. What hints can do for you is. Just don’t let the name mislead you.
Caution: Just because a hint is a directive, it doesn’t mean that the query optimizer will always use it. Or, seeing it the other way around, just because a hint is not used by the query optimizer, it doesn’t imply that a hint is merely a suggestion. As I will describe in a moment, there are cases where a hint is simply not relevant or legal, and therefore, it has no influence over the execution plan generated by the query optimizer.
While optimizing 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 is 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 reduce the number of execution plans considered by the query optimizer. Basically, with a hint you tell the query optimizer which operations should or should not 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 table emp is a heap table and its column empno is indexed, the query optimizer considers at least two execution plans. The first is to completely read the table EMP 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 cannot tell the query optimizer “I want a full table scan on table emp, 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 table emp, 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 7-1. Note that even if the query optimizer works with decision trees, this is a general example not directly related to Oracle. In Figure 7-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 is 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 this is not 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.
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 is more and more difficult to find all the necessary hints that lead to the desired execution plan.