How to Find Unused Indexes
You can use Monitoring feature of Oracle to find which indexes are unused. You can run the following query to enable monitoring on an index:
ALTER INDEX index_name MONITORING USAGE;
After you enable indexing, you should wait for a while (for example 1-2 days if you think that this index should be used daily), and then you can query v$object_view to see if index is used:
|
1 2 3 4 5 |
SELECT * FROM v$object_usage; INDEX_NAME,TABLE_NAME,MONITORING,USED,START_MONITORING,END_MONITORING ———————————————————————————————————————- EMP_EMAIL_UK,EMPLOYEES,YES,YES,08/28/2010 22:06:23,08/28/2010 22:24:10 |
You can run the following query to disable monitoring of an index:
ALTER INDEX index_name NOMONITORING USAGE;
Disabling monitoring an index, does not change the “used” value on v$object_usage; If you query that view, you should still use the index as used. Because the “used” property shows if index is used between start_monitoring and end_monitoring period.