Too many indexes
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Too many indexes

  1. #1
    Join Date
    Mar 2003
    Posts
    38

    Too many indexes

    I have an application that uses a table that has around 25 columns. The app was poorly designed and the table really needs to be broken up into multiple tabes but unfortunatly I can't do that right now. The table has about 700,000 rows in it.

    Anyway there are currently 40 indexes on the table. The indexes have various column combinations. Performance on this table is horrible(selects especially). There are a lot of inserts and updates happening. I am wondering... Are there too many indexes on the table? Can there be too many indexes? Especially since indexes are updated on inserts, updates, and deletes?

    I thought I heard someone say one time that it would be better to have a single index for each column? I could be off on that. Just looking for some clarification and information on indexes?

    Nathan

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    40 indexes and 25 columns and your queries are still slow? Something sounds pretty wrong there... You only have 700K rows, how slow could a FTS be?
    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."

  3. #3
    Join Date
    Mar 2003
    Posts
    38
    No kidding. You think I would have covered most combinations with that many indexes. I think they are confusing the CBO because there is so many of them. This is version 8.1.7. I am grasping at straws here. Just trying to gather some info.

    Nathan

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    my guess is a majority of the indexes are not being used. If I were in this situation, I would look at my most common queries and create appropriate indexes, probably no more than 4 or so...
    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."

  5. #5
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    Just check when the table got analyzed last and when the indexes where created and the last DDL time. And check out for execution plans of the queries on the table. Check for top queries using..

    column sql format a75
    select * from
    (select hash_value, executions, disk_reads, buffer_gets, substr(sql_text, 1, 75) sql
    from v$sqlarea order by 3 desc , 4 desc)
    where rownum < 13

    and find out any of these top queries are based on the mentioned table. Rebuilding could be of great use and I go for what marist said. Poor design.. so check out the frequently used queries on this table and create indexes based on these queries.
    -nagarjuna

  6. #6
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    start with monitoring the indexes to see if they get used
    with ALTER INDEX MONITORING USAGE and then look at v$object_usage..

    Opionions may vary on how many indexes are just right. On a DW table who cares, if it's oltp you'll want to benchmark at which point
    diminishing returns make extra indexes more harmful then helpful
    I'm stmontgo and I approve of this message

  7. #7
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    Originally posted by stmontgo
    start with monitoring the indexes to see if they get used
    with ALTER INDEX MONITORING USAGE and then look at v$object_usage..

    Opionions may vary on how many indexes are just right. On a DW table who cares, if it's oltp you'll want to benchmark at which point
    diminishing returns make extra indexes more harmful then helpful
    This is 9i feature. For 8i we dont have anything to monitor index usage as such.. but, we have something to monitor segment i/o. catio.sql is the script provided for this purpose. But, this script never worked and didnt have patience to gig into this script after that.. Script is under default $ORACLE_HOME/rdbms/admin location. Will be happy if somebody could experiment and post the results
    -nagarjuna

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