{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

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 Document Right 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 Document Right 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 • no! Tree Idx • yes • yes • no! • yes • yes Index matches (part of) a predicate if Conjunction of terms involving only attributes (no disjunctions) Hash: only equality operation, predicate has all index attributes.
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.

{[ snackBarMessage ]}