General index attributes

You typically determine which type of index you need to define after you define a table space. An index can have many different attributes.

Index attributes fall into two broad categories: general attributes that apply to indexes on all tables and specific attributes that apply to indexes on partitioned tables only. The following table summarizes these categories.

Table 1. Index attributes
Table or table space type	Index attribute
Any	Unique or nonunique Clustering or nonclustering Padded or not padded
Partitioned	Partitioning Secondary

This topic explains the types of indexes that apply to all tables. Indexes that apply to partitioned tables only are covered separately.

Unique indexes
DB2® uses unique indexes to ensure that no identical key values are stored in a table.
Nonunique indexes
You can use nonunique indexes to improve the performance of data access when the values of the columns in the index are not necessarily unique.
Clustering indexes
A clustering index determines how rows are physically ordered (clustered) in a table space. Clustering indexes provide significant performance advantages in some operations, particularly those that involve many records. Examples of operations that benefit from clustering indexes include grouping operations, ordering operations, and comparisons other than equal.
Indexes that are padded or not padded
The NOT PADDED and PADDED options of the CREATE INDEX and ALTER INDEX statements specify how varying-length string columns are stored in an index.
Expression-based indexes
By using the expression-based index capability of DB2, you can create an index that is based on a general expression. You can enhance query performance if DB2 chooses the expression-based index.
Compression of indexes
You can reduce the amount of space that an index occupies on disk by compressing the index.

Parent topic: Creation of indexes

Related concepts:

Indexes on partitioned tables