Submitted by michael_bialik on Tue, 2006-08-01 04:22
Indexes are used to speed up data access by SQL statements, but there is no free lunch as each additional index increases:
- The time needed to perform DML (Insert/Update/Delete) operation on the table (because additional index entries must be updated).
- The enqueue time (during DML the corresponding index entries are locked decreasing the ability of parallel updates and causing transactions, issued by another session(s) to wait.
- The generated UNDO volume.
- The disk space needed to store the index information.