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

BQ

Difficult
predicates

QUBE BE

Find the slow
SELECTs

Optimizer
issues

LRT-level
exception
monitoring

Call-level
exception
monitoring

FF estimates

Candidate A

Design good
candidates

Fat

Semifat Ideal

Candidate B

QUBE

BE

Joins and
subqueries

NLR

Worst-input
LRT

CPU busy

Make the right
choice

BJQ

I/U/D
elapsed time

Drive busy

Storage cost

Figure 16.4 Summary of the basic approach to index design.

NINE STEPS TOWARD EXCELLENT INDEXES

1. When the first version of the table design is completed (primary keys,
foreign keys, table row order), create the version 0 indexes : primary key
indexes, foreign key indexes, and candidate key indexes, if any.
2. Check the performance of the first version of the table design: Using the
QUBE, estimate the elapsed time of a few heavy transactions and batch
programs with ideal indexes . If the estimates are not satisfactory, combine
tables that have a 1 : 1 or 1 : C (1 to 0 or 1) relationship and add redundant
data to the dependent tables in a 1 : M (one to many) relationship.
3. When the table structure appears stable, you may add any obvious
indexes—based on knowledge of the application.
4. If a table is extremely volatile (say, more than 50 rows inserted, replaced,
or deleted per second), you should estimate, using the QUBE, how many
indexes the table tolerates.
5. When the database processing of a program (transaction or batch) is
known, calculate the worst input QUBE using the latest database ver-
sion. If the estimated local response time of a transaction exceeds the
application-specific alarm limit (e.g., 2 s), the current database version
is not satisfactory for that program. The acceptability of the estimated