When is the overhead for indexes more than the benefit of the indexes?
735819Jan 26 2010 — edited Jan 27 2010I working with an Oracle 10g system that has a main fact table around 10 terabytes in size. The table has around 60 partitions. The system originally had low volume and global indexes on most columns. As the table has grown queries against the main fact table have drastically slowed. The data is volitile with large numbers of inserts, deletes and occasional updates. Explains of the read only queries usually indicate table scans even if the where condition is working with globally indexed columns within a single partition.
I'm concerned with the use of global indexes on such a large and volitile table. Statistics are infrequently run on the table due to the amount of time it requires.
Do you think that global indexes are past the point of diminishing returns with this type of design and volume or should I be looking in a different direction to improve the performance of this table?
Edited by: JMarkW on Jan 26, 2010 12:47 PM