Rebuilding Indexes

when to rebuild index

the index may be rebuild in following situations

1)If the existing index is to moved to different tablespace.
2) If index contains many deleted entries.
3) If the table of index has been moved to another tablespace.

in case of rebuilding an index

1) A new index is built using an existing index as data source.
2)the older index is deleted after the new index is built.
3)queries can continue to use the existing index while the new index is being built.

example:

ALTER INDEX mytable_idx REBILD
TABLESPACE newtb;

monitoring index usage

From Oracle 9i ,statistic about the usage of an index can be gathered and displayed in V$OBJECT_USAGE . If the information shows that the index is never used , then that index can be dropped.

To start the index monitoring

ALTER INDEX mytable_idx
MONITORING USAGE

To stop the index monitoring

ALTER INDEX mytable_idx
NOMONITORING USAGE


SQL> select * from v$object_usage ;

INDEX_NAME TABLE_NAME MON USE
------------------------------ ------------------------------ --- ---
START_MONITORING END_MONITORING
------------------- -------------------
MYTABLE_IDX MYTABLE NO YES
02/08/2010 11:40:35 02/08/2010 11:41:27