Designing Tables and Indexes for Performance › Indexing › Index Design Recommendations › Secondary Indexes

Secondary Indexes

Two types of secondary indexes exist; non-partitioning secondary indexes and data-partitioned secondary indexes as follow:

Non-Partitioning Secondary Indexes (NPSIs)

Are used on partitioned tables. They are not the same as the clustered partitioning key, which is used to order and partition the data, but rather they are for access to the data. NPSIs can be unique or non unique. You can have only one clustered partitioning index, but you can have several NPSIs on a table, if necessary. NPSIs can be broken into multiple pieces (data sets) by using the PIECESIZE clause on the CREATE INDEX statement. Pieces can vary in size from 254 KB to 64 GB—the optimum size depends on the amount of data you have and how many pieces you want to manage. If you have several pieces, you can achieve more parallelism on processes, such as heavy INSERT batch jobs, by alleviating the bottlenecks that are caused by contention on a single data set. In DB2 V8 and later, the NPSI can be the clustering index.

Note: NPSIs are good for fast read access because there is a single index b-tree structure. NPSIs can become extremely large and can cause maintenance and availability issues.

Data-Partitioned Secondary Indexes (DPSIs)

Provides many advantages over the traditional NPSIs for secondary indexes on a partitioned table space in terms of availability and performance.

The partitioning scheme of the DPSI is the same as the table space partitions, and the index keys in 'x' index partition matches those in 'x' partition of the table space. Some of the benefits that this provides include the following:

• Clustering by a secondary index
• Ability to easily rotate partitions
• Efficient utility processing on secondary indexes (no BUILD-2 phase)
• Allow for reducing overhead in data sharing (affinity routing)