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

Chapter 5

Proactive Index Design

ž Introduction to the two fast and easy-to-use techniques that will be used
extensively throughout this topic: the Basic Question (BQ) and the Quick
Upper-Bound Estimate (QUBE)
ž Application of the principles discussed in Chapter 4 to the SQL
application design process
ž Use of these techniques to determine the merits and demerits of potential
index designs and to determine improvements necessary to provide
adequate SQL performance
ž Assessment of the suitability of any improvements considered
ž Discussion of when the QUBE should be used

DETECTION OF INADEQUATE INDEXING

As soon as the specifications for a program are complete, we should determine
whether the current indexes are adequate for the new program. To do this, we
will now consider two simple, fast, and practical approaches, namely
1. Basic Question (BQ) (8)
2. Quick Upper-Bound Estimate (QUBE)

BASIC QUESTION (BQ)

Even the busiest programmer has time to do this evaluation. For each SELECT
statement the answer to the following question must be considered as shown in
the following steps:

Is there an existing or planned index that contains all the columns referenced
by the WHERE clause (a semifat index)?

If the answer is no, we should first consider adding the missing predicate
columns to an existing index. This will produce a semifat index where,

ž