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

Table 5.2 Comparison of Worst Input QUBEs for Example 2

Type Index LRT Maintenance

Existing LNAME, FNAME or CITY 17 m —

Semifat CITY, LNAME 101 s U LNAME + 10-20 ms

Semifat LNAME, FNAME, CITY 102 s U CITY + 10-20 ms

Fat CITY, LNAME, FNAME, CNO 1 s U L & FNAME + 10-20 ms

Fat LNAME, FNAME, CITY, CNO 2 s U CITY + 10-20 ms

2 * A CITY, LNAME, FNAME, CNO 1 s U L & FNAME + 10-20 ms

2 * B CITY, FNAME, LNAME, CNO 2 s U L & FNAME + 10-20 ms

LRT is for the worst input QUBE; I = insert; D = delete; U = update

WHEN TO USE THE QUBE

Ideally, the QUBE should be applied during the design of a new program. If the
worst input local response time with the current or planned indexes exceeds, say
2 s, index improvements should be considered.
The alarm limit for batch jobs is job specific, but it is very important to check
the maximum time between commit points in all batch programs. This should
probably be less than 1 or 2 s, otherwise the batch job may cause excessive lock
waits in transactions and other batch jobs.
The QUBE could even be applied before designing the program. It is simply
necessary to know how the database must be processed with the worst input; for
example, read 10 nonadjacent rows from table A, add 2 adjacent rows to table
B, and so on. In fact, it is wise not to design the program until the estimate is
satisfactory. Sometimes it may be necessary to make the program structure more
complex in order to achieve adequate performance.
Chapter 7 will discuss the use of the QUBE when problems arise after the
program has gone into production.