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