Designing indexes for performance

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

Begin program-specific programming interface information.

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.

Tip: Query tuning capabilities that can help you with this task, such as visual explain and statistics advisor, are available in IBM® Db2 Administration Foundation for z/OS® and IBM Db2 for z/OS Developer Extension.

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?
    • Start of changeCan your indexes take advantage of fast index traversal?End of change

    End program-specific programming interface information.