-
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
-
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
-
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
-
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
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|