lec19 - Query Optimization Chapter 15 EECS 484 Database...

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

View Full Document Right Arrow Icon
1 3/28/11 EECS 484: Database Management Systems, Kristen LeFevre 1 Query Optimization Chapter 15 3/28/11 EECS 484: Database Management Systems, Kristen LeFevre 2 Query Execution Life-Cycle Query Query Result Database Server Select R.text from Report R, Weather W where W.image.rain() and W.city = R.city and W.date = R.date and R.text. matches(“ insurance claims”) Query Syntax Tree Parser Query Plan Optimizer Segments Dispatch Query Plan |… |……| ………. |………. | |… |……| ………. |………. | |… |……| ………. |………. | |… |……| ………. |………. | |… |……| ………. |………. | |… |……| ………. |………. | |… |……| ………. |………. | |… |……| ………. |………. | |… |……| ………. |………. | |… |……| ………. |………. | |… |……| ………. |………. | Query Result Execute Operators
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 3/28/11 EECS 484: Database Management Systems, Kristen LeFevre 3 Query Optimization Query Optimizer – Given a SQL query, how do we choose an evaluation plan ? Enumerate alternative (equivalent) plans Choose a plan based on estimated cost 3/28/11 EECS 484: Database Management Systems, Kristen LeFevre 4 Query Evaluation Plan Annotated, extended RA tree consisting of operators Operator Interface: open(), getNext(), close() Intermediate Results (multiple ops): Pipelined : Tuples resulting from one operator fed directly into the next Materialized : Create a temporary table to store intermediate results
Background image of page 2
3 3/28/11 EECS 484: Database Management Systems, Kristen LeFevre 5 Example SELECT DISTINCT E.ename FROM Emp E, Dept D WHERE D.dname = ‘Toy’ AND D.did = E.did EMP (ssn, ename, addr, sal, did) DEPT (did, dname, floor, mgr) ename DEPT EMP dname=‘Toy’ (Clustered Index Scan) (File Scan) (Sort-Merge Join, Dept pre-sorted) (dup removal, input pre-sorted) (pipelined) Annotated RA Tree 3/28/11 EECS 484: Database Management Systems, Kristen LeFevre 6 Alternative Plans Many equivalent plans that produce the same result! (Different operator orders and algorithms) ename EMP DEPT dname=‘Toy’ ename DEPT EMP dname=‘Toy’ Which plan do you think is most efficient?
Background image of page 3

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

View Full DocumentRight Arrow Icon
4 3/28/11 EECS 484: Database Management Systems, Kristen LeFevre 7 Equivalent Plans What does it mean for two plans to be equivalent?
Background image of page 4
Image of page 5
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 12/08/2011 for the course EECS 484 taught by Professor Staff during the Winter '08 term at University of Michigan.

Page1 / 12

lec19 - Query Optimization Chapter 15 EECS 484 Database...

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

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