Organizing the Index Design Process - Relational Database Index Design and the Optimizers - page 291

elapsed time for a batch program must be evaluated case by case; but, to
prevent long lock waits, the alarm limit for the elapsed time between two
commit points should be the same as the alarm limit for local response
time. When an alarm limit is exceeded, you should improve the indexing
(semifat index, fat index, or ideal index). If the estimate (QUBE) is unsat-
isfactory even with ideal indexes (or if the number of required indexes
exceeds the table-specific maximum number of indexes, set in step 4),
you should make a more accurate estimate of the slow SELECTs, based
on the issues discussed in Chapter 15. If the estimated elapsed time is
then still too long you must resort to changing the table design as in step
2. In the worst case, you must negotiate the specifications with the users
or the hardware configuration with the management.
6. When the SQL statements are written, the programmer should apply the
basic question (BQ) or, if applicable, the basic join question (BJQ).
7. When the application is moved to the production environment, it is time
to do a quick explain review: analyze all SQL calls that cause a full table
scan or a full index scan. This review may reveal inadequate indexing or
optimizer problems.
8. When production starts, create an LRT-level exception report (spike report
or equivalent) for the first peak hour. When a long local response time
is not due to queuing or an optimizer problem, you should proceed as in
step 5.
9. Produce an LRT-level exception report at least once a week.
Variation1
If there is no time to do a QUBE for all programs, the estimations may be
limited to suspicious programs (such as browsing transactions and massive batch
programs).
Variation2
If LRT-level exception reporting is not possible, call-level exception reporting
should be used with a low threshold (say, 100 ms elapsed time). In addition,
visibly slow transactions should be analyzed with the QUBE if their SQL calls
are not caught by the call-level exception report.
Note
A table diagram is sufficient in step 2; a complete CREATE TABLE state-
ment is not needed. Therefore, index design can begin as soon as the first
program has been specified. Subsequent programs may require additional table
and index columns.