How do indices impact query performance?

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?

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.

StackExchange Database Administrators Q#1733, answer score: 15