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

MC = 8 MC = 2

C1,C2,C3,
C4,C5,C6,
C7,C8,C9

C1,C3,C10

CARD(C1,C2,...,C8) CARD(C1,C3)

OPTIMIZER
ESTIMATE

OPTIMIZER
ESTIMATE

ACTUAL ACTUAL

20,000 20,000 500 500,000

Figure 14.5 Optimizer does not know everything.

DBAs were shocked when they discovered that this tool generated SELECT,
SQLB, shown in SQL 14.7, now used the new index instead of the primary
key index; the latter appeared to be much better suited, using eight matching
columns instead of only two with the new index, as shown in Figure 14.5. The
DBAs were forced to drop the new index while they investigated the behavior
of the optimizer.

SQL 14.6

SQLA

SELECT many columns

FROM SALES

WHERE C1 = :C1

AND
C3 = :C3

AND
C10 = :C10

SQL 14.7

SQLB

SELECT many columns and sums
FROM SALES
WHERE C1 = :C1 AND C2 = :C2 AND C3 = :C3 AND C4 = :C4 AND
C5 = :C5 AND C6 = :C6 AND C7 = :C7 AND C8 = :C8
ORDER BY...
GROUP BY...