Deriving the Ideal Index for a SELECT - Relational Database Index Design and the Optimizers - page 62

RECOMMENDATION

Even with the current low cost of disk space, it will be unwise to mechanically
implement the best possible index for every SELECT because index maintenance
may make some programs too slow or overload the disk drives (which will affect
every program). The best possible index (derived with the two-candidate method
or by an index design tool) is a good starting point, but the three types of
superfluous indexes should be considered before deciding to create the ideal
index for a new SELECT.
Even if it were possible to derive the best index for every new SELECT, it
is more likely that in the real world this would be done only for the SELECT
statements that are found to be too slow, either by estimation or by measure-
ment, because of inappropriate indexing; but how are those statements to be
found? Chapter 5 recommends two simple methods for detecting slow access
paths at an early stage, when the SQL statements are being written or gen-
erated. Chapter 7 discusses an approach to exception monitoring, which will
reveal significant access path problems as well as other performance problems
in a production system.

EXERCISES

4.1. Derive index candidates A and B for the SELECT statement in SQL 4.5.
4.2. For each alternative, count the number of index rows that must be accessed by one
transaction with the worst input. The ORDERITEM table has 100,000,000 rows.

SQL 4.5

SELECT A, B, D, E

FROM ORDERITEM

WHERE B BETWEEN :B1 AND :B2 (FF = 1...10%)

AND
C = 1 (FF = 2%)

AND
E > 0 (FF = 50%)

AND
F = :F (FF = 0.1...1%)

ORDER BY A, B, C, F
WE WANT 20 ROWS PLEASE