Database Reference
In-Depth Information
If the index rows contain all the columns referred to by the SELECT—as in
Figure 4.2—the index is given the third star. This eliminates table access: The
access path is
index only
.
Of these three stars, the third is often the most important one. Leaving a
column out of an index may lead to many slow random reads from the disk drive.
An index that has
at least
the third star is a
fat index
for the given SELECT.
A Fat Index
A fat index is an index that has at least the third star. It contains all the table
columns referred to by the SELECT and so enables index only access.
In simple cases, like CURSOR41, the structure of a three-star index is quite
straightforward.
ToQualifyfortheFirstStar
=
...
Pick the columns from all equal predicates (WHERE COL
). Make these
the first columns of the index—in any order. For CURSOR41, the three-star
index will begin with columns LNAME, CITY or CITY, LNAME. In both cases
the index slice that must be scanned will be as thin as possible.
ToQualifyfortheSecondStar
Add the ORDER BY columns. Do not change the order of these columns, but
ignore columns that were already picked in step 1. For example, if CURSOR41
had redundant columns in the ORDER BY, say ORDER BY LNAME, FNAME
or ORDER BY FNAME, CITY, only FNAME would be added in this step.
When FNAME is the third index column, the result table will be in the right
order without sorting. The first FETCH call will return the row with the smallest
FNAME value.
ToQualifyfortheThirdStar
Add all the remaining columns from the SELECT statement. The order of the
columns added in this step has no impact on the performance of the SELECT,
but the cost of updates should be reduced by placing volatile columns at the end.
Now the index contains all the columns required for an index-only access path.
The resulting three-star index is:
(LNAME, CITY, FNAME, CNO) or (CITY, LNAME, FNAME, CNO)
CURSOR41 is trivial in three respects:
Search WWH ::
Custom Search