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