This preview has intentionally blurred sections. Sign up to view the full version.View Full Document
Unformatted text preview: Physical Database Design and Tuning 205 2. Redesign the physical schema assuming that the set of important queries is changed to be the following: List the id and address of employees with a user-specified employee name. List the overall maximum salary for employees. List the average salary for employees by department; that is, for each deptid value, list the deptid value and the average salary of employees in that de- partment. List the sum of the budgets of all departments by ﬂoor; that is, for each ﬂoor, list the ﬂoor and the sum. Assume that this workload is to be tuned with an automatic index tuning wizard. Outline the main steps in the execution of the index tuning algorithm and the set of candidate configurations that would be considered. Answer 20.1 The answer to each question is given below. 1. If we create a dense unclustered B+ tree index on h age, sal i of the Emp relation we will be able to do an index-only scan to answer the 5th query. A hash index would not serve our purpose here, since the data entries will not be ordered by age ! If index only scans are not allowed create a clustered B+ tree index on just the age field of Emp. We should create an unclustered B+Tree index on deptid of the Emp relation and another unclustered index on h dname, did i in the Dept relation. Then, we can do an index only search on Dept and then get the Emp records with the proper deptid ’s for the second query. We should create an unclustered index on ename of the Emp relation for the third query. We want a clustered sparse B+ tree index on floor of the Dept index so we can get the department on each ﬂoor in floor order for the sixth query....
View Full Document
- Fall '12
- Relational model, emp, b+ tree index