Database Reference
In-Depth Information
LNAME
CITY
FNAME CNO
::::::::::::::
::::::::::::::
::::::::::::::
::::::::::::::
JOHNS ZURICH BERNHARD 9696969
JONES LONDON JAMES 1234567
JONES LONDON MIKE 0037380
JONES LONDON MIKE 1012034
JONES MADRID
TAPIO
0968431
::::::::::::::
::::::::::::::
::::::::::::::
::::::::::::::
Figure 4.4 Only the third star for CURSOR43 with MC = 1.
ALGORITHM TO DERIVE THE BEST INDEX FOR A SELECT
The ideal index will be a three-star index for the reasons discussed above. We
have seen, however, that with a range predicate this may not be possible; we may
have to sacrifice (probably) the second star in order to achieve a thin index slice
(the first star). The best index will then only have two stars. This is why we are
careful to distinguish between ideal and best . The ideal in this case would just
not be possible. Taking this into account, we can formulate the rules for creating
the best (perhaps not ideal ) index under all conditions. The result might have
three stars or two stars.
First a fat index (third star) is designed, where the scanned index slice is
as thin as possible (first star). If this index does not imply a sort (second star),
it is a three-star index. Otherwise it will only be a two-star index, having sacri-
ficed the second star. Another candidate should then be derived that eliminates
sorting, thereby having the second star but having sacrificed the first star. One
of the resulting two star indexes will then be the best possible index for the
given SELECT.
The algorithm to derive the best index for a SELECT follows.
Candidate A
1. Pick the equal predicate columns that are not too difficult for the optimizer
(discussed in Chapter 6). These are the first index columns—in any order.
2. The next index column is the column in the most selective range predi-
cate, if any. Most selective means the lowest filter factor with the worst
input. Only consider the range predicates that are not too difficult for the
optimizer (discussed in Chapter 6).
3. 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 or 2.
Search WWH ::




Custom Search