Oracle-DB: Identify unused indexes
A common problem with indexes is: creating an index is quite easy in consideration of the possible impact deleting an index is much more difficult because: Who has the heart to decide this index is really no longer needed? This results in: Nearly every database system stores and maintains indexes that are not really needed. In worst cases this not needed indexes are more than half of the systems total storage Securely indentifying and deleting this unnecessary indexes may be a strong lever to improve performance and reduce resource consumption without any changes on application. How to identify unused indexes: 1. Use Oracle's own index usage monitoring You can enable index usage monitoring by issueing ALTER INDEX <Index_Name> MONITORING USAGE; Index usage is expressed as YES/NO-state that flips to YES if index is used by any executed SQL-statement. Combined with start monitoring timestamp you can identify indexes that are not used by SQL since start