Consider the table Authors with attributes Name, Publ, Title,andYearPub. Assume that Name is the primary key (authors’ names are unique) and hence one would expect that the DBMS would automatically create a clustered index on that attribute. Consider the statement
```
SELECT A.Publ, COUNT(*)
FROM Authors A
WHERE ...range pred icate on YearPub...
GROUP BY A.Publ
```
a. Assume that the statement is generally executed with a very narrow range speci?ed in the WHERE clause (the publication year of only a few books will fall within the range). What indices would you create for the table and what query plan would you hope the query optimizer would use (include any changes you might make to the index on Name).
b. Repeat (a) assuming that a very broad range is generally speci?ed.
a. You might choose an unclustered B+ tree index on YearPub in which case the query plan would search the index using the small end of the range and then scan forward along the leaf level to locate the (few) rows satisfying the WHERE clause. Unfortunately, these rows would not be grouped together in the table, so they would have to be randomly retrieved. The plan would then sort them by Publ and count the size of each group. Alternatively, you might change the index on Name from clustered to unclustered and create a clustered B+ tree index YearPub. In this case the qualifying rows would be contiguous in the table.
b. You might change the index on Name from clustered to unclustered and create a clustered B+ tree index Publ. This would be useful if it is expected that a signi?cant fraction of the rows in the table ?t within the range and hence most of the pages will be read. In that case the query plan will scan the entire table (instead of trying to retrieve qualifying rows through an index on YearPub). Groups are stored contiguously in the table and the scan can simply count the qualifying rows one group at a time as the scan progresses without having to do an expensive sort.
You might also like to view...
Which of the following is true about backing up and recovering the VMM environment?
A. you use the VMM administrator console to backup the SQL database B. you use the Failover Cluster Manager to backup the library server C. the VMM server is backed up using the VMM administrator console D. you cannot perform a full server backup to a network folder
What are the possible orders of message delivery to each process if the messages are causally related as m11 -> m21 -> m12 -> m22 and the multicast is (i) FIFO, (ii) causal, and (iii) atomic?
Suppose a multicast group currently is participated by two processes: P1 and P2. Suppose P1 multicasts m11 then m12, P2 multicasts m21 then m22.
FIGURE OL 3-1
Referring to Figure OL 3-1 above, number 2 is pointing to the ____.
A. To-Do bar B. Task List C. Tasks folder button D. Meetings, appointments, and events
Case 5-2Jeremy is in charge of his hockey team's Web site. He is creating a table to keep track of the scores.Jeremy wants the border color to be black and the line around the table to be solid with a width of 5. The correct code would be: ____.
A. .table1 {border-color:black;border-type:solid;borderwidth:5} B. .table1 {color:black;border-type:solid;borderwidth:5} C. .table1 {border-color:black;border-style:solid;border-width:5} D. .table1 {color:black;borderstyle:solid;border-width:5}