Take the 2-minute tour ×

Obviously keeping several different indices has a negative impact on insert and delete performance. How about query performance: Does it make sense at all keeping too many indices on a table? Will the query performance improve in any case with an index added (of course for queries using the index at all) or is it even possible that the query performance will degrade with too many indices because it becomes necessary to consult all the indices to get the result?

In case there are different indices on a table: will they all be considered, or only the best from the optimizer point of view? Does Oracle implement multi-dimensional indices?

share|improve this question
why don't you test this out, and report back with your results? :) –  Neil McGuigan Mar 14 '11 at 18:02
I will do tomorrow :) –  paweloque Mar 14 '11 at 20:52
Indexes do not necessarily slow down deletes, they can also speed them up (just like updates can take advantage of an index) –  a_horse_with_no_name May 28 '11 at 14:07
@el chief what's point in reinventing the wheel? so yes the query performance effect is in question though we know for sure that the inserts/deletes would be affected. How about updates? will they be negatively affected? –  Sangram Sep 26 '11 at 6:39

3 Answers 3

It will take marginally longer to generate the plan when there are more indexes to consider, but I doubt the difference would be measurably significant. The reasons for dropping an index does not list query performance. On the other hand, in general you shouldn't create indexes unless you know they will be used to make a query more efficient.

From the Oracle Concepts Guide, here are the criteria for creating an index.

In general, consider creating an index on a column in any of the following situations:




All indexes will be considered in the sense that all indexes on the tables in the query are examined to determine whether they could be used. Those that could be are further examined to determine usefulness.

share|improve this answer

So long as your statistics are up to date, the cost-based optimizer ought to make sensible decisions as to which indexes to use. If it doesn't, that is the time to use hints. It will never consult an index on a column that isn't necessary to satisfy the query.

share|improve this answer

I found this in the Selecting an Index Strategy of the Application Developer's Guide. That page is about 'selecting an index strategy':

Limit the Number of Indexes for Each Table



share|improve this answer
From the question: "Obviously keeping several different indices have a negative impact on insert and delete performance. How about query performance" –  Jack Douglas Sep 26 '11 at 16:09

Your Answer


