Database Reference
In-Depth Information
CITY LNAME
FNAME CNO
::::::::::::::
::::::::::::::
::::::::::::::
::::::::::::::
LISBON JONES MARIA 2026477
LONDON JOHNS TONY 7477470
LONDON JONES MIKE 0037380
LONDON JONES MIKE 1012034
MADRID JONES TAPIO 0968431
::::::::::::::
::::::::::::::
::::::::::::::
::::::::::::::
Figure 4.3
Effect of a range predicate on the sequence.
before the BETWEEN predicate column LNAME as in index (FNAME,
...
)or
index (CITY, FNAME,
...
).
Regarding the first star, if CITY is the first column of the index, we will have
a relatively thin slice to scan (MC
=
1), depending on the CITY filter factor. But
the index slice will be even thinner with the index (CITY, LNAME,
...
); now
with two matching columns we only touch the index rows we really need. But
to do this and so benefit from a very thin index slice, no other column (such as
FNAME) can come between them.
So how many stars will our
ideal
index have? It can certainly have the third
star, but, as we have just seen, we can have
either the first star or the second
star, but not both!
In other words, we can either:
ž
Avoid a sort—by having the second star.
or
ž
Have the thinnest possible index slice, which will minimize not only the
number of index entries to be processed but also any subsequent process-
ing, in particular synchronous reads for the table rows—by having the
first star.
The presence of the BETWEEN predicate in our example, or indeed any other
range predicate, means
we cannot have both
; we cannot have a three-star index.
This implies therefore, that we have to make a choice between the first
and second stars. Usually this isn't a difficult choice since, as we will see in
Chapter 6, the first star tends to be the much more important star, although this
is not always the case as we will see at that time.
Let's consider for a moment an index (LNAME, CITY,
) as shown in
Figure 4.4. LNAME is a range predicate, which means it is the last column that
can participate in the index matching process, as we saw in Chapter 3. The equal
predicate CITY would
not be used
in the matching process. The result of this
would be only
one
matching column—a much thicker index slice than we would
have with the index (CITY, LNAME,
...
...
).
Search WWH ::
Custom Search