Section 11.7. Optimizer Directives


11.7. Optimizer Directives

I shall conclude this chapter with a cautionary note about optimizer directives . An SQL optimizer can be compared to the program that computes shutter speed and exposure in an automated camera. There are conditions when the "auto" mode is no longer appropriatefor instance, when the subject of the picture is backlit or for the shooting of night scenes. Similarly, all database systems provide one way or another to override or at least direct decisions taken by the query optimizer in its quest for the Dream Execution Path. There are basically two techniques to constrain the optimizer:

  • Special settings in the session environment that are applied to all queries executed in the session until further notice.

  • Local directives explicitly written into individual statements.

In the latter case the syntax between products varies, since you may have these directives written as an inherent part of the SQL statement (for instance force index(...) with MySQL or option loop join with Transact-SQL), or written as a special syntax comment (such as /*+ all_rows */ with Oracle).

Optimizer directives have so far been mostly absent from this book, and for good reasons. Repeatedly executing queries against living data is, to some degree, similar to repeatedly photographing the same subject at various times of day: what is backlit in the morning may be in full light in the afternoon. Directives are destined to override particular quirks in the behavior of the optimizer and are better left alone. The most admissible directives are those directives specifying either the expected outcome, such as sql_small_result or sql_big_result with MySQL, or whether we are more interested in a fast answer, as is generally the case in transactional processing, with directives such as option fast 100 with SQL Server or /*+ first_rows(100) */ with Oracle. These directives, which we could compare to the "landscape" or "sports" mode of a camera, provide the optimizer with information that it would not otherwise be able to gather. They are directives that don't depend on the volume or distribution of data; they are therefore stable in time, and they do add value. In any case, even directives that add value should not be employed unless they are required. The optimizer is able to determine a great deal about the best way to proceed when it is given a properly written query in the first place. The best and most simple example of implicit guidance of the optimizer is possibly the use of correlated versus uncorrelated subqueries. They are to be used under dissimilar circumstances to achieve functionally identical results.

One of the nicest features of database optimizers is their ability to adapt to changing circumstances. Freezing their behavior by using constraining directives is indicative of a very short-term view that can be potentially damaging to performance in the future. Some directives are real time-bombs, such as those specifying indexes by name. If, for one reason or another a DBA renames an index used in a directive, the result can be disastrous. We can get a similarly catastrophic effect when a directive specifies a composite index, and this index is rebuilt one day with a different column order.

Optimizer directives must be considered the private territory of database administrators. The DBA should use them to cope with the shortcomings of a particular DBMS release and then remove them if at all possible after the next upgrade.

Let me add that it is common to see inexperienced developers trying to derive a query from an existing one. When the original query contains directives, beginners rarely bother to question whether these directives are appropriate to their new case. Beginners simply apply what they see as minor changes to the select list and the search criteria. As a result, you end up with queries that look like they have been fine-tuned, but that often follow a totally irrelevant execution path.

The good plan that is forced upon a query today may be disastrous tomorrow.




The Art of SQL
The Art of SQL
ISBN: 0596008945
EAN: 2147483647
Year: N/A
Pages: 143

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net