{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

Data Mgmt Lab_Part_51 - A Typical Query Optimizer 151(e...

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

View Full Document Right Arrow Icon
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) , floor: 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.floor=1 AND E.sal 59000 AND E.hobby = ‘yodeling’ 1. Identify a relational algebra tree (or a relational algebra expression if you prefer) that reflects 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.) Briefly 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.floor , Dept.did , and Finance.did . The system’s statistics indicate that employee salaries range from 10,000 to 60,000, employees enjoy 200 different hobbies, and the company owns two floors in the building. There are a total of 50,000 employees and 5,000 departments (each with
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
Image of page 2
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}