Wednesday, August 21, 2013

Database -- Index Block Corruption/ index recreation or index rebuild

It 's better to use drop and create an index to correct an index block corrution..

As Oracle Documentation says:

ALTER INDEX ... REBUILD is faster than dropping and re-creating an index, because this statement uses the fast full scan feature. It reads all the index blocks using multiblock I/O, then discards the branch blocks. A further advantage of this approach is that the old index is still available for queries while the rebuild is in progress.

The rebuild operation does a fast full index scan, which reads the index blocks.. (consider one of the index block is corrupt..) I didnt test it, but it probably will encounter an error if an index block is corrupt.

So drop/create index seems the only solution for now.

But there is another option ALTER INDEX ... REBUILD ONLINE;

As Oracle Support Doc (When Does Offline Index Rebuild Refer To Base Table? (Doc ID 278600.1) explains, with this method accesses the table directly instead of the old index

So rebuild online becomes another solution..

No comments :

Post a Comment