23-qo

# 23-qo - Announcements (Thu. Dec. 1) Query Optimization CPS...

This preview shows pages 1–3. Sign up to view the full content.

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 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

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

View Full Document
2 7 Heuristics-based query optimization ± Start with a logical plan ± Push selections/projections down as much as possible ² Why? Reduce the size of intermediate results ² Why not? May be expensive; maybe joins filter better ± Join smaller relations first, and avoid cross product ² Why? Reduce the size of intermediate results ² Why not? Size depends on join selectivity too ± 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
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 / 5

23-qo - Announcements (Thu. Dec. 1) Query Optimization CPS...

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

View Full Document
Ask a homework question - tutors are online