Additional Estimation Considerations - Relational Database Index Design and the Optimizers - page 267

Numberof IndexPages
After an index reorganization, there are about 45 index rows in a leaf page.
Thus, the number of leaf pages is 10 , 000 , 000 / 45 = 220 , 000. Level two con-
sists of 220 , 000 / 45 = 4900 pages, level three 4900 / 45 = 110 pages, level four
110 / 45 = 3 pages. There is only one page in level five, the root page.
Thus, there are about 5000 nonleaf pages, and their total space requirement
is about 20 MB. The total number of pages in the index is about 225,000. Thus,
the disk space requirement is 1 GB.
NoteAboutKeyTruncation
If only that part of the key needed to choose the correct page at the next lower
level is stored in the nonleaf pages, and if the keys are truncation-friendly, the
number of nonleaf pages would be significantly lower. In our example, LNO is
the primary key. As the five first index columns make an index key unique, the
index columns following LNO would not be stored in the nonleaf pages. Because
the longest column in the index, CNAME, follows LNO, the number of nonleaf
pages could be as low as 2000 instead of 5000. Unfortunately, not all index keys
truncate as well as this.
The disk space requirement is probably not an issue (according to our rule
of thumb, the cost is only about U.S.$50 per month), but will the nonleaf pages
fit in memory, as we have assumed? In this index, the number of nonleaf pages
is 2% of the number of leaf pages, (5000/220,000). Let us assume this is a
typical index.
The next question is the total size of all the operational indexes. In a medium-
size insurance company, a few million customers, the total size of the operational
tables may be 500 GB. The total size of the indexes for these tables may also
be 500 GB. Using the same 2% coefficient derived above, the space required for
all the nonleaf pages will then be 10 GB. This is a little too much for current
database servers , which typically have 16 to 64 GB of memory. Furthermore,
virtual storage considerations set a limit on the size of database buffers if the
system does not yet support 64-bit addressing.
Impact of the Disk Server Read Cache
The rapidly growing disk server read cache provides a solution. At the time of
writing, a disk server may typically have 64 GB of read cache, and this amount
of semiconductor memory does not carry an inhibitive price tag. The essential
factor is the transfer time from read cache to memory; with current disk servers
and fiber channels, this will be less than 1 ms per 4-kb page. Although this time
is much longer than the retrieval time from a pool in memory, a disk server read
cache that is significantly larger than the database buffer pool helps to keep the
average access time to nonleaf pages low.
If the size of the database buffer pools and read cache are only 10% of the
values presented in Figure 15.2, while the operational database size remains one