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

slice I/O time quite long, even before any splits. This problem, together with its
solution, will be discussed later.

JustificationofourRecommendations

Short Index Rows Basic recommendation:
P = 10%
reorganize the index when it has grown by 5%.
To justify the recommendation, we will consider the borderline case: The
length of the index rows is 80 bytes and the leaf page size 4K.
If 4096

− 10 = 4086 bytes are available for index rows, 51 index rows fit in

a leaf page. With P = 10%, each leaf page has 45 index rows after reorganization.

Thus, X 6.

When the index has grown by 5%, there are 2.25 new index rows (5% of
45) per leaf page, on average. Thus F = 2 . 25/6 = 38%.
The curve for X = 6 (Fig. 11.6) gives LPSR = 1% when F = 38%.
This is the worst case ; when index rows are shorter than 2% of the leaf page
size, the LPSR will stay lower.
If a longer reorganization interval is required, P should be set to 20% and
the index reorganized when it has grown by 12%. The justification for this is
as follows:
With P = 20%, each leaf page has 40 index rows after reorganization. Thus,
X = 11. When the index has grown by 12%, there are 4.8 new index rows (12%
of 40) per leaf page, on average. Thus F = 4 . 8/11 = 47%. The curve for X = 11
(Fig. 11.6) would give LPSR = 1% when F = 47%.
For even longer reorganization intervals, assume LPSR = 1% when F =
50%. Then

=

G = 50 × ( P /( 100 − P ))

Medium Index Rows Basic recommendation: Choose P to make X = 5, reor-
ganize when G = 33 × ( P /( 100 − P )) .
We will again consider the borderline case: The length of index rows is 200
bytes and the leaf page size 4K.
Pmustbe(5 × 200 bytes)/4086 bytes = 25% to make X = 5. At reorgani-
zation, 15 index rows will be placed in each leaf page.
When the index has grown by 11% (G = 33 × 25/75), there are 1.65 new
index rows (11% of 15) per leaf page on average. Thus F = 1 . 65 / 5 = 33%.
The curve for X = 5 (Fig. 11.6) gives LPSR = 0 . 7% when F = 33%.
This is again the worst case ; when index rows are shorter then 5% of the
leaf page size, LPSR will stay lower.

Long Index Rows Choose reasonable values for X and P (try to make X at
least 2); then find F and G as usual. This will be considered in more detail in
the section on Long Index Rows.