Proactive Index Design - Relational Database Index Design and the Optimizers - page 64

even if the matching index process is inadequate (the first star), index
screening will take place to ensure table access will only occur when it is
known that all the selection criteria have been met.
ž If this does not result in adequate performance, the next alternative is to
add enough columns to the index to make the access path index only. This
will produce a fat index that will eliminate all table access.
ž If the SELECT is still too slow, a new index should be designed using the
two-candidate algorithm introduced in Chapter 4. This, by definition, will
be the best index possible.
How do we determine whether the first alternative (semifat index) or the second
one (fat index) makes the SELECT fast enough even with the worst input?
If access to the production database or a production-like test database is
available, one index may be created at a time and the response time measured
with the worst input. To ensure that the measured response times are close to
those in normal production, the buffer pools must be considered and the buffer
pool hit ratios of each transaction observed. The first test transaction will probably
find no pages in the buffer pool, so the number of disk reads will be higher than
in a normal environment. Furthermore the first transaction accessing a new index
must wait for the files to be opened. On the other hand, the second transaction
accessing the same index may have a buffer pool hit ratio of 100% (no disk
reads) if the same input is repeated. To obtain representative response times,
each index alternative should be tested after warm-up transactions have been
submitted; a few typical inputs (but not the worst case) are entered to open the
files and to bring many of the nonleaf index pages into the database buffer pool.
Then the response time of the transaction with the worst input will probably have
a representative response time, at least as far as the CPU and disk read service
times are concerned.
It is actually far less tedious to employ the second technique, which will be
addressed shortly, the QUBE to evaluate the alternatives.
If neither the measurement nor the QUBE approach is undertaken, the fat
index alternative should be adopted, followed by the use of exception reports
immediately after production cutover to detect the cases where even a fat index
is not adequate. Then, if necessary, the best possible (new) index will need to be
designed for the slow SELECTs.

Warning

An affirmative answer to the BQ does not guarantee adequate performance.
Remember that the objective of the BQ is to simply ensure that we can at least
do index screening in order to minimize the access to the table—no more.
For example, assume a SELECT with WHERE B :C and

the only useful index for the SELECT being (A, B, C). The index does indeed
contain all the predicate columns for the SELECT, and so the SELECT does
not raise an alarm with the BQ. The access path, however, will be a full index

= :B AND C =