Unused Indexes

Is there any SQL query that I could use to determine any "unused"/"unwanted" indexes in a database schema?


Thanks in advance.

Analyze the schema and then issue:
select index_name, distinct_keys from dba_indexes where owner='name';
The lower the # of distinct keys, lower is the selectivity of the index.

Halo, thanks for replying.

But, let me explain to you what exactly I am looking for:

I have a table with 12 indexes (I know this number is too high) and I feel
some of them may not be required due to the changes in the application queries.
So, I want figure out the unwanted indexes and drop them. Perhaps, it would
improve the performance on the table.

Well in that case, check the queries and see which ones will use which index and then based on what I mentioned earlier, you can decide which ones to remove.

Thanks again Halo.

This application is a web based application and it generates tons of SQLs and it would
be a painful task for me to trace the instance and do the analysis.

I know Quest Software has a tool (SQL*Impact ???) that would generate a report of "unused" indexes.
I can not use this tool since I do not have direct access to the database machine.
THat is why I am asking if there any SQL query that can be run to identify "unused" indexes.

hmm.. in that case, all I can suggest is that you do what I mentioned earlier, and wait for someone to come up with a better solution.

It sounds like you are looking for a silver-bullet. Unfortunately, there is no "LAST_ACCESSED" type field in the data dictionary for indicies.

You could seperate your questionable indicies into different tablespaces and then query v$filestat to see if there is much activity. However, this method would only be an educated guess because you may have a small number of hits that are valid.

granted your oltp app is going to generate a large number of distinct queries, but how many different actual query statements are there on the table?

select something where user_id = 34, and
select something where user_id = 35

may be different queries but odds are they will be treated the same.

if you can work with the apps people to se what queries are being run on the table (hopefully not too many), you could just explain plan them all :)

Yeah...I think I have to do something like that.

However, I am curious to find out the query that SQL*Impact uses to generate "unused indexes" report ???

or actually, another route might be to find the queries used by the application and see what fields they query off of. then take a look at all the indexes, you said there were 12. if all your queries are only using a few fields, it might make more sense to just drop the ones on fields not being used at all, or just drop them all and re-create indexes from scratch with whatever decisions you would use in that situation.

not sure about the sql impact.

If you have Oracle Enterprise Manager with Diagnostic Pack which includes Oracle Expert, this will generate a report which would recommend where you have to delete unused indexes, where you need to add index (this is just a little part of report)etc etc . I believe you can download OEM from technet so diagnostic pack.

There is another tool which I wrote called EZSQL (www.ezsql.net) which will go through the sql statements in v$SQLAREA and analyze them, and figure out which indexes have been used and which ones have not. You can set up some filters so it doesn't analyze everything in V$SQLAREA, just the ones you want...

Oracle Expert goes through v$sqlarea as well.