1 / 6

Query Optimization

Query Optimization. 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?.

edwardfreeman
Download Presentation

Query Optimization

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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.

More Related