Databases Reference
In-Depth Information
3.
Force certain plan choices using “query hints.”
As we have seen, the process of query optimization is very complex and can involve
very deep and exhaustive searching to find an ideal plan. The more complex the query
and the more design attributes to choose from, the more choices and complexity the
query optimizer faces. In fact, the time required for the query optimization process can
be significant. Most database engines try to automatically determine the depth of the
optimization search (or use a default search depth that is reasonable for most queries,
most of the time). However, there are often overrides. When the query optimizer
appears to be making wrong choices and selecting a suboptimal query execution plan,
one strategy is to modify the search depth by either increasing or decreasing it, hoping a
different and superior plan will be selected.
Another strategy to bias plan selection is to modify the statistics that the optimizer
relies upon when selecting the query execution plans. Many database vendors provide
user-updatable views for these statistics. By default, these user-modifiable views show
the same values for the object statistics as the primary catalogs storing the actual statis-
tics. When these views are updated, the new values supercede the actual statistics. In this
way the database designer can bias the access of an object. For example, you can make
an index appear more desirable by increasing its clustering statistics or by decreasing its
size (number of tree levels or number of pages).
Finally, the most explicit way of biasing query execution plans is through query
hints. SQL Server, Oracle, and DB2 all provide a facility for query hints, though the
interfaces vary significantly. Hints should be used with caution and only if the query
optimizer appears to have serious trouble finding a reasonable query execution plan for
a query. Hints are most commonly used to force index access or a specific join method,
or to essentially freeze a volatile query execution plan for a complex query, in order to
reduce runtime risk of a sudden poor plan choice on a production system.
11.7.2 Introduction to Query Hints
What follows are some quick syntax insights on using query hints for the major ven-
dors, without any attempt at being comprehensive.
SQL Server query hints can be added directly to the SQL text by adding the
OPTION clause. This has the following format:
OPTION (
hint1
[, ...
hintn]
)
The following example uses the OPTION clause to force the query optimizer to
use a hash join method when joining two tables.
