1. Query Optimization

2. Very Big Picture • A query execution plan is a program. • There are many of them. • The optimizer is trying to chose a good one. • Hence, the optimizer is reasoning about programs. • Key: cost model, search space. • Compilers don’t have cost models. Why?

3. More Detail, but Still Big Picture • Parse the SQL query into a logical tree: • identify distinct blocks (corresponding to nested sub-queries or views). • Query rewrite phase: SQL-to-SQL transforms • apply algebraic transformations to yield a cheaper plan. • Merge blocks and move predicates between blocks. • Optimize each block: join ordering. • Complete the optimization: select scheduling (pipelining strategy).

4. Query Rewrite Summary • The optimizer can use any semantically correct rule to transform one query to another. • Rules try to: • move constraints between blocks (because each will be optimized separately) • Unnest blocks • Especially important in decision support applications where queries are very complex. • Rules can be very tricky!

5. The Course in Perspective • The relational data model, SQL • Views, updates, transactions • Conceptual design • Expressing the constraints on the domain • Using them to get good schema designs • XML • A format for data exchange. Has its own query language. • Basis for web services.

6. Perspective (continued) • Building a database system: • Storage and indexing • Query execution (join algorithms) • Query optimization • Data integration and sharing: • Databases were created independently • Schemas need to be matched • Need to access multiple databases in one query.