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

because it was the fastest one according to its estimates. The second part of this
chapter discusses this type of problem.
Nevertheless, we recommend the topic (1) from which the above quote was
taken, especially if the DBMS manuals or performance guides do not adequately
cover the limitations of their current optimizer.

OPTIMIZERS' COST ESTIMATES MAY BE VERY WRONG

Range Predicates with Host Variables
WHERE COL1 > :COL1

If the optimizer does not estimate the filter factor at execution, when the value of
the host variable :COL1 is known, it must resort to a default value. The default
value may be constant, 1/3, for instance, or it may depend in some way on
the cardinality of the column; for example, DB2 for z/OS V7 assumes that the
filter factor for a range predicate becomes smaller the higher the cardinality of
the column. In any case, it can be very wrong. This may result in it choosing,
among other index problems, the wrong index or the wrong table access order.
Making estimates every time an SQL call is executed consumes a large
amount of CPU time, but the default FF values often lead to very poor cost
estimates. Many products now offer a compromise option: The optimizer may
be asked to make the cost estimates the first time an SQL call is executed and
to store the chosen execution plan for reuse. This technique may well result
in better cost estimates than with the default filter factors, but it is somewhat
unpredictable; the values moved into the host variables at the first execution may
be atypical.
Figure 14.1 shows a scenario that may lead to very poor filter factor esti-
mates. Both predicates refer to host variables. For the range predicate A :A,

the optimizer has to use a default filter factor that might depend on the cardinality
of the column; for example, if column A has a high cardinality, the optimizer
could assume a low filter factor, such as 0.01%.

<

P C

SELECT A, B, C, D
FROM T
WHERE A < :A AND C = :C
ORDER BY A, B

TNO

A, B C

T

FF

10,000,000 rows

OPT ACT

Default value (a guess)
1 / CARD(C)

A < :A

0.01% 1%

QUBE
FF(OPT)

C = :C 10% 50%

FF(ACT)
100 s

Full table scan + sort
Index A, B (MC = 1) + T
Index C (MC = 1) + T + sort

100 s
10 s
20 s

Figure 14.1 Scenario that may
lead to very poor filter
factor estimate.

1000 s
100 s