Why Indexes Can Hurt Performance: Hidden Costs and Failure Scenarios

Index Overheads

Indexes improve query speed but introduce several hidden costs that become evident during data modification and large‑scale operations.

Hot‑block contention : New rows are inserted into the rightmost index blocks; concurrent inserts target the same block, causing contention.

Lookup (rowid) cost : After locating an index entry, the database must fetch the full row via the rowid, which can be expensive for large tables.

Update overhead : Maintaining the ordered structure of an index during inserts, deletes, or updates requires page splits and rebalancing, incurring significant CPU and I/O.

Build overhead : Creating an index involves sorting the indexed column, inserting sorted entries into index pages, and often locking the table; this can be a heavy operation, especially for large tables.

These costs are illustrated with a series of experiments that compare insertion speed on tables with varying numbers of indexes.

Index Invalidations

Beyond overhead, indexes can become ineffective. Invalidations fall into two categories:

Logical Invalidations

These occur when the query syntax prevents the optimizer from using an existing index, such as applying functions to indexed columns (e.g., UPPER(name)) or forcing a full‑table scan with hints.

Physical Invalidations

Physical failures happen when the index structure itself is corrupted or marked unusable, often due to operations like changing column types, executing MOVE, or manipulating partitioned tables.

Examples include converting a LONG column to CLOB (requiring index rebuild) and performing a MOVE that shrinks a table but invalidates its indexes.

Experimental Demonstrations

Several scripts were executed to quantify index overhead:

Inserting rows into a table without indexes shows relatively constant insert time.

Adding multiple indexes causes insertion time to increase sharply as the table grows.

Creating indexes on large tables generates full‑table locks and extensive sorting, observable via monitoring tools.

Index Usage Monitoring and Maintenance

Database administrators can monitor index usage with the V$OBJECT_USAGE view. Unused indexes should be dropped, while frequently used ones should be kept and possibly tuned.

Best‑practice guidelines include:

Avoid redundant single‑column indexes when a composite index can serve the same queries.

Remove indexes that the optimizer never uses.

Limit the number of columns in a composite index; more than four columns often degrade performance.

For partitioned tables, understand which operations (e.g., TRUNCATE, DROP PARTITION, SPLIT PARTITION) invalidate global or local indexes and rebuild them as needed.

By carefully weighing the benefits against these costs and regularly monitoring index health, developers can make informed decisions that balance query performance with maintenance overhead.