Database Reference
In-Depth Information
single matching column in fact defines the thickness of our slice. If there had
been a second column, both in the WHERE clause and in the index, such that the
two columns together were able to define an even thinner slice of the index, we
would have two matching columns. Less index processing would be required,
but of even greater importance, fewer synchronous reads to the table would
be required.
Index Screening and Screening Columns
Sometimes a column may indeed be in both the WHERE clause and in the index,
but for one reason or another it is not able to further define the index slice—this is
a very complex area and one that will have to be addressed continually throughout
this topic; suffice it to say at this time that not all columns in the index are able
to define the index slice. Such columns, however, may still be able to reduce
the number of synchronous reads to the table and so play the more important
part. We will call these columns screening columns , as indeed do some relational
database systems, because this is exactly what they do. They avoid the necessity
of accessing the table rows because they are able to determine that it is not
necessary to do so by their very presence in the index.
Without going into too much detail at this stage, we can at least provide an
initial simple understanding of how one can determine whether predicates may
participate in this matching and screening process.
Figure 3.2 shows a WHERE clause consisting of three predicates, the
columns of each being part of the index shown; column D, the last column
in the index, is not present in a predicate.
We must take the first index column A. This column is present in an equal
predicate, the simplest predicate of all. This is obviously a matching column and
will be used to define the index slice.
Examine index columns
from leading to trailing
A, B, C, D
1. In the WHERE clause, does a column
have at least one simple enough
predicate referring to it?
WHERE
A = :A
AND
B > :B
AND
C = :C
If yes, the column is an M column .
If not, this column and the remaining
columns are not M columns .
2. If the predicate is a range predicate ,
the remaining columns are not
M columns .
3. Any column after the last M column is an S column if there
is a simple enough predicate referring to that column.
Figure 3.2 Predicting matching and screening columns.
Search WWH ::




Custom Search