SQL Processing - Relational Database Index Design and the Optimizers - page 40

A matching predicate , by definition, participates in defining the slice; where to
enter, where to exit. The number of index rows is the same as the number of
table rows, with the exception that Oracle does not create index rows for NULL
values. Consider the following query (SQL 3.3):

SQL 3.3

SELECT PRICE, COLOR, DEALERNO

FROM CAR

WHERE MAKE = :MAKE

AND
MODEL = :MODEL

ORDER BY PRICE

Both simple predicates will be matching predicates if the index in Figure 3.7
is used. If the filter factor of the compound predicate is 0.1%, the index slice
accessed will be 0.1% of the whole index. Columns MAKE and MODEL are
matching columns . The index in Figure 3.7 appears to be fairly appropriate for
the SELECT shown in SQL 3.3, although it is far from being the best possible
index; at least the index slice to be scanned is rather thin.
The index is not as good, however, for the SELECT shown in SQL 3.4
because we have only one matching column. This is shown in Figure 3.8.
If index SEX, HEIGHT, WEIGHT, CNO is chosen to search for large men
using the SELECT shown in SQL 3.5, there will be only one matching predicate,
SEX, the filter factor of which is normally 0.5. The reason why only one predicate
participates in the matching process will be discussed later.
Consequently, if there are 1,000,000 rows in table CUST, the index slice to
be scanned will have 0 . 5 × 1 , 000 , 000 rows = 500 , 000 rows, a very thick slice!

MAKE,
MODEL,
YEAR

MC = 2, FF = 0.1%

1000 index rows
(0.1% of
1,000,000)

CAR

Figure 3.7 Two matching
columns—a thin slice.

1,000,000 rows