Databases Reference
In-Depth Information
Caution
If you rebuild the table to “fix” the clustering factor for one index, you may mess up the clustering
factor for another index. Since the table can only be rebuilt in one order, any index that doesn't match that order
will have a less than perfect clustering factor. In our opinion, you need a really good reason (backed with proof) to
rebuild a table to try to achieve a “good” clustering factor.
Arguments Against Rebuilding
This section summarizes the most important reasons why you should not be doing automatic index
rebuilds based on the deleted percentage of index entries.
Locking issues during rebuilds: The
analyze index …validate structure
command could result in massive locking issues, so this is something to keep in
mind if you're using the
DEL_IF_ROWS
statistics as your criterion for rebuilding
indexes. In previous releases, even an online rebuild meant that the database
applied locks, thus blocking users from doing their work until the index rebuilds
were completed. Starting with Oracle 10g, an online rebuild of an index doesn't
involve locking of the index.
•
•
Excessive redo generation: Index rebuilds generate massive amounts of redo. If
you use the
nologging option,
however, this is a non-issue.
Our Advice
There are numerous discussions on various blogs as to whether one should rebuild indexes or not on a
frequent basis. Most of these discussions relate to the points we mentioned under the arguments for and
against rebuilding of indexes. The following is our advice without rehashing all the discussions in favor
and against rebuilding indexes.
Despite Oracle's own advice, there's a strong perception among developers and DBAs (and their
managers!) that index rebuilds are strongly correlated with performance. One of the most common
reasons put forward by DBAs to rebuild indexes on a regular basis is that rebuilding improves
performance and recovers space. In fact, Oracle documentation itself mentions these as the two top
reasons to rebuild indexes.
It is not uncommon for a DBA to be asked how frequently they schedule index rebuilds when a
serious performance issue crops up. At an intuitive level, an index "rebuild" seems to be something that
is "good" for the database. However, we urge you to read Richard Foote's well-documented (and
extremely well detailed) presentation on why an automatic index rebuild isn't necessary:
If most of the queries in your database read only single rows via an index access, an index rebuild
isn't likely to make any impact on performance. However, if you have a case where you identify extreme
index fragmentation with heavy deletions from the same portion of the index
and
the queries read
numerous index rows each time,
and
the index is heavily utilized, it makes sense to rebuild the index.
Note that these are rare cases and usually involve an index with a lefthand (older) side "brown" leaves
with heavy deletion of index entries. The index will become more compact and your queries will run
much faster in such a case. Note that if
all
index entries are deleted from the lefthand side, Oracle
database automatically moves the index leaf blocks from the left (older) side to the right (newer ) side.
