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

Objective: LPSR < 1%

Leaf page

Short rows Medium rows

P = 1 0

P%

X = 5

Reorg index
when F = 33%

Reorg index
when G = 5%

Figure 11.14 Free space and reorganization summary.

Included also are perhaps the two most important characteristics that influ-
ence these recommendations. It is crucial that the role of the LPSR is fully
understood and that very small values will begin to affect index scan performance;
our 1% objective will no doubt come as a surprise, even to many experienced
DBAs. That this limit will be breached when relatively small amounts of the
free space has been used will also cause some surprise; as will suggestions of
reorganization when perhaps only one third of the free space has been used.