Optimizers Are Not Perfect - Relational Database Index Design and the Optimizers - page 263

rather harmless lie—until one day the company had to do a complicated recovery
procedure. Following this, one critical transaction ran very slowly for a couple
of hours because the system tables had reverted to the correct number of levels
for the index. The DBAs decided never to falsify statistics again.

ModifyIndex
The right index can be made more attractive, or the wrong index can be made
unattractive, for a SELECT. We have seen examples of both in this chapter. This
is often a good approach.

DO OPTIMIZER PROBLEMS AFFECT INDEX DESIGN?

The short answer is a qualified no. It is difficult for a cost-based optimizer to
resist a really good index. Therefore, when the optimizer chooses the wrong
index because of poor filter factor estimates, the best solution may be to make
the good index even better. The qualification that must always be borne in mind
is the one we discussed earlier in the section on partial index keys; a SELECT
statement may mistakenly begin to use a newly created index, even with cost-
based optimizers, although the previously chosen index was quite suitable. We
have to accept that this risk exists whenever we add a new index taking whatever
steps were appropriate to monitor inappropriate use. The risk is likely to be much
smaller when columns are added to an existing index.
The only way to totally avoid performance degradation due to index improve-
ments would be to use specific hints with every SQL call. This would be
extremely tedious and defeats the whole idea of leaving the access path choice
to the optimizer; it would also be rather risky as we are not perfect either. Like-
wise, it would negate any positive side effects of a cost-based optimizer; when an
index is improved to make one SQL call faster, many other calls tend to become
faster as well. The preferred approach is to design very good indexes as early
as possible, and provide the optimizer with ample information about column and
column group value distributions, at least the cardinalities of all partial index
keys . The cardinalities, unlike histograms and Top N/Bottom N, are useful for
equal predicates, even when the estimates are made with host variables. Consider
index (A,B,C) and WHERE A = :A AND B = :B.
The risk of negative side effects when improving indexing is analogous to
the dilemma that optimizer developers face—whenever they improve the cost
estimate formulas, many SQL calls around the world will become faster; unfor-
tunately a few will become slower.

EXERCISES

14.1. Rewrite the cursor shown in SQL 14.8 to a cursor whose access path is
ž MC 1

ž Index only
ž No sort

=