Databases Reference
In-Depth Information
Chapter 7: Hints
SQL is a declarative language; it only defines
what
data to retrieve from the database. It
doesn't describe the manner in which the data should be fetched. That, as we know, is
the job of the Query Optimizer, which analyzes a number of candidate execution plans
for a given query, estimates the cost of each of these plans, and selects an efficient plan by
choosing the cheapest of the choices considered.
But there may be cases when the execution plan selected is not performing as you have
expected and, as part of your query troubleshooting process, you may try to find a better
plan yourself. Before doing this, keep in mind that, just because your query does not
perform as you have expected, that does not mean that a better plan is always possible.
Your plan may be an efficient one, but this is, in fact, probably an expensive query to
perform, or your system may be having performance bottlenecks which are impacting the
query execution.
However, although the Query Optimizer does an excellent job most of the time, it does
occasionally fail to produce an eicient plan, as we've seen throughout this topic. That
being said, even in the cases when you're not getting an efficient plan, you should still try
to distinguish between the times when the problems arise because you're not providing
the Query Optimizer with all the information it needs to do a good job, and those when
the problems are a result of a Query Optimizer limitation. Part of the focus of this topic
so far has been to help you to provide the Query Optimizer with the information it needs
to produce an efficient execution plan, such as the right indexes or good quality statis-
tics, and also how to troubleshoot the cases when you are not getting a good plan. This
chapter will cover what to do if you hit a Query Optimizer limitation.
Having said that, there might be cases when the Query Optimizer just gets it wrong
and, in such cases, we may be forced to resort to the use of
hints
. These are essentially
optimizer directives which allow us to take explicit control over the execution plan for a
given query, with the goal of improving its performance. In reaching for a hint, however,
