Summary
Query optimization is an important task in a relational DBMS.
Typically optimize 1 “select…” (query block) at a time
Must understand optimization in order to understand the performance impact of a given database design (relations, indexes) on a workload (set of queries).
Two parts to optimizing a query:
- Consider a set of alternative plans.
- Must prune search space; typically, left-deep plans only.
- Must estimate cost of each plan that is considered.
- Must estimate size of result and cost for each plan node.
- Key issues: Statistics, indexes, operator implementations.