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

SummaryofStepstoDeterminetheReorganizationFrequency
1. For short index rows, P = 10%; reorganize the index when it has
grown by 5% (G = 5%). Alternatively, use P = 20% and G = 12%.
To make the reorganization interval longer, choose P > 20%; then G =
50 × ( P /( 100 − P )) %.
2. For long index rows, refer to the later section.
3. For medium-sized index rows,
a. Choose P such that X = 5: P = 5 × index row length/leaf page size.
b. Using Figure 11.7, determine the value of G and hence the reorgani-
zation interval.
SummaryofStepsIftheReorganizationFrequencyIsSpecified
1. For short index rows, choose one of the following: (G = 5%, P = 10%),
(G = 12%, P = 20%), or (G = 33% and P = 40%). Use the formula P =
100 × G /( G + 50 ) when G > 33%.
2. For long index rows, refer to the later section.
3. For medium-sized index rows,
a. Assume LPSR of 1%, X = 5, F = 33%.
b. Given the value of G, determine the value of P using Figure 11.7.
3.LargePortionofNewIndexRowsGoestoSmallArea
(hotspots—nottotheendoftheindex)
Unfortunately, this hard to manage insert pattern is fairly common. In index
(BO, DATE), for instance (BO = branch office, DATE is ever increasing), the
new index rows go to the end of each BO slice. The reorganization interval must
be tailored index by index according to the number of leaf page splits and their
impact on index slice scan performance.
In index (BO, DATE), the page containing the last row of the largest BO is
the hottest page. It splits soon after reorganization. After the first split, the hottest
page is half full. For a while, the hottest page contains only rows of the largest
BO. The most recent rows for that BO can still be found quite quickly. The
situation gets worse when the last pages of many branch offices have been split.
In the end, each new index row per BO may go to a different leaf page. It may
then take 20 random index touches to retrieve the last 20 index rows per BO.
In a case such as this, a simple solution can provide significant relief. If old
rows (low values of DATE) are periodically deleted, the index should not be
reorganized after the deletes. It is convenient to have empty leaf pages close to
each hot spot; cutting the tails makes room for the growing heads.
Making an index, which has a high insert rate and hot spots, resident (pinned)
in memory is becoming more and more feasible as memory prices are falling and
64-bit addressing makes large buffer pools possible. In addition to the obvious
benefit of having no read I/Os, a resident index is not very sensitive to leaf page