|
 |
|
Oracle index rebuilding: rebuild indexes
Oracle Tips by Burleson Consulting |
Important
2009 update: The rules for
identification of candidates for index rebuilding are changing.
Please see my
updated notes on index rebuilding.
How rare are "bad" indexes?
You cannot generalize to say that index
rebuilding for performance is rare, or even medium rare, it
depends on many factors, most importantly the characteristics of
the application.
-
In scientific applications (clinical, laboratory) where large
datasets are added and removed, the need to rebuild indexes is
"common".
-
Conversely, in system that never update or delete rows, index
rebuilding rarely improves performance.
-
In systems that do batch DML jobs, index rebuilding "often"
improves SQL performance.
There is a lot of research into the benefits of
rebuilding Oracle indexes, and quite a bit of theories and rules
for
periodic rebuilding of Oracle indexes. Boris Milrud has
published a set of rules for
when to rebuild Oracle indexes.
There are many myths and legends surrounding
the use of Oracle indexes, especially the ongoing passionate debate
about rebuilding of indexes for improving performance. Some experts
claim that periodic rebuilding of Oracle b-tree indexes greatly
improves space usage and access speed, while other experts maintain
that Oracle indexes should “rarely” be rebuilt.
Oracle reports that the new Oracle10g Automatic
Maintenance Tasks (AMT) will automatically detect indexes that are
in need of re-building. Here are the pros and cons of this highly
emotional issue:
In an OracleWorld 2003 presentation titled
Oracle Database 10 g: The Self-Managing Database by Sushil Kumar of
Oracle Corporation, Kumar states that the Automatic Maintenance
Tasks (AMT) Oracle10g feature will automatically detect and rebuild
sub-optimal indexes. “AWR provides the Oracle Database 10g a very
good 'knowledge' of how it is being used. By analyzing the
information stored in AWR, the database can identify the need of
performing routine maintenance tasks, such as optimizer statistics
refresh, rebuilding indexes, etc. The Automated Maintenance Tasks
infrastructure enables the Oracle Database to automatically perform
those operations.”
Tapio Lahdenmaki and Michael Leach note in
their outstanding book on SQL optimizers "Relational
Database Index Design and the Optimizers" that:
"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 ever0-increasing column."
When can we "prove" a benefit from an index
rebuild? Here, Robin Schumacher
proves that an index that is rebuilt in a larger tablespace will
contain more index entries by block, and have a flatter structure:
"As you can see, the amount of logical
reads has been reduced in half simply by using the new 16K
tablespace and accompanying 16K data cache."
In an OracleWorld 2003
presentation titled “Oracle Database 10g: The Self-Managing
Database” by Sushil Kumar of Oracle Corporation, Kumar
states that the new Automatic Maintenance Tasks (AMT) Oracle10g
feature will "automatically detect and re-build sub-optimal
indexes.“
This
Kim Floss article shows the Oracle 10g segment advisor
recommending a rebuild of an index:
“The page lists
all the segments (table, index, and so on) that constitute
the object under review. The default view ("View Segments
Recommended to Shrink") lists any segments that have free
space you can reclaim.”
Oracle index rebuild advisor (Source: Oracle Corporation)
See my related notes on index rebuilding:
|
