Consider the table Professor with attributes Id, Name, Dept, Salary, and Rank and primary key Id, and the statements:
where ssn is a host variable and sal is a literal.
(a) Give an ecient query plan for S1 and state what index (search key, clustered, nonclustered,
B+ tree, hash) is used.
(b) Now suppose, in addition to optimizing S1, we are equally concerned with optimizing the
execution of S2. Assuming sal is a large number (most rows do not satisfy the WHERE
clause) describe the query plan you would like the optimizer to use and any changes that
might need to be made to your previous answer. State what index (search key, clustered,
nonclustered, B+ tree, hash) is used for each statement. Explain your reasoning.
(c) Redo the previous part assuming that sal is a small number (most rows satisfy the
WHERE clause). Explain your reasoning.
(a) Give an ecient query plan for S1 and state what index (search key, clustered, nonclustered,
B+ tree, hash) is used.
Solution:
clustered or unclustered, hash or B+ tree on Id
use index to locate unique row satisfying Id = :ssn
(b) Now suppose, in addition to optimizing S1, we are equally concerned with optimizing the
execution of S2. Assuming sal is a large number (most rows do not satisfy the WHERE
clause) describe the query plan you would like the optimizer to use and any changes that
might need to be made to your previous answer. State what index (search key, clustered,
nonclustered, B+ tree, hash) is used for each statement. Explain your reasoning.
Solution:
Use an unclustered B+ tree index on Salary to get the rows satisfying the WHERE clause
(only a few rows satisfy). Sort them by Dept and count size of each group. The answer
to the previous part is unchanged.
or
Use a clustered B+ tree on Salary. Scan leaf level starting from sal to get satisfying
rows, sort on Dept, count size of each group. The answer to the previous part must be
unclustered index on Id.
(c) Redo the previous part assuming that sal is a small number (most rows satisfy the
WHERE clause). Explain your reasoning.
Solution:
Use clustered B+ tree index on Dept. Scan entire leaf level, groups will be contiguous
and number of members with appropriate salary of each group can be counted. Since
most rows satisfy, most of the pages will have to be read anyway and a large sort will
be necessary if rows are not already sorted on Dept. By clustering on Dept the sort is
avoided and fast scanning techniques can be employed. The answer to the rst part must
be an unclustered index on Id.
You might also like to view...
What is a transistor, and how did the introduction of transistors lead to faster and cheaper computers? What other effects did transistors have on modern technology and society?
What will be an ideal response?
When parentheses in an arithmetic expression are nested, the _____ set of parentheses is evaluated first.
Fill in the blank(s) with the appropriate word(s).
Ready-made illustrations that you can insert in documents are called ____________________.
Fill in the blank(s) with the appropriate word(s).
Hotspots can be ____ shapes.
A. rectangular B. polygonal C. circular D. All of the above.