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

Question

```
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.

Answer 1

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.