Data Mgmt Lab_Part_69 - Physical Database Design and Tuning...

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

View Full Document Right Arrow Icon

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

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

Page1 / 3

Data Mgmt Lab_Part_69 - Physical Database Design and Tuning...

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