This chapter describes the fundamentals of query rewrite and optimization. Join processing is described in Chapter 3: “Join Planning and Optimization.”
The information provided is designed to help you to interpret EXPLAIN reports more accurately and to explain and emphasize the importance of maintaining accurate statistical and demographic profiles of your data, not to describe query optimization as an abstraction.
A statistic is a single computed value that represents an entire sample or population of data. For example, the mean of a distribution is a measure of its central tendency calculated by summing the data and then dividing that sum by the number of items summed. The actual data might not have a value identical to its mean, but the statistic provides a very valuable means for describing its properties.
Demographics are raw, uncomputed characteristics of an entire set of data. For example, the highest and lowest values that occupy an interval are demographic measures, not statistics. Similarly, the cardinality of a table is the actual count of its rows, so it is demographic, not statistical, information.
Topics described include why relational systems need query optimizers, what query optimization is, and an overview of how query optimization is done. Special attention is paid to statistics and cost optimization.
This chapter does not describe the Teradata Index Wizard, which is also a component of the Optimizer.
Grad (2009) is a special issue of IEEE Annals of the History of Computing that presents a history of database management systems that were developed prior to the advent of relational systems, and includes some basic information about query optimization.