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

one of the predicates is too difficult for the optimizer, resulting in MC = 1 (one

matching column), changing the MC value to 2 would not help!
Compared to the Oracle and SQL Server implementations, the DB2 hint is
somewhat tedious to use. The developers recommend it mainly for fast fallback
to a previous (better) access path. This is easy to do if the old access plans
arestoredinthePLANTABLE. Furthermore, influencing generalized programs
(such as SAP and Peoplesoft) is easier with the DB2 approach because the source
programs are not changed. When the SQL calls cannot be changed, the alternative
approach requires the use of VIEWs with hints or storing the SQL call with its
hints in system tables ( stored outlines in Oracle).

RedundantPredicates
This is a clever, but dirty, way to influence the optimizer by making an unwanted
alternative look expensive. Consider WHERE A < :A AND C = :C as shown
in Figure 14.1, and assume the optimizer chooses index A because of poor FF
estimates. A redundant predicate may be added, such as 0 = 1, that does not
affect the result but makes index A unattractive:

WHERE (A < :A OR0=1)ANDC=:C

:A non-BT; now index A has
no matching columns. Even with the poor FF estimates, the optimizer makes a
lower cost estimate for the better alternative, index C.
The dangers of using this approach are obvious:
1. The optimizer may one day be intelligent enough to remove the redun-
dant predicate.
2. There will be only one matching column when A is added to index C
(Fig. 14.3).
3. What will our grandchildren think if they discover we have written code
like this?

The redundant predicate makes predicate A <

FalsifyingStatistics
The statistics for the optimizer are normally held in tables that can be updated
with SQL. This is useful for making test databases look like production databases
to catch optimizer problems early. It is also possible to influence the optimizer
by falsifying the statistics in the production system. The table access order in a
join, for instance, could be influenced by changing the number of rows in a table
from perhaps 10,000 to 100,000. This is, of course, even more risky than using
redundant predicates because this sort of change may affect many SQL calls.
This trick has been used by several companies whose optimizer had chosen a
wrong index, perhaps because a better index happened to have one more level (the
optimizer assuming that only the root page would stay in the buffer pool). One
installation affected by this changed the number of levels for a particular index
in the statistics, so the optimizer would choose the right index. This seemed a