This preview shows pages 1–2. Sign up to view the full content.
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 reects 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.) Briey 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
- Fall '12