Oracle Database: Find Unused Indexes

=======================================================
The examples below work if the user logged is the owner of the index.

1. Set the index under MONITORING:

SQL> alter index I_EMP monitoring usage;
Index altered.

SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USED START_MONITORING END_MONITORING
--------------- --------------- --- ---- ------------------- -------------------
I_EMP EMP YES NO 03/14/2001 09:17:19


2. Check if the monitored indexes are used or not through the USED column in
V$OBJECT_USAGE view:

SQL> select sal from emp where ename='SMITH';

SAL
----------
800


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'I_EMP' (NON-UNIQUE)


The explain plan indicates the index is used.


SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USED START_MONITORING END_MONITORING
--------------- --------------- --- ---- ------------------- -------------------
I_EMP EMP YES YES 03/14/2001 09:17:19


If the index was not used, the DBA could drop this unused index.


3. To stop monitoring an index, use the following SQL statement:

SQL> alter index i_emp nomonitoring usage;
Index altered.

SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USED START_MONITORING END_MONITORING
--------------- --------------- --- ---- ------------------- -------------------
I_EMP EMP NO YES 03/14/2001 09:17:19 03/14/2001 09:55:24


As soon as you turn monitoring on again for the index, both columns
MONITORING and USED are reset.


SQL> alter index i_emp monitoring usage;
Index altered.

SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USED START_MONITORING END_MONITORING
--------------- --------------- --- ---- ------------------- -------------------
I_EMP EMP YES NO 03/14/2001 09:57:27