23-qo-notes

23-qo-notes - Query Optimization CPS 116 Introduction to...

Info iconThis preview shows pages 1–4. Sign up to view the full content.

View Full Document Right Arrow Icon
1 Query Optimization CPS 116 Introduction to Database Systems Announcements (Thu. Dec. 1) ± Extra credit (20 points) due next Tuesday ± Homework #4 deadline extended—due next Thursday (Dec. 8) ± Sign up (via email) for a 30-minute slot in the project demo period, Dec. 12-14 ² Two “public” demo slots available right after final exam ± Final exam 2-4pm Dec. 13 ² Open book, open notes ² Focus on the second half of the course ² Sample final available 2 3 Query optimization ± One logical plan ! “best” physical plan ± Questions ² How to enumerate possible plans ² How to estimate costs ² How to pick the “best” one ± Often the goal is not getting the optimum plan, but instead avoiding the horrible ones 1 second 1 hour 1 minute Any of these will do
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
2 4 Plan enumeration in relational algebra ± Apply relational algebra equivalences ) Join reordering: £ and ² are associative and commutative (except column ordering, but that is unimportant) ² ² R S T ² ² S R T ² ² R T S === 5 More relational algebra equivalences ± Convert ¾ p - £ to/from ² p : ¾ p ( R £ S ) = R ² p S ± Merge/split ¾ ’s: ¾ p 1 ( ¾ p 2 R ) = ¾ p 1 A p 2 R ± Merge/split ¼ ’s: ¼ L 1 ( ¼ L 2 R ) = ¼ L 1 R , where L 1 μ L 2 ± Push down/pull up ¾ : ¾ p A pr A ps ( R ² p’ S ) = ( ¾ pr R ) ² p A p’ ( ¾ ps S ), where ³ pr is a predicate involving only R columns ³ ps is a predicate involving only S columns ³ p and p’ are predicates involving both R and S columns ± Push down ¼ : ¼ L ( ¾ p R ) = ¼ L ( ¾ p ( ¼ L L’ R )), where ³ L’ is the set of columns referenced by p that are not in L ± Many more (seemingly trivial) equivalences… ³ Can be systematically used to transform a plan to new ones 6 Relational query rewrite example ¼ title ¾ Student . name =“Bart” A Student . SID = Enroll . SID A Enroll . CID = Course.CID £ Enroll Course £ Student ¼ title ¾ Enroll . CID = Course.CID £ Enroll Course £ Student ¾ Student . SID = Enroll.SID ¾ Student . name = “Bart” Push down ¾ ¼ title ² Enroll . CID = Course . CID Enroll Course Student ² Student . SID = Enroll . SID ¾ name = “Bart” Convert ¾ p - £ to ² p
Background image of page 2
3 7 Heuristics-based query optimization ± Start with a logical plan ± Push selections/projections down as much as possible ² Why? ² Why not? ± Join smaller relations first, and avoid cross product ² Why? ² Why not? ± Convert the transformed logical plan to a physical plan (by choosing appropriate physical operators) 8 SQL query rewrite ± More complicated—subqueries and views divide a query into nested “blocks” ² Processing each block separately forces particular join methods and join order ² Even if the plan is optimal for each block, it may not be optimal for the entire query ±
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 4
This is the end of the preview. Sign up to access the rest of the document.

This document was uploaded on 01/17/2012.

Page1 / 10

23-qo-notes - Query Optimization CPS 116 Introduction to...

This preview shows document pages 1 - 4. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online