Find unused indexes with MONITORING USAGE clause
                                                 Last update (2008-07-30 13:05:06)
                                                                                                                   Date added (2008-03-05 11:53:19)

Summary
Creating an index doesn't mean that the index will be used. To find unused indexes for a period of time in Oracle9i and afterwards you must enable the INDEX MONITORING USAGE

Example
Create a table OBJECTS with an index.
CREATE TABLE OBJECTS AS SELECT * FROM ALL_OBJECTS;
CREATE INDEX OBJECTS_IDX ON OBJECTS(OBJECT_TYPE);
Query view to see if index is monitored.
SELECT * FROM V$OBJECT_USAGE;
No monitor is enabled. Enable index monitoring and check the view if started monitoring.
ALTER INDEX OBJECTS_IDX MONITORING USAGE;
SELECT * FROM V$OBJECT_USAGE;
Now use the index with the query
SELECT COUNT(*) FROM objects 
WHERE object_type = 'TABLE';
Query again to see how monitor is going.
SQL> SELECT * FROM V$OBJECT_USAGE;

INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
OBJECTS_IDX                    OBJECTS                        YES YES 03/05/2008 11:43:55
To stop monitoring execute
ALTER INDEX OBJECTS_IDX NOMONITORING USAGE;
To enable and disable monitoring indexes for a specific schema use the following 2 queries:
SELECT 'alter index ' || owner || '.' || index_name || ' MONITORING USAGE;' monitor_index_sql 
FROM dba_indexes 
WHERE owner = 'AP';

SELECT 'alter index ' || owner || '.' || index_name || ' NOMONITORING USAGE;' monitor_index_sql 
FROM dba_indexes 
WHERE owner = 'AP';  
To drop the unused indexes(what is the meaning to support something useless?) execute:
SELECT 'drop index ' || I.OWNER || '.' || U.index_name || ';' drop_sql 
FROM V$OBJECT_USAGE U, DBA_INDEXES I 
WHERE USED = 'NO' 
AND U.INDEX_NAME = I.INDEX_NAME 
AND U.TABLE_NAME = I.TABLE_NAME;
Reviews
Categories
Filters
Search