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

Info icon This 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
Image of page 1

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

View Full Document Right 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
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?
Image of page 3

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

View Full Document Right 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? Consider a query that accesses one relation (R) Two plans P1 and P2 are equivalent if they produce the same result for every possible instance of R In general, two plans are equivalent if they produce the same result for every possible database instance How do we know if two plans are equivalent?
Image of page 4
Image of page 5
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern