12

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?

Nicolas Kaiser
  • 129
  • 1
  • 1
  • 9
paweloque
  • 1,089
  • 2
  • 14
  • 23
  • 2
    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
  • 2
    Indexes do not necessarily slow down deletes, they can also speed them up (just like updates can take advantage of an index) –  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 06:39

3 Answers3

15

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.

Leigh Riffel
  • 23,854
  • 16
  • 78
  • 152
7

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.

Community
  • 1
Gaius
  • 11,200
  • 3
  • 31
  • 64
0

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.

Community
  • 1
Sangram
  • 9
  • 1
  • 2
    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