Optimizers Are Not Perfect - Relational Database Index Design and the Optimizers - page 251

The equal predicate C = :C is much easier for the optimizer. If the cardinality
of column C is 10, the optimizer assumes the filter factor is 10%, which is the
correct value for the average case. However, the worst input filter factor for
C = :C might be 50%.
In Figure 14.1 the first FF index estimate is very low and the second one
low . Based on these poor estimates, the optimizer would make the wrong choice.
We may help the optimizer by forcing it to choose the access path at each
execution when the actual host variable values are known. This would probably
solve the problem, at least if the optimizer had a histogram available that showed
the distribution of the values for column A. Knowing the minimum and maximum
values may not be sufficient if the distribution is skewed as shown in Figure 14.2.
An access path hint would avoid the overhead involved in estimating the
cost at each execution. This would be sensible if index C was indeed the best
choice with all input.
The best solution for this example though, as with many real-life situations, is
to make the best index even better as shown in Figure 14.3. This index enhance-
ment not only improves the LRT; it also eliminates the need to use the tricks
described above. A really good index is hard to resist with a cost-based optimizer.

Skewed Distribution

In the example shown in SQL 14.5, we are assuming a single-column index,
SEX, and that 99.9% of Sumo wrestlers are men.

SQL 14.5

SELECT AVG (WEIGHT)

FROM SUMO

WHERE SEX = :SEX

Distribution of Values in Column A

%

A < :A

Figure 14.2 Histogram
showing the distribution of
the values for column A.

A