La gran discusión, algunos DBA's SSr dicen que si, Oracle suport dice que no es necesario. Aun no he definido mi posición, pero lo que puedo sacar en limpio es que las distintas visiones concuerdan que hay que realizar el ANALYZE INDEX ,,,, VALIDATE STRUCTURE/COMPUTE STATICS para permitir al RDMS tomar mejores decisiones tanto a nivel estructural como en los planes de ejecucion.
Estos artículos son interesante como para comenzar.
http://www.dba-oracle.com/t_oracle_analyze_index.htm
http://www.dba-oracle.com/t_index_rebuilding_issues.htm
http://www.dba-oracle.com/t_scheduling_oracle_index_rebuilding.htm
============================================================================================
Oracle DocID 989093.1
Applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7.0 to 11.2.0.1.0 - Release: 8.1.7 to 11.2
Information in this document applies to any platform.
Purpose
This article will try to outline the implications of rebuilding indexes. Indexes are often rebuilt on a regular basis, but in fact the call as to whether an index benefits from a rebuild is often not based on statistical indications and seldom is a rebuild history for an index kept.
Scope and Application
This article is intended for Database Administrators.
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 self-balanced.
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:
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.
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 proper diagnostics.
Please see the following note which lists a script that can be used to analyze the index structure. It does not use the 'analyze index validate structure' command but is based on the current table and index statistics to estimate the index size.
Note 989186.1 : Script to investigate a b-tree index structure
