This preview has intentionally blurred sections. Sign up to view the full version.View Full Document
Unformatted text preview: A Typical Query Optimizer 151 (e) Equivalent maximally pushed form: σ c 1 ( π l 1 ( π l 2 ( σ c 2 ( R )) × S )). (f) Equivalent maximally pushed form: π l ( σ c 1 ( π l 1 ( π l 2 ( σ c 2 ( R )) × S ))). Answer 15.6 Answer omitted. Exercise 15.7 Consider the following relational schema and SQL query. The schema captures information about employees, departments, and company finances (organized on a per department basis). Emp( eid: integer , did: integer , sal: integer , hobby: char(20) ) Dept( did: integer , dname: char(20) , ﬂoor: integer , phone: char(10) ) Finance( did: integer , budget: real , sales: real , expenses: real ) Consider the following query: SELECT D.dname, F.budget FROM Emp E, Dept D, Finance F WHERE E.did=D.did AND D.did=F.did AND D.ﬂoor=1 AND E.sal ≥ 59000 AND E.hobby = ‘yodeling’ 1. Identify a relational algebra tree (or a relational algebra expression if you prefer) that reﬂects the order of operations a decent query optimizer would choose. 2. List the join orders (i.e., orders in which pairs of relations can be joined to compute the query result) that a relational query optimizer will consider. (Assume that the optimizer follows the heuristic of never considering plans that require the computation of cross-products.) Brieﬂy explain how you arrived at your list. 3. Suppose that the following additional information is available: Unclustered B+ tree indexes exist on Emp.did , Emp.sal , Dept.ﬂoor , Dept.did , and Finance.did ....
View Full Document
This note was uploaded on 01/17/2012 for the course EGN 4302 taught by Professor Dr.vishak during the Fall '12 term at University of Central Florida.
- Fall '12