Unused Indexes

[rpardip]

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


Thanks in advance.

[Halo]

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.

[rpardip]

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.

[Halo]

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.

[rpardip]

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.

[Halo]

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.

[marist89]

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.

[pwoneill]

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 :)

[rpardip]

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 ???

[pwoneill]

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.