The table Faculty has 60,000 rows, each row occupies 100 bytes, and the database page size is 4 k bytes. Assuming pages in the index and data ?les are 100% occupied, estimate the number of page transfers required for the following SELECT statement in each of the cases listed below.
```
SELECT F.DeptId
FROM Facu lty F
WHERE F.Id = ’111111111’
```
a. The table has no index.
b. The table has a clustered B+ tree index on Id. Assume a (nonleaf) index entry has 20 characters.
c. The table has an unclustered B+ tree index on Id. Assume a (nonleaf) index entry has 20 characters.
d. The table has an unclustered B+ tree index on (Id, DeptId). Assume that an index entry now has 25 characters.
e. The table has an unclustered B+ tree index on (DeptId, Id). Assume that an index entry now has 25 characters.
a. Since each page of the table accommodates about 40 rows, the table occupies about 1500 pages and on average half of these will have to be transferred.
b. A non-leaf page in the index can accommodate 200 index entries, so a three-level index is required. Hence, 3 page transfers will be required.
c. A three-level index is still required. Hence, 4 page transfers are required (one additional transfer to get the row).
d. 160 index entries ?t in a page and a three level index is required. An index-only strategy is now possible involving 3 page transfers
e. 160 index entries ?t in a page and a three level index is required. An index-only strategy is now possible although a scan of the leaf level is now required. The leaf level contains about 60000/160 = 375 pages, and about half of these will have to be transferred.
You might also like to view...
The Slide Master can be displayed by pressing the ________ key on the keyboard and clicking the Normal view button on the Status bar
Fill in the blank(s) with correct word
In preventing routing loops with RIP, which of the following values is used to denote that a route has failed via route poisoning?
A) A value of 0 B) A value of infinity C) A value of 16 D) A value of 64
Press ____ to open the Save As dialog box.
a. CTRL+S b. SHIFT+S c. SHIFT+CTRL+S d. ALT+S
When you are prompted to enter a value when you run a query which includes a calculated field, you probably ________.
A. entered too many arguments in the formula B. used the wrong arithmetic operator in your formula C. forgot to name the new, calculated field D. typed a field name incorrectly