The table Faculty has 60,000 rows, each row occupies 100 bytes, and the database page size is 4 k bytes. The table contains an attribute City, indicating the city in which a professor lives, there are 50 cities with names city10 ...city50, and professors are randomly distributed over the cities. Assuming that pages in the index and data ?les are 100% occupied, estimate the number of page

Question

transfers required for the following SELECT statement in each of the cases listed below.

```
SELECT F.Id
FROM Facu lty F
WHERE F.City > ’city10’ AND F.City < ’city21’
```

a. The table has no index.
b. The table has a clustered B+ tree index on City. Assume a (nonleaf) index entry has 25 characters.
c. The table has an unclustered B+ tree index on City. Assume an index entry has 25 characters.
d. The table has an unclustered B+ tree index on (City, Id). Assume an index entry has 40 characters.
e. The table has an unclustered B+ tree index on (Id, City). Assume an index entry has 40 characters.

Answer 1

a. Since each page of the table accommodates about 40 rows, the table occupies about 1500 pages and all will have to be transferred.
b. A three level index will be required. Instead of transferring the entire table, only pages containing cities in the range will have to be transferred: about 1/5 of the data pages, or 300 pages (ignoring the I/O required to search the index).
c. Since rows in the range are scattered throughout the data ?le we can expect the virtually every page will contain a row to be retrieved. Hence, it is unlikely that the query plan will use the index and a ?le scan involving 1500 page transfers is likely.
d. An index-only query plan can be used. Since 100 index entries ?t in a page, the leaf level will have 600 pages and 120 will be transferred (ignoring the I/O required to search the index).
e. Now the entire leaf level of the index will have to be scanned, costing 600 page transfers.