怎么判断是否该重建索引了？

Maclean Liu(刘相兵

参考 这个 note

Index Rebuild, the Need vs the Implications

There have been many discussions about whether rebuilding indexes is useful or not. Generally speaking, the
need to rebuild b-tree indexes is very rare, basically because a b-tree index is largely self-managed or selfbalanced.
The most common justifications given for rebuilding an index are:
- index becomes fragmented
- index grows and grows - deleted space is not re-used
- index clustering factor becomes out of sync
In fact most indexes remain both balanced and fragmentation-free because free leaf entries will be reused.
Inserts/Updates and Deletes result in free slots being scattered around the index blocks, but these will typically be
refilled.
The clustering factor reflects how sorted the table data is with respect to the given index key. Rebuilding an
index never has an influence on the clustering factor but instead requires a table re-organization.
Secondly the impact of rebuilding the index can be quite significant, please read the following comments
thoroughly:
1. Most scripts around depend on the index_stats dynamic table. This is populated by the command:
2/25/13 Document 989093.1


analyze index ... validate structure;
While this is a valid method to inspect the index, it grabs an exclusive table lock while analyzing the index.
Especially for large indexes, this can be very dramatic, as DML operations on the table are not permitted during
that time. While it can be run online without the locking considerations, it may consume additional time.
2. Redo activity and general performance may increase as a direct result of rebuilding an index.
Insert/update/delete causes the index to evolve over time as the index splits and grows. When the index is
rebuild it will become more tightly packed; however as DML operations continue on the table the index splits have
to be redone again until the index reaches it's equilibrium. As a result, the redo activity increases and the index
splits are now more likely to impact performance directly as we consume more I/O, CPU, etc to serve the index
restructuring. After a certain period of time the index may again experience 'issues' and may be re-flagged for a
rebuild, causing the vicious cycle to continue. Therefore it is often better to leave the index in its natural
equilibrium and/or at least prevent indexes from being rebuilt on a regular basis.
3. An index coalesce is often preferred instead of an index rebuild. It has the following advantages:
- does not require approximately 2 times the disk storage
- always online
- does not restructure the index, but combines index leaf blocks as much as possible, avoiding
system overhead as explained in point 2.
Note: To re-allocate an index, to another tablespace for example a rebuild is required.
Due to the reasons listed above, it is strongly advised not to rebuild indexes on a regular basis but instead use

我也向上

仅建议：
你也可以通过查看字典如：DBA_INDEXES，确认索引的层高，BLEVEL>=4以上，就建议做下rebuild index.

我也向上

那么多索引，你也可以用过程监控一段时间，索引是否在使用，长时间没有使用的该删除就删除了。保持有效、整洁、完整。