innovative

 Profile Answers by innovative Questions by innovative

• Feb 23rd, 2006

 

When you rebuild an index, you use an existing index as the data source. Creating an index in this manner enables you to change storage characteristics or move to a new tablespace. Rebuilding an index based on an existing data source removes intra-block fragmentation. Compared to dropping the index and using the CREATE INDEX statement, re-creating an existing index offers better performance.Improper sizing or increased growth can produce index fragmentation. To eliminate or reduce fragmentation, you can rebuild or coalesce the index.Coalescing an index online vs. rebuilding an index online. Online index coalesce is an in-place data reorganization operation, hence does not require additional disk space like index rebuild does. Index rebuild requires temporary disk space equal to the size of the index plus sort space during the operation. Index coalesce does not reduce the height of the B-tree. It only tries to reduce the number of leaf blocks. The coalesce operation does not free up space for users but does improve index scan performance. If a user needs to move an index to a new tablespace, online index rebuild is recommended. Index rebuild also improves space utilization, but .......the index rebuild operation has higher overhead than the index coalesce operationso index coalesce is right chice.....