Database Reference
In-Depth Information
Next we take the second index column, B. This too, like the BETWEEN
predicate we saw in Figure 3.1, is simple enough to be a matching column and
again be used to define the index slice.
Item 2 in Figure 3.2 now indicates that because B is a
range
predicate,
the remaining column C cannot participate in the matching process—it
cannot
further define the index slice. Item 3 goes on to say, however, that column C
can
avoid unnecessary table accesses because it can participate in the screening
process. In effect column C is
almost
as important as columns A and B; the
index slice scanned will just be a little thicker.
The most difficult issue is what constitutes a simple or a difficult predi-
cate; this depends very much on the DBMS, and we can safely leave this until
Chapter 6.
To summarize, the WHERE clause shown in Figure 3.2 has two matching
columns, A and B, which define the index slice used. In addition it has one other
column, C, which will be used for screening; thus the table will only be accessed
when it is
known
that a row satisfies
all three
predicates. If the predicate on
column B had been omitted, we would have a thicker index slice with only one
matching column, A; but column C would still be used for screening. If the
predicate on column B had been an equal predicate,
all three columns
would be
used for matching, resulting in a very thin index slice. Finally, if the predicate
on column A had been omitted, the index slice used would be the
whole index
,
with columns B and C both used for screening.
Access Path Terminology
Unfortunately, the terminology used to describe access paths is far from stan-
dardized—even the term
access path
itself, another term that is often used being
the
execution plan
. In this topic we will use
access path
when referring to the
way the data is actually accessed; we will use
execution plan
when referring to
the output provided by the DBMS by means of an EXPLAIN facility (described
below). This is a subtle distinction, and it is really of no consequence if the
terms are used interchangeably. Other, much more important issues are now
described.
Matching predicates are sometimes called
range delimiting
predicates. If a
predicate is simple enough for an optimizer in the sense that it is a
matching
predicate when a suitable index is available, it is called
indexable
(DB2 for
z/OS) or
sargable
(SQL Server, DB2 for VM and VSE). The opposite term is
nonindexable
or
nonsargable
. Some Oracle topics use the term
index suppression
when they discuss predicates that are too difficult for matching.
SQL Server uses the term
table lookup
for an access path that uses an index
but also reads table rows. This is the opposite of index only. The obvious way
to eliminate the table accesses is to add the missing columns to the index. Many
SQL Server topics call an index a
covering index
when it makes an index-only
access path possible for a SELECT call. SELECT statements that use a covering
index are sometimes called
covering SELECTs
.
Search WWH ::
Custom Search