Reactive Index Design - Relational Database Index Design and the Optimizers - page 106

path; 90% of these statements may have excellent performance with the sort
contributing an insignificant amount to the response time. Thus, the large number
of false alarms may make this review method somewhat unproductive.
There are many database consultants who consider the sort to be the enemy.
We believe that those consultants who emphasize death by random I/O are
more credible.
Cost Estimate
The EXPLAIN feature of several DBMSs display the optimizer's estimate for
the local response time, or at least the CPU time, for the chosen access path.
Some products, SQL Server 2000, for instance, show the estimated CPU time
and I/O time for each step in the access path. In a simple case there might be
several steps such as:
1. Retrieve index rows.
2. Sort pointers.
3. Retrieve table rows.
4. Sort result rows.
Experience has shown that it can be dangerous to place too much reliance
on the optimizer's cost estimates. After all, the optimizer is only producing the
cost estimate in order to choose the fastest alternative access path available. The
estimates sometimes give false alarms, yet don't show all the alarms; but it is an
easy and fast tool to use, and enables an early check to be made. Consequently
SQL statements with unusually high cost estimates—perhaps the 20 statements
that have the highest cost estimates belonging to a new application—should be
checked. It is quite likely that inadequate indexing or optimizer problems will
be discovered.
Unfortunately, two serious issues limit the value of using the cost estimate
in this way:
1. The optimizer estimate for the local response time may be very different
from the actual value (refer to Chapter 14).
2. Whenever a predicate uses host variables (which is very common, of
course), the optimizer estimate for the filter factor is based on the aver-
age input, or even worse, a default value. To obtain the more valuable
worst-case estimate, the host variables in the EXPLAIN must be replaced
by the worst input literals. This is a tedious operation that requires appli-
cation knowledge.
EXPLAIN is an indispensable tool for analyzing optimizer problems. This is its
raison d'etre , not index design. Airport security does not only check suspicious
looking passengers at the airport!