|
 |
|
Oracle index rebuilding: rebuild indexes
Oracle Tips by Burleson Consulting |
Important
2007 update: The rules for
identification of candidates for index rebuilding are changing.
Please see my
updated notes on index rebuilding.
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 ion
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 be 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:
 |
|
Need an Oracle Health Check?
- Do you have
bad performance after an upgrade?
- Need to
certify that your database follows best practices?
BC Oracle performance gurus can quickly
certify every aspect of your
Oracle database and provide a complete verification that your database
is fully optimized. |
|
|
