Chapter 2. Index design 63
2.3 Cost of an index
An index can improve performance by reducing the number of rows and pages that have to be
accessed, which provides more efficient access. However, overall performance must also be
evaluated. Indexes also have an associated cost due to:
The additional cost to maintain the index. The cost can be divided into:
– CPU cost to update the index during an INSERT, UPDATE that references a column in
the index, or DELETE operation.
– Additional I/Os that may be required to make the required changes to the index. The
I/O impact can be very different depending on the
cluster ratio of the index. If the
processing sequence is the same as the clustering index:
• Indexes with a high cluster ratio will incur sequential data I/O and are likely to
benefit from asynchronous I/Os, and the application will probably not have to wait.
• Indexes with a low cluster ratio will incur random data I/O.
So, depending on the index columns and their correlation with the table’s clustering
sequence, adding an index can have a different impact on the application’s performance.
Adding an index is not at a linear cost.
Higher CPU usage and longer elapsed times for utilities, such as RECOVER, LOAD, and
REORG that need to maintain the index.
Disk space used by the index.
Maintaining the index
Indexes are great for SELECT operations, since they normally speed up the data retrieval
considerably, but you always have to keep in mind that other operations that need to maintain
the index, such as an INSERT/DELETE statement, require extra work when an index is
involved. In case of an INSERT/DELETE, not only does DB2 have to insert/delete the row into
the table, but it also has to put/remove the key columns and the corresponding RID of the row
in all the indexes that are defined on the table.
The costs of maintaining indexes are often higher than the costs of maintaining the data. In a
data sharing environment, inserting, updating, or deleting index entries comes at an even
higher cost, because of the extra P-lock and cross-invalidation overhead that may be
necessary to guarantee data integrity.
INSERT, UPDATE, and DELETE frequency
An INSERT, DELETE, or UPDATE operation modifying columns included in an index requires
extra work when indexes are involved. This is because DB2 has to maintain those indexes,
not just only the data. Therefore, when deciding which indexes to build, it is important to
consider the amount of update activity (INSERT, UPDATE, and DELETE) against the table.
With very little update activity, you can afford more indexes compared to a case where keys
need to be updated all of the time.
Also, do not forget to take into consideration the update activity caused by referential integrity
if the table has an (indexed) foreign key defined that is defined as ON DELETE SET NULL, or
ON DELETE CASCADE. If there is an index defined on that foreign key, frequent deletes of
rows in the parent table can cause heavy activity on the dependent table and its indexes.
If an indexed column in a table is updated, the key (and RID) is deleted from its existing
position in the index and inserted with the new value in the correct (new) position. To make
sure the deleted key does not have to be reinserted in case a rollback is performed, the