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

All indexes
Indexes with high LPSR
Indexes with high TR
because of high LPSR
Figure 11.13 Candidates for
index reorganization.
ž If the index is always used to find a single index row, a high LPSR does
not matter.
ž If the scanned index slices are always thin, say less than 1000 rows, a high
LPSR may not matter too much.
If index reorganization is disruptive, we may have to consider an index to be
innocent until it is proven guilty. Split-sensitive indexes can be found with the
help of an exception report that shows the profile of slow transactions or SQL
calls. If a SELECT takes a long time because of a large number of random index
page reads, the reason is probably one of the following:
1. Non-BJQ join with nested loop
2. High LPSR and index slice scan
Thus, we may either reorganize all indexes that may benefit the larger subset
in Figure 11.13 or only those that are known to benefit the smaller subset. The
first approach may be reasonable if the reorganizations do not cause significant
contention (lock waits, CPU queuing, disk queuing) and with a company-owned
computer so that there are no external charges for CPU time.
The weekly split report should include, if the information is available, the
number of inserts since the last reorganization; again DB2 for z/OS V7 RTS:
REORGINSERTS, is a fine example; this helps to improve the reorganization
schedule. If the LPSR growth is high compared to the number of inserts, higher
than that predicted by the binomial distribution, the index may have hot spots.
Tailored solutions, such as pinning the index in memory, may then be necessary.
SUMMARY
Index reorganization management is a controversial topic. Donald K. Burleson (9)
concludes a recent article on the topic with the following thoughts:
TheDebateContinues
Today, a battle is raging between the “academics” who do not believe that
indexes should be re-built without expensive studies and the “pragmatists” who