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

WHERE
CITY = :CITY

CUST Result

1,000,000 rows FF = 0.2% 2000 rows

WHERE
BD = :BD

CUST Result

1,000,000 rows FF = 0.27% 2700 rows

WHERE
CITY = :CITY
AND BD = :BD

Result

CUST

5 rows

1,000,000 rows

FF = 0.2% × 0.27%

Figure 3.5 No correlation.

WHERE
MAKE = :MAKE

CAR Result

1,000,000 rows FF = 1% 10,000 rows

WHERE
MODEL= :MODEL

CAR Result

1,000,000 rows FF = 0.1% 1000 rows

WHERE
MAKE = :MAKE
AND MODEL = :MODEL

Result

CAR

1000 rows

1,000,000 rows

FF = 0.1%

Figure 3.6 Strong correlation.

compound predicate is probably much lower than the product of the two simple
predicates.
Columns CITY and BD (birthday, mmdd) are probably not related. Therefore,
the filter factor of the compound predicate can be estimated by a process of
multiplication as shown in Figure 3.5.
An extreme example of column correlation is the table CAR, shown in
Figure 3.6, with columns MAKE and MODEL. The filter factors for predicates
MAKE

= :MAKE and MODEL = :MODEL may be 1/100 and 1/1000, respec-

tively. The filter factor for MAKE :MODEL is much

less than 1/100,000, perhaps as low as 1/1000 because, for example, to the best
of our knowledge, only Ford made Model Ts.

= :MAKE AND MODEL =