Database Reference
In-Depth Information
Basically, although the method used to access the data is always the same, the method used to combine the
data to produce the result set is different. In this specific case, the two tables are very small, and consequently,
you wouldn't notice any real performance difference with these execution plans. Naturally, if you're dealing with
much bigger tables, that may not necessarily be the case. Generally speaking, whenever you process a large amount
of data, every small difference in the execution plan could lead to substantial differences in the response time or
resource utilization.
The key point here is to realize that the very same data can be extracted by means of different SQL statements.
Whenever you're optimizing a SQL statement, you should ask yourself whether other equivalent SQL statements exist.
If they do, compare their execution plans carefully to assess which one provides the best performance.
When to Use It
Whenever you're able to change the SQL statement, you should consider this technique. There is no reason for not
doing it.
Pitfalls and Fallacies
SQL statements are code. The first rule of writing code is to make it maintainable. In the first place, this means that
it should be readable and concise. Unfortunately, with SQL, because of the reasons explained earlier, the simplest or
most readable way of writing a SQL statement doesn't always lead to the most efficient execution plan. Consequently,
in some situations you may be forced to give up readability and conciseness for performance, although only when it's
really necessary and profitable to do so.
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, a hint is something that impels toward an action, rather
than merely suggests one. It seems to me that Oracle's choice of this word wasn't the best when naming this feature. In
any case, the name isn't that important. What hints can do for you is important. Just don't let the name mislead you.
■
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 isn't used by the query optimizer, it doesn't imply that a hint is merely a
suggestion. as i describe in a moment, there are cases where a hint is simply not relevant or legal, and therefore has no
influence over the execution plan generated by the query optimizer.
Caution
How It Works
The following sections describe what hints are, which categories of hints exist, and how to use them. The essential
thing to note before looking at the details is that using hints isn't as trivial as you might think. Actually, in practice,
it's quite common to see hints incorrectly applied.
