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

)ontablePOLICY
after 500,000 inserts. Assume the index rows have a fixed length of 150 bytes
and that 4086 bytes (4096

Let us take a closer look at the state of index (CNO, ...

− 10) are available for index rows per leaf page. With

P = 10%, 3677 bytes (0 . 9 × 4 , 086) can be used at reorganization, enough for

24 index rows (24 3600 bytes) but not for 25 index rows. As

27 index rows fit in 4086 bytes (27

× 150 bytes =

4050 bytes), there is space
for 3 newcomers per leaf page. The actual amount of distributed free space is
thus 3/27

× 150 bytes =

11%; we almost always get a little more free space than we request.
Now, the number of leaf pages needed to store 5,000,000 index rows is 5,000,000
rows/24 rows/page = 208 , 000. From this we can predict the effect of the inserts.
After 500,000 inserts, the average number of new index rows per leaf page is

=

500,000 rows / 208,000 pages = 2.4

Therefore, 80% (2.4/3) of the distributed free space has been consumed. Assum-
ing the inserts are random, 22% of the leaf pages will have been split because

BINOMDIST(3,500000,1/208000, TRUE) = 0.78

1 ms, etc.) are valid, the I/O
time for a full index scan will now be 23 times as long as immediately after
reorganization (208

If the normal assumptions (A = 10 ms, B = 0 .

, 000 × 0 . 1ms = 21 s):

(1 + (LPSR × A/B)) × ORIG = (1 + (0.22 × 100)) × 21 s
=23 × 21 s
= 8 min

The actual I/O time would be less than 8 min because the binomial distribu-
tion model becomes pessimistic after a large number of splits. In any case, the
high LPSR should have been revealed by a weekly exception report (more about
this later in this chapter) long before the index becomes so badly disorganized.
Let us now apply the guidelines, assuming that we expect 10% growth before
reorganization. Figure 11.7 proposes that P should be set to 23% when G =
10%. Now, no more than 20 index rows fit in 0 . 77 × 4086 bytes = 3146 bytes
(21 × 150 bytes = 3150 bytes), leaving space for 7 new rows. With X = 7and
F = 29% (2/7), Figure 11.6 predicts LPSR = 0 . 1%, so the I/O time for a full
index scan will be quite short after 10% index growth:

(1 + ( LPSR × A/B)) × ORIG = (1 + (0.001 × 100)) ×
25 s
= 1.1 × 25 s
=28s

This result may seem too good to be true. Weren't the recommendations
based on LPSR = 1%? Well, the starting point was LPSR < 1% and the actual

LPSR will often stay well below 1% for two reasons:

1. The basic recommendation was derived assuming that 5 new index rows
fit in the free space defined by P (X = 5). In our example X = 7.