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

2. At reorganization, the P is rounded up. In our example, 21 index rows
almost fit in a leaf page when P 23%.

Reorganizing the index at this point is not necessary, but it is not a bad idea
either; the curve for X

=

7 in Figure 11.6 shows that performance is beginning
to deteriorate—the curve is becoming steeper at this point.
This example illustrates how dramatically the I/O time for a full index scan
may grow when index reorganization is long overdue, which may happen if
a table grows faster than expected and adequate exception monitoring is not
in place. The elapsed time of the batch program does not grow in the same
proportion because of the high CPU time (QUBE: 500 s) required by the 5
million FETCH calls. It is the SELECT calls that choose a few index rows out
of a thick index slice that suffer most from a high LPSR; the elapsed time of
such calls consists mostly of I/O time.

=

Table Disorganization (with a Clustering Index)

The POLICY table, with P 10% grew by 10%, just like its indexes. How does

that affect the elapsed time of the batch job?
Almost all the POLICY table pages become full after 500,000 inserts (growth
10%) because P being 10% means that the distributed free space is used up when
the table has grown by

=

P/(100 - P) = 11%

and because (real) table pages are never split; inserted table rows that do not fit in
the home page (where they should be inserted) are normally placed in a near-by
page. Table rows that are stored in leaf pages are discussed in the next section.
If the DBMS places the table rows close to the home page when the latter is full,
the performance of the table access does not at first deteriorate dramatically. This
is because the recently accessed pages will be in the buffer pool or disk cache.
However, when all the table pages close to the home page are full, the new row
will probably go at the end of the POLICY table. Each row that is placed a long
way from its home page will probably add a random I/O. If only one new table
row fits in the free space per page (10%), the table will be in a terrible state
after 500,000 inserts. The number of additional random touches will probably be
more than 100,000 (assuming more than 100,000 inserted table rows are a long
way from the home page); the elapsed time of the batch program will increase
by more than 15 min. The table should have more free space, at least 20%, to
survive growth of 10%.
If the DBMS does not support a clustering index, the POLICY table must
be reorganized frequently because new POLICY rows go to the end of the table
and each INSERT adds 10 ms (one random touch) to the elapsed time of the
batch program. After 10,000 new rows, the POLICY table will have grown
by only 0.2%, but the time for a table scan via the foreign key index will
have grown from 50 s (1 × 10 ms + 5 , 000 , 000 × 0 . 01 ms) to 150 s (10 , 000 ×