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

P C SELECT A, B, C, D

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

TNO A, B C , A, B, D

FF T

10,000,000 rows

ACT

OPT

A < :A

0.01% 1%

QUBE
FF(OPT)

C = :C 10%

50%

FF(ACT)
100 s 100 s

10 s
0.01 s

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

1000 s
0.5 s

Figure 14.3 Making the
best index even better.

Even if the distribution of the values in column SEX is stored in the system
tables, the optimizer cannot make use of it if the access path selection is not
done repeatedly at every execution time. When the access path is selected with
a host variable in the predicate, the optimizer has to assume that the filter factor
will be 1/cardinality

= .

5. It will then select a full table scan.
If the only purpose of the index is to find the exceptions, the solution is
simple—a literal should be used instead of a host variable. For predicate SEX

0

=
'F', the optimizer would assume the correct filter factor 0.1%. Then it would
probably decide to use the index.
If the user sometimes asks for the average weight of female Sumo wrestlers
and sometimes that of males, there are solutions that enable the best access path
to be chosen for both cases, without repeated cost estimates:
ž Two SELECT statements are written, one with SEX

= 'F', the other with

'M' and the application program allowed to choose the appropriate
SELECT depending on the user input.
ž If static SQL with bind is not available, use an option that stores the
statements and their access paths in a pool for reuse. Be careful that the
optimizer does not convert the literals into variables, otherwise it will
use the same access path for both cursors. This option is called cursor
sharing in Oracle. If the INIT.ORA parameter CURSOR SHARING =
FORCE, all statements that differ only in the values of the literals, use
the same execution plan. CURSOR SHARING = EXACT disables cur-
sor sharing; there will be a separate execution plan for each literal. With
CURSOR SHARING = SIMILAR, the optimizer will examine the statis-
tics and may use different execution plans.
Thus, another old recommendation has become a myth:
Do not create an index for a column with a low cardinality.
By the way—you might like to consider the following question:

SEX =