Database Reference
In-Depth Information
Chapter 14
Optimizers Are Not Perfect
ž Optimizers do not always see the best alternative
ž Matching and screening problems
ž Non-BT predicates
ž Unnecessary sorts
ž Unnecessary table touches
ž Optimizers' cost estimates are sometimes wrong, perhaps disastrously so
(and how we can help)
ž Range predicates
ž Skewed distribution
ž Correlated columns
ž Properties of partial index keys
ž Optimizers' cost estimation formulas considerations
ž I/O estimation
ž CPU estimation
ž Impact of pools and cache
ž Helping the optimizer with estimation-related problems
ž Optimize every time with actual values
ž Access path hints
ž Redundant predicates
ž Falsifying statistics
ž Modifying indexes
ž Effect of optimizer problems on index design
INTRODUCTION
Sometimes a SELECT call can be unbelievably slow even though suitable indexes
have been created for it. If the dominant component is service time and not queu-
ing time, the optimizer has probably chosen a poor access path. The optimizer
of a relational DBMS has a difficult task; it must choose the right access path
based solely on the statistics collected for the characteristics of the tables and
Search WWH ::




Custom Search