Database design with indexes

You can use indexes to optimize data access, to ensure uniqueness, and to enable clustering.

If you are involved in the physical design of a database, you work with other designers to determine what columns and expressions you should index. You use process models that describe how different applications are going to access the data. This information is very important when you decide on indexing strategies to ensure adequate performance.

The main purposes of an index are:

To optimize data access

In many cases, access to data is faster with an index than without an index. If the DBMS uses an index to find a row in a table, the scan can be faster than when the DBMS scans an entire table.

To ensure uniqueness

A table with a unique index cannot have two rows with the same values in the column or columns that form the index key. For example, if payroll applications use employee numbers, no two employees can have the same employee number.

To enable clustering

A clustering index keeps table rows in a specified sequence to minimize page access for a set of rows. When a table space is partitioned, rows are clustered within each partition. Clustering can be in the same order as the partitioning. For example, if the partition is on the month and the clustering index is on the name, the rows are clustered on the name within the month.

In general, users of the table are unaware that an index is in use. Db2 decides whether to use the index to access the table.