Factors Affecting the Index Design Process - Relational Database Index Design and the Optimizers - page 87

It is essential to set up the measurement in such a way that the required pages are
not in memory or read cache at the start of the program. An interval of a couple
of hours between measurements is probably sufficient if the system is fairly busy
during the interval.
If the measured elapsed times are much longer than the predicted times, the
primary causes should be determined:
Slow disk drives or paths?
Overloaded disk drives?
Regardless of whether these figures are optimistic or even pessimistic, they can
be useful as relative performance indicators.

MULTIPLE THIN INDEX SLICES

Normally, an index is derived from a SELECT (and not the other way round),
but sometimes it may be desirable for an SQL statement to be rewritten to enable
the DBMS to use an existing index in the most efficient manner. This is because
the optimizers do not know everything!

SQL 6.1

DECLARE CURSOR61 CURSOR FOR
SELECT CNO, FNAME

FROM CUST

WHERE LNAME BETWEEN :LNAME1 AND :LNAME2

AND
CITY BETWEEN :CITY1 AND :CITY2

ORDER BY FNAME

A perfect optimizer would be able to construct an access path consisting of
several thin slices when the WHERE clause contains two range predicates, as in
CURSOR61 (refer to SQL 6.1 and Figure 6.1). In this example we assume that
the input consists of abbreviated values for both LNAME and CITY, say JO and
LO, from which the program derives the range values.
Because a range predicate column is the last matching column in the match-
ing process, making the DBMS read several thin slices (defined by both LNAME
and CITY) requires additional program logic. The predicate LNAME BETWEEN
:LNAME1 AND :LNAME2 must be changed to LNAME

=

:LNAME. This can
be done with an auxiliary table, maintained with a trigger, that contains all the
distinct values of the column LNAME in the customer table. Then when the
user enters the character string JO, the program reads one qualifying LNAME
at a time from the auxiliary table and opens CURSOR62 (SQL 6.2) with each
LNAME value.