Data Mgmt Lab_Part_52 - 154 Chapter 15 3 Consider the...

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

View Full Document Right Arrow Icon
154 Chapter 15 3. Consider the following query: SELECT * FROM Emp E, Dept D WHERE E.did=D.did (a) Suppose that there is a clustered hash index on did on Emp. List all the plans that are considered and identify the plan with the lowest estimated cost. (b) Assume that both relations are sorted on the join column. List all the plans that are considered and show the plan with the lowest estimated cost. (c) Suppose that there is a clustered B+ tree index on did on Emp and Dept is sorted on did . List all the plans that are considered and identify the plan with the lowest estimated cost. 4. Consider the following query: SELECT D.did, COUNT (*) FROM Dept D, Proj P WHERE D.projid=P.projid GROUP BY D.did (a) Suppose that no indexes are available. Show the plan with the lowest esti- mated cost. (b) If there is a hash index on P.projid what is the plan with lowest estimated cost? (c) If there is a hash index on D.projid what is the plan with lowest estimated cost? (d) If there is a hash index on D.projid and P.projid what is the plan with lowest estimated cost? (e) Suppose that there is a clustered B+ tree index on D.did and a hash index on P.projid . Show the plan with the lowest estimated cost. (f) Suppose that there is a clustered B+ tree index on D.did , a hash index on D.projid , and a hash index on P.projid . Show the plan with the lowest estimated cost. (g) Suppose that there is a clustered B+ tree index on h D.did, D.projid i and a hash index on P.projid . Show the plan with the lowest estimated cost. (h) Suppose that there is a clustered B+ tree index on
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 2
This is the end of the preview. Sign up to access the rest of the 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.

Page1 / 3

Data Mgmt Lab_Part_52 - 154 Chapter 15 3 Consider the...

This preview shows document pages 1 - 2. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online