Database Reference
In-Depth Information
rebuild indexes on a schedule because their end-users report faster
response times.
To date, none of the world's Oracle experts has determined a reliable rule
for index rebuilding, and no expert has proven that index re-builds “rarely”
help. Getting statistically valid “proof” from a volatile production system
would be a phenomenal challenge. In a large production system, it would be a
massive effort to trace LIO from specific queries to specific index, before and
after the rebuild.
ž Academic approach —Many Oracle experts claim that indexes rarely
benefit from rebuilding, yet none have ever proffered empirical evidence
that this is the case, or what logical I/O conditions arise in those “rare”
cases where indexes benefit from re-building.
ž Pragmatic approach —Many IT managers force their Oracle DBAs to
periodically re-build indexes because the end-user community reports
faster response times following the re-build. The pragmatists are not
interested in “proving” anything, they are just happy that the end-users
are happy. Even if index re-building were to be proven as a useless
activity, the Placebo effect on the end-users is enough to justify the task.
It is clear that all 70 of the index metrics interact together in a predictable
way, and some scientist should be able to take this data and reverse-engineer
the internal rules for index rebuilding, if any actually exist. For now, the most
any Oracle professional can do is to explore their indexes and learn how the
software manages the b-tree structures.
While the placebo theory is hard to deny, we believe that there are many
indexes in the world that should be reorganized (rebuilt) periodically to realize the
full benefit of fast sequential read. Many indexes grow; some indexes start with
a volatile and ever-increasing column. Reading thick index slices is becoming
more and more common, as we have seen throughout this topic.
The static metrics like LPSR and LLPSR can only divide the indexes into two
sets: those that certainly do not need a reorganization and those that may need
it. The real need can only be determined by estimating or monitoring the number
of random leaf page reads per SQL call. Then, when an SQL call that reads a
thick index slice is found, the effect of a reorganization is easy to determine.
Index growth should be predicted when an index is designed. Furthermore,
it is important to remember that when index rows are long compared to the leaf
page size, leaf page splits begin to occur when only a small part of the distributed
free space has been used. This may set an upper limit to the number of columns
that should be added to an index; sometimes it is better to create a new index.
The sensitivity of the leaf page split ratio to the length of the index row also
reduces the attraction of storing table rows in an index.
Figure 11.14 summarizes the most important issues with regard to free space
and reorganization for indexes; our recommendations for both short and medium-
sized index rows are shown, assuming the rows are inserted randomly. We
have already seen that specific recommendations cannot be provided for long
index rows.
Search WWH ::




Custom Search