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?

  • 2
    why don't you test this out, and report back with your results? :) Commented Mar 14, 2011 at 18:02
  • I will do tomorrow :)
    – paweloque
    Commented Mar 14, 2011 at 20:52
  • 2
    Indexes do not necessarily slow down deletes, they can also speed them up (just like updates can take advantage of an index)
    – user1822
    Commented May 28, 2011 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
    Commented Sep 26, 2011 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:

  • The indexed columns are queried frequently and return a small percentage of the total number of rows in the table.

  • A referential integrity constraint exists on the indexed column or columns. The index is a means to avoid a full table lock that would otherwise be required if you update the parent table primary key, merge into the parent table, or delete from the parent table.

  • A unique key constraint will be placed on the table and you want to manually specify the index and all index options.

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.


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.


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

The more indexes, the more overhead is incurred as the table is altered. When rows are inserted or deleted, all indexes on the table must be updated. When a column is updated, all indexes on the column must be updated.

You must weigh the performance benefit of indexes for queries against the performance overhead of updates. For example, if a table is primarily read-only, you might use more indexes; but, if a table is heavily updated, you might use fewer indexes.

  • 2
    From the question: "Obviously keeping several different indices have a negative impact on insert and delete performance. How about query performance" Commented Sep 26, 2011 at 16:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.