Q

Finding unused indexes

I want to find out the unused indexes on a particular table, because most of the indexes are not used by the queries which are written based on the indexes.

I want to find out the unused indexes on a particular table, because most of the indexes are not used by the queries which are written based on the indexes.
First, query the Data Dictionary to find all indexes on that table:
SELECT index_name FROM user_indexes WHERE table_name='EMP';
For all indexes that are returned, issue the following:
ALTER INDEX index_name MONITORING USAGE;
After exercising your application, query the V$OBJECT_USAGE view. The USED column of this view will contain YES if the index was used; otherwise, it will contain NO. This will tell you if the index has ever been used since the MONITORING USAGE command was last given.

If you are not using indexes, drop them. When done, you can turn off monitoring with the following:

ALTER INDEX index_name NOMONITORING USAGE;
This was last published in September 2006

Dig Deeper on Oracle database design and architecture

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close