Indexes can provide efficient data access in many situations,
and DB2® uses them for other
purposes. However, certain costs are also associated with creating
and maintaining indexes, and you must consider these costs in your
database design.
About this task
Indexes can provide efficient
access to data through index access, data clustering, and ordering
retrieved data without a sort operation. DB2 also
uses indexes for other purposes, such as to enforce uniqueness the
uniqueness of column values, as in the case of parent keys, and to
partition tables.
However, before you begin to create indexes,
you must carefully consider their costs. You might be able to eliminate
indexes that are no longer necessary or change the characteristics
of an index to reduce disk usage.
Dropping unneeded indexes
also improves performance because of savings in index maintenance.
Procedure
When designing or evaluating indexes:
- Consider the following costs of indexes:
- Indexes require storage space. Padded indexes require more space
than non-padded indexes for long index keys. For short index keys,
non-padded indexes can take more space.
- Each index requires an index space and a data set, or as many
data sets as the number of data partitions if the index is partitioned,
and operating system restrictions exist on the number of open data
sets.
- Indexes must be changed to reflect every insert or delete operation
on the base table. If an update operation updates a column that is
in the index, then the index must also be changed. The time required
by these operations increases accordingly.
- Indexes can be built automatically when loading data, but this
process takes time. They must be recovered or rebuilt if the underlying
table space is recovered, which might also be time-consuming.
- When analyzing index access, ask the following questions:
- Can
you consolidate indexes by including non-key columns on unique indexes?
- Would adding a column to an index allow the query to use index-only
access?
- Do you need a new index?
- Is your choice of clustering index correct?