I am trying to improve performance of a view in Oracle 11g, the view joins 11 tables, the 3 biggest tables in the query are 40, 27, 19 milions of rows. I found that each of these big tables has more then 80 different indexes. Besides that I suspect the database server has a lack of memory.
I know that many indexes can harm performance of UPDATE , INSERT AND DELETE actions.
I wonder if so many indexes might decrease the performance of complicated SELECT query?
Oracle over-indexed tables
indexoracle
Related Question
- Oracle query optimization
- Sql-server – Fragmented clustered primary key (sequential GUID) index after processing – SQL Server
- Oracle 11g R2 – How to Enable Database In-Memory Feature to Reduce Report Generation Time
- Mysql – Does using a view over standard queries and table indexes improve performance on large databases
- Performance tuning across two tables using indexes
- Rebuild materialized view from massive base tables
- Postgresql – Iterate over smaller table instead of bigger one
Best Answer
For me too many indexes means that it is more expensive to insert, update, or delete rows. Because, if the indexes are of any use they need to be maintained. Find all of the indexes with columns that you either don't query, or don't query very often and drop them. Find all of the indexes where the columns are the same, but in a different order drop all but one version of those. For example a good index would start with low cardinality and get more specific. For example country, state, city, zip code. Another good method would be that if you search by date and other fields lead with the date column to eliminate the largest number of rows first, then choose the values where the other fields match. Try to get the number of indexes down to less than 10 per table.
Although the number of indexes that are useful depends on whether this is OLTP, DSS or data warehouse.