Types of indexes

In DB2® for z/OS®, you can create a number of different types of indexes. Carefully consider which type or types best suit your data and applications.

All of the index types are listed in the following tables. These index types are not necessarily mutually exclusive. For example, a unique index can also be a clustering index. Restrictions are noted.

The following table lists the types of indexes that you can create on any table.

Table 1. Index types that are applicable to any table type
Type	Description	SQL syntax	More information
Unique index	An index that ensures that the value in a particular column or set of columns is unique.	`CREATE INDEX... UNIQUE...`	Unique indexes Nonunique indexes
Primary index	A unique index on the primary key of the table. A primary key is column or set of columns that uniquely identifies one row of a table. You define a primary key when you create or alter a table; specify PRIMARY KEY in the CREATE TABLE statement or ALTER TABLE statement. Primary keys are optional. If you define a primary key on a table, you must define a primary index on that key. Otherwise, if the table does not have a primary key, it cannot have a primary index. Each table can have only one primary index. However, the table can have additional unique indexes.	No keywords are required in the CREATE INDEX statement. An index is a primary index if the index key that is specified in the CREATE INDEX statement matches the primary key of the table.	Defining a parent key and unique index
Secondary index	An index that is not a primary index. In the context of a partitioned table, a secondary index can also mean an index that is not a partitioning index. See Table 2.	No keywords are required in the CREATE INDEX statement. A secondary index is any index that is not a primary index or partitioning index.	None
Clustering index	An index that ensures a logical grouping. When data is inserted into the table, the clustering index attempts to maintain the clustering sequence within the partition. Each table can have only one clustering index.	`CREATE INDEX... CLUSTER...` or `ALTER INDEX... CLUSTER...`	Clustering indexes
Expression-based index	An index that is based on a general expression. Use expression-based indexes when you want an efficient evaluation of queries that involve a column-expression.	In the CREATE INDEX or ALTER INDEX statement, the index key is defined as an expression rather than a column or set of columns.	Expression-based indexes

The following table lists the types of indexes that you can create on partitioned tables. These indexes apply to range-partitioned universal tables. They do not apply to partition-by-growth universal table spaces.

Table 2. Index types that are applicable to only partitioned tables
Type	Description	SQL syntax	More information
Partitioned index	An index that is physically partitioned. A partitioned index consists of multiple data sets. Each data set corresponds to a table partition.	`CREATE INDEX... PARTITIONED...`	Indexes on partitioned tables
Partitioning index (PI)	An index that corresponds to the columns that partition the table. These columns are called the partitioning key and are specified in the PARTITION BY clause of the CREATE TABLE statement. All partitioning indexes must also be partitioned. Partitioning indexes are not required.	No keywords are required in the CREATE INDEX statement. An index is a partitioning index if the index key matches the partitioning key. To confirm that an index is partitioning index, check the SYSIBM.SYSINDEXES catalog table. The INDEXTYPE column for that index contains a P if the index is a partitioning index.	Indexes on partitioned tables
Secondary index	Depending on the context, a secondary index can mean one of the following two things: An index that is not a partitioning index. An index that is not a primary index.	No keywords are required in the CREATE INDEX statement. A secondary index is any index that is not a primary index or partitioning index.	Indexes on partitioned tables
Data partitioned secondary index (DPSI)	A partitioned index that is not a partitioning index. These indexes are also called partitioned secondary indexes (PSIs).	`CREATE INDEX... PARTITIONED...` Also, the specified index key must not match the partitioning key.	Indexes on partitioned tables
Nonpartitioned secondary index (NPSI)	An index that is not partitioned or partitioning. These indexes are also called nonpartitioned indexes (NPIs).	The CREATE INDEX statement does not include the PARTITIONED keyword. Also, the index key does not match the partitioning key.	Indexes on partitioned tables
Multi-piece index	A nonpartitioned index that has multiple data sets. The data sets do not correspond to data partitions. Use a multi-piece index to spread a large index across multiple data sets and thus reduce the physical I/O contention on the index.	`CREATE INDEX... PIECESIZE ...` or `ALTER INDEX... PIECESIZE ...`	None

The following table lists the XML index type.

Table 3. Index types that are applicable to only tables with XML columns
Type	Description	SQL syntax	More information
XML index	An index that uses a particular XML pattern expression to index paths and values in XML documents that are stored in a single XML column.	`CREATE INDEX... GENERATE KEY USING XMLPATTERN` or `CREATE INDEX... GENERATE KEYS USING XMLPATTERN`	XML index attributes

Additionally, when you create any of these types of indexes, you can define whether they have the following characteristics:

Table 4. General index characteristics
Characteristic	Description	SQL syntax	More information
Padded	Any varying-length string columns in the index are padded with the default pad character to their maximum length.	`CREATE INDEX... PADDED ...` or `ALTER INDEX... PADDED...`	Indexes that are padded or not padded
Compressed	The data is compressed to reduce the size of the index on disk.	`CREATE INDEX... COMPRESS YES...` or `ALTER INDEX... COMPRESS YES...`	Compression of indexes