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

Short Medium Long

<2% 2...5% >5%

4K: < 80 bytes
8K: < 160 bytes

80...200 bytes
160...400 bytes

>200 bytes
>400 bytes

Figure 11.3 Three different free-space treatments.

Short-CutforShortIndexRows
When the index rows are short, refer to Figure 11.3, choosing P for an index
with random inserts is simple:
Basic recommendation: P = 10%; reorganize the index when it has grown
by 5%. Thus, P = twice the predicted growth.
With short index rows, this rule keeps the number of leaf page splits to a
reasonably low level; LPSR will be less than 1%. The discussion that follows
justifies the factor of 2. We will also see why leaf page splits will be more likely
and P more difficult to choose, when the index rows are longer.
If a longer reorganization interval is required, P should be set to 20% and
the index reorganized when it has grown by 12%. We will discuss at a later stage
what would be required if the interval needed to be extended even further.

DifficultChoiceforLongIndexRows
When the index rows are long, refer to Figure 11.3, choosing P for an index
with random inserts can be quite tricky. This will be addressed later, after the
following discussion on medium-sized index rows has been understood.

ChoicesforMedium-SizedIndexRows
The number of leaf page splits can be predicted when two values, P and X, are
known about the state of an index after reorganization:
P% = amount of free space requested in CREATE INDEX
To request 25% free space, a value of 25 would be used for PCTFREE in
Oracle and DB2, and a value of 75 for FILLFACTOR in SQL Server. During
reorganization (rebuild) and load, index rows are added to a leaf page until the
next row would not totally fit above the P% line shown in Figure 11.4.