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

is likely to stay very low for more than a year when distributed free space is
specified, according to the basic recommendations (P at least 10% and large
enough for at least five new index rows per leaf page). Reorganization frequency
will depend solely on the net growth.
In general, it is important to place the volatile index column as far to the right
as possible. For WHERE STATUS = :STATUS AND ITEMNO = ITEMNO, the

index on the ORDER table should be (ITEMNO, STATUS ... ) and not (STA-

TUS, ITEMNO ). The moves caused by the volatile column STATUS will be

shorter with the first candidate index. LPSR may stay low even if the moves are
not random.
Volatile fixed-length index columns following the primary key, for example,
INV THIS M in index (CNAME, CNO, INV THIS M), do not create splits
because updating such columns does not cause the index row to be moved.

...

LONG INDEX ROWS

An index row may be considered long if making P large enough for five new
rows (X = 5) results in an excessive amount of distributed free space. When P
is not large enough for five rows,

G=33 × P / (100 - P)%

is no longer valid because LPSR > 1% when F = 33%. For instance, LPSR =
4 . 5% if only one row fits in the free space—see the curve for X = 1in
Figure 11.6.
Let us assume that only eight index rows fit in a leaf page. To allow for
five new rows per leaf page, P should be 63% (5/8). This would make an index
slice read quite slow even without splits. P

=

25% (six rows after reorganization,
room for two new rows) seems more reasonable. Let us see how the LPSR would
grow then.
Following the curve for X = 2 in Figure 11.6, we see that the LPSR reaches

1% when F = 22%. At that point, the average number of new index rows per

leaf page is 0 6). If

we are able to reorganize the index this often, the LPSR will stay below 1% with
P

. 22 × 2 = 0 . 44, so the index has grown by 7% (100 × 0 . 44 /

25%.
Let us assume that the index rows are even longer, perhaps so long that only
four rows fit in a leaf page. The reasonable alternatives are P

=

= 25% (X = 1) and

P 2). In the first case, to keep the LPSR below 1%, the index must

be reorganized when F

= 50% (X =

15% (Fig. 11.6), that is, when the average number of
new index rows per leaf page is 0

=

. 15 × 1 = 0 . 15; the index has grown by 5%

(100 50% because there are

three, instead of two, index rows per leaf page after reorganization; at this point,
the I/O time will be 0.03 ms per index row as opposed to 0.05 ms with P

× 0 . 15 / 3). This solution is better than one with P =

50%.
If the index cannot be reorganized this often, one of the following alternatives
must be chosen:

=