Unused Indexes
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Unused Indexes

  1. #1
    Join Date
    Sep 2000
    Posts
    155

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


    Thanks in advance.

  2. #2
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    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.

  3. #3
    Join Date
    Sep 2000
    Posts
    155

    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.

  4. #4
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    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.

  5. #5
    Join Date
    Sep 2000
    Posts
    155

    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.

  6. #6
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    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.

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095

    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    Jun 2000
    Posts
    417
    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 :)

  9. #9
    Join Date
    Sep 2000
    Posts
    155

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

  10. #10
    Join Date
    Jun 2000
    Posts
    417
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width