Indexes and Reorganization - Relational Database Index Design and the Optimizers - page 217

1. Set P 50% and reorganize when the index has grown by 22%. At this

point, 22% of the free space has been used and the average number of
new index rows per leaf page is 0

=

. 22 × 2 = 0 . 44; the index has grown

by 100 × 0 . 44 / 2, LPSR = 1%.
2. Leave every th leaf page empty at reorganization if that option is avail-

able (as with FREEPAGE in DB2 for z/OS). Now, most leaf page splits
are not very harmful; when the other half of a leaf page moves only a
few pages away, the split probably does not cause a random I/O. The
disk system at least, if not the DBMS, normally reads ahead, so a nearby
page is often found in a pool or cache. If P = 25% (enough for one new
index row) and every fourth page is left totally empty, there is room for
56 new rows [ ( 32 × 1 ) + ( 8 × 3 ) ] per 32-page group. The probability of
a long-distance split is then quite low even when F = 50%. The reorgani-
zation interval required to avoid the index slice read time from doubling
is difficult to predict, but it may be more than twice that of the first alter-
native (in which the amount of distributed free space was slightly higher:
50% vs. 56 / 128 = 44%).
The second alternative seems better in this case. In general, however, when
the index rows are not this long, it is probably better to avoid leaf page splits
by using a high P value, rather than making the leaf page splits less harmful by
using empty pages. The empty leaf pages make sequential read slower, just like
the empty space per leaf page.
The two examples may seem unrealistic; are there indexes with rows so long
that only eight or four rows fit in a leaf page? Such indexes are rare today, but as
hardware evolves and the DBMS limits are increased, they will probably become
more common. It is sensible to have some idea of when an index row may be
too long from the leaf page split point of view. Also, some products provide the
option of storing table rows in leaf pages. Table rows are often long—more than
500 bytes is quite normal.
The benefits derived from fat and semifat indexes rely on the high speed of
index slice scans with current hardware, 0.01 ms per TS, according to the QUBE.
This benefit is reduced when the index rows are very long. If there are only two
rows per 4K leaf page, the I/O time per row may be 0

N

05 ms. More
seriously, the number of random index touches begins to increase steeply with
inserts when X

. 1ms / 2 = 0 .

< 5.

EXAMPLE: ORDER-SENSITIVE BATCH JOB

The 12 million touches in the batch program joining tables CUST and POLICY,
shown in Figure 11.9 and SQL 11.4, take only 10 min according to the QUBE.
At least, that is the figure if the assumptions on which the QUBE is based
apply—in this case that the indexes and tables are in perfect order, as they
would be following a reorganization. (Note: Because of WE WANT 500 ROWS
PLEASE, the optimizer will choose nested loop; this is essential to avoid a sort.