Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Indexes are usually not maintained in Oracle databases. But indexes need to be checked regularly. If an index is created and not checked, it may cause trouble. You may encounter an index that has reached a very large size in the future, spent serious db time, slowed down the processes in the table, but was not used in any query.
The use of indexes should be monitored. Unused indexes should be dropped.
But..
When the index usage monitoring is turned on, we can find out whether the relevant index is used by the Cost Based Optimizer. This information is not sufficient to drop the index. For example, the use of primary key indexes will always be NO. Therefore, when it is dropped, it causes serious problems.
In summary, unused indexes can be dropped if they are not associated with the primary key or any sql profile.
Open Index Usage Monitoring:
1 | SQL> ALTER INDEX INDEX_NAME MONITORING USAGE; |
Once the monitor is opened, you can watch it with v $ object_usage. In addition, if you create a view as below, you will have the opportunity to examine more in detail.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | CREATE VIEW dba_object_usage (owner,object_type, index_name, table_name, monitoring, used, start_monitoring, end_monitoring ) AS SELECT do.owner, do.object_type, io.NAME, t.NAME, DECODE (BITAND (i.flags, 65536), 0, 'NO', 'YES'), DECODE (BITAND (ou.flags, 1), 0, 'NO', 'YES'), ou.start_monitoring, ou.end_monitoring FROM SYS.obj$ io, SYS.obj$ t, SYS.ind$ i, SYS.object_usage ou, dba_objects do WHERE i.obj# = ou.obj# AND io.obj# = ou.obj# AND t.obj# = i.bo# AND ou.obj# = do.object_id; |
I would like to point out that the monitoring of its usage will bring extra load to the system. After you are done, you can close it as follows.
Close Index Usage Monitoring:
1 | SQL> ALTER INDEX INDEX_ADI NOMONITORING USAGE; |
