Table and Index Organization - Relational Database Index Design and the Optimizers - page 22

leaf page. If the average length of the rows in a table is more than one third of
the page size, space utilization suffers. Only one row with 2100 bytes fits in a
4K page, for instance. The problem of unusable space is more pronounced with
indexes. As new index rows must be placed in a leaf page according to the index
key value, the leaf pages of many indexes should have free space for a few index
rows, after load and reorganization. Therefore, index rows that are longer than
20% of the leaf page may result in poor space utilization and frequent leaf page
splits. We have much more to say about this in Chapter 11.
With current disks, one rotation takes 4 ms (15,000 rpm) or 6 ms (10,000
rpm). As the capacity of a track is normally greater than 100 kilobytes (kb), the
time for a random read is roughly the same for 2K, 4K, and 8K pages. It is
essential, however, that the stripe size on RAID disks is large enough for one
page; otherwise, more than one disk drive may have to be accessed to read a
single page.
In most environments today, sequential processing brings several pages into
the buffer pool with one I/O operation—several pages may be transferred with
one rotation, for instance. The page size does not then make a big difference in
the performance of sequential reads.
SQL Server 2000 uses a single page size for both tables and indexes: 8K.
The maximum length of an index row is 900 bytes.
Oracle uses the term block instead of page . The allowed values for
BLOCK SIZE are 2K, 4K, 8K, 16K, 32K, and 64K, but some operating
systems may limit this choice. The maximum length of an index row is 40%
of BLOCK SIZE. In the interests of simplicity, we trust Oracle readers will
forgiveusifweusetheterm page throughout this topic.
DB2 for z/OS supports 4K, 8K, 16K, and 32K pages for tables but only 4K
pages for indexes. The maximum length for index rows is 255 bytes in V7, but
this becomes 2000 bytes in V8.
DB2 for LUW allows page sizes of 4K, 8K, 16K, and 32K for both tables
and indexes. The upper limit for the index row length is 1024 bytes.
Table Clustering
Normally a table page contains rows from a single table only. Oracle provides
an option to interleave rows from several related tables; this is similar to storing
a hierarchical IMS database record with several segment types. An insurance
policy, for instance, may have rows in five tables. The policy number would be
the primary key in one table and a foreign key in the other four tables. When
all the rows relating to a policy are interleaved in one table, they might all fit in
one page; the number of table I/Os required to read all the data for one policy
will then be only one, whereas it would otherwise have been five. On the other
hand, as older readers may remember, interleaving rows from many tables may
create problems in other areas.