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

4. Add all remaining columns referred to by the SELECT statement, in any
order (but start with the nonvolatile columns).
Example:CURSOR43
Candidate A will be (CITY, LNAME, FNAME, CNO).
Candidate A causes a sort for CURSOR43 because FNAME comes after the
range predicate column, LNAME.
Candidate B
If candidate A leads to a sort with the given SELECT, candidate B is designed.
By definition, the second star is more important than the first star for candidate B.
1. Pick the equal predicate columns that are not too difficult for the opti-
mizer. These are the first index columns—in any order
2. Add the ORDER BY columns in the correct order (and with DESC if
an ORDER BY column has DESC). Ignore columns that were already
picked in step 1.
3. Add all remaining columns referred to by the SELECT statement, in any
order (but start with the nonvolatile columns).
Example:CURSOR43
Candidate B will be (CITY, FNAME, LNAME, CNO).
We now have two candidates for the best index, one with the first star,
one with the second star. To determine which will be the best index, we could
analyze the performance of each index as we did at the beginning of this chapter.
This would be quite time consuming, however, and Chapter 5 presents a simpler
method (the QUBE) for estimating which candidate will provide the faster access
path for the SELECT.
It is important to realize that all we have done so far is to design the ideal
or best index. Whether this would be practical or not, at this stage we are not
in a position to say.
Sorting Is Fast Today—Why Do We Need Candidate B?
Sorting has become much faster over recent years. These days much of the sort
process takes place in memory, such that with the fastest current processors the
CPU time per sorted row is roughly 10 µ s. Thus, the elapsed time for sorting
50,000 records could be as short as 0.5 s, possibly acceptable as a response time
component of an operational transaction but quite high as a contributor to the
CPU time.
Because sorting is so fast with current hardware, candidate A will be as
fast or faster than candidate B if a program fetches all the result rows. For the
programmer this is the most convenient solution. Many environments provide
flexible commands for browsing the result table.