lec17 - Evaluation of Relational Operators Chapters 12 and...

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

View Full Document Right Arrow Icon
1 3/22/11 EECS 484: Database Management Systems, Kristen LeFevre 1 Evaluation of Relational Operators Chapters 12 and 14 3/22/11 EECS 484: Database Management Systems, Kristen LeFevre 2 Query Execution Overview 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/22/11 EECS 484: Database Management Systems, Kristen LeFevre 3 Query Evaluation Plans Extended relational algebra tree, including algorithms for each operator ename EMP DEPT dname=‘Toy’ SELECT 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) Query Optimizer selects the evaluation plan 3/22/11 EECS 484: Database Management Systems, Kristen LeFevre 4 Operator Evaluation How to implement common operators? Selection Projection (optional DISTINCT) Join Set Difference Union Aggregate operators (SUM, MIN, MAX, AVG) GROUP BY Next week – How to choose a plan?
Background image of page 2
3 3/22/11 EECS 484: Database Management Systems, Kristen LeFevre 5 Selection An access path is a method of retrieving tuples from a relation and (optionally) applying some selection condition Example of a selection condition a predicate: gpa > 3.0 and age = 21 Examples of access paths File scan Index that matches a selection in the query. Examples: B+tree index on the <gpa, age> attributes B+tree index on gpa B+tree index on age Hash index on age 3/22/11 EECS 484: Database Management Systems, Kristen LeFevre 6 Index Matching When can we use an index to evaluate a selection predicate ? Convert to Conjunctive Normal Form (CNF) (p1 or p3) and (p2 or p3) An index matches a predicate if index can be used to evaluate the predicate An index can match a subset of conjuncts Primary conjuncts
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/22/11 EECS 484: Database Management Systems, Kristen LeFevre 7 Index Matching Search key <a, b, c> a=5 and b= 3? a > 5 and b < 3 b=3 a=7 and b=5 and c=4 and d>4 a=7 and c=5 Hash Idx • no! • no! • no! • yes
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 / 15

lec17 - Evaluation of Relational Operators Chapters 12 and...

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