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

ž The WHERE clause does not contain range predicates (BETWEEN,

> ,

,etc.).
ž The FROM clause refers to a single table only.
ž None of the predicates seems too difficult for the optimizer.

> =

The third of these items will be addressed in Chapter 6; the second in Chapter 8;
the first will be considered at a simple level now and in more depth in Chapter 6.

Range Predicates and a Three-Star Index

This example, SQL 4.3, requires the same information as before, but the required
customers are now within a range of values.

SQL 4.3

DECLARE CURSOR43 CURSOR FOR
SELECT CNO, FNAME

FROM CUST

WHERE LNAME BETWEEN :LNAME1 AND :LNAME2

AND
CITY = :CITY
ORDER BY FNAME

Let's try to design a three-star index for this CURSOR. Much of the reason-
ing is the same as that for CURSOR41, but the substitution of the BETWEEN
predicate for the = predicate will have significant implications. We will consider
the three stars in reverse order, which arguably reflects the level of difficulty of
understanding.
First the easy (albeit very important) star, the third star. As before, the
index will qualify for the third star by ensuring that all the columns specified
in the SELECT are in the index. No access to the table will be required and so
synchronous reads will not cause a problem.
The index will qualify for the second star by adding the ORDER BY column,
but this will only be true if it is added before the BETWEEN predicate column
LNAME, for example, with index (CITY, FNAME, LNAME). There is only
a single CITY value ( predicate), and so the result set will be provided in

FNAME sequence simply by using this index without the need for a sort. But
if the ORDER BY column is added after the BETWEEN predicate column
LNAME, for example, with index (CITY, LNAME, FNAME), the index rows
will not be in FNAME sequence and so a sort will be required; this can be
seen in Figure 4.3. Therefore to qualify for the second star, the FNAME must be

=