Indexes on table columns

If you are involved in the physical design of a database, you will be working with other designers to determine what columns you should index.

You will use process models that describe how different applications are going to be accessing the data. This information is 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.

  Unique indexes can include additional columns that are not part of a unique constraint. Those columns are called INCLUDE columns. When you specify INCLUDE columns in a unique index, queries can use the unique index for index-only access. Including these columns can eliminate the need to maintain extra indexes that are used solely to enable index-only access.

• To enable clustering

  A clustering index keeps table rows in a specified sequence to minimize page access for a set of rows.

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.