Chapter 14. Optimizers Are Not Perfect

  1. Tapio Lahdenmäki,
  2. Michael Leach

Published Online: 27 JAN 2005

DOI: 10.1002/0471721379.ch14

Book Title

Relational Database Index Design and the Optimizers: DB2, Oracle, SQL Server, et al.

Relational Database Index Design and the Optimizers: DB2, Oracle, SQL Server, et al.

Additional Information

How to Cite

Lahdenmäki, T. and Leach, M. (2005) Optimizers Are Not Perfect, in Relational Database Index Design and the Optimizers: DB2, Oracle, SQL Server, et al., John Wiley & Sons, Inc., Hoboken, NJ, USA. doi: 10.1002/0471721379.ch14

Author Information

  1. Smlednik, Slovenia, Shrewsbury, England

Publication History

  1. Published Online: 27 JAN 2005
  2. Published Print: 24 JUN 2005

ISBN Information

Print ISBN: 9780471719991

Online ISBN: 9780471721376

SEARCH

CHAPTER TOOLS

Get PDF (177K)

Keywords:

  • optimizers;
  • SQL Server;
  • Oracle;
  • DB2 for z/OS;
  • optimizer problems;
  • matching problems;
  • screening problems;
  • non-BT predicates;
  • unnecessary sorts;
  • unnecessary table touches;
  • optimizers' cost estimates;
  • disastrous optimizers' estimates;
  • optimizer assistance;
  • range predicates;
  • skewed distribution;
  • correlated columns;
  • partial index keys;
  • optimizers' cost formulae;
  • I/O estimation;
  • CPU estimation;
  • buffer pools;
  • cache;
  • optimize every time;
  • access path hints;
  • redundant predicates;
  • falsifying statistics;
  • modifying indexes;
  • index design

Summary

Why optimizers do not always see the best alternative: matching and screening problems; non-BT predicates; unnecessary sorts; unnecessary table touches. Why 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 formulae considerations: I/O estimation; CPU estimation; the 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. The effect of optimizer problems on index design.

Get PDF (177K)