Indexes on partitioned tables

The following types of indexes apply to only partitioned tables: partitioned indexes, partitioning indexes (PIs), data-partitioned secondary indexes (DPSIs), and nonpartitioned secondary indexes (NPIs or NPSIs).

Partitioned index

A partitioned index is an index that is physically partitioned. Any index on a partitioned table, except for an XML index, can be physically partitioned.

To create a partitioned index, specify PARTITIONED in the CREATE INDEX statement.

A partitioned index consists of multiple data sets. Each data set corresponds to a table partition. The following figure illustrates the difference between a partitioned index and a nonpartitioned index.

Figure 1. Comparison of partitioned and nonpartitioned index
Begin figure description. This figure illustrates the difference between a partitioned and a nonpartitioned index. End figure description.

Partitioning index

A partitioning index is an index on the column or columns that partition the table. Partitioning indexes are generally not required because Db2 uses table-controlled partitioning, where the partitioning scheme (the partitioning key and limit key values) are already defined in the table definition.

The CREATE INDEX statement does not have a specific SQL keyword that designates an index as a partitioning index. Instead, an index is a partitioning index if the index key that is specified in the CREATE INDEX statement matches the partitioning key. The partitioning key is the column or columns that are specified in the PARTITION BY clause of the CREATE TABLE statement. Those columns partition the table. An index key matches the partitioning key if it has the same leftmost columns and collating sequence (ASC/DESC) as the columns in the partitioning key.

A partitioning key is different from the limit key values. A partitioning key defines the columns on which the table is partitioned. The limit key values define which values belong in each partition. Specifically, a limit key value is the value of the partitioning key that defines the partition boundary. It is the highest value of the partitioning key for an ascending index, or the lowest value for a descending index. Limit key values are specified in the PARTITION... ENDING AT clause of a CREATE TABLE statement or ALTER TABLE statement. The specified ranges partition the table space and the corresponding partitioning index space.

Remember: Partitioning is different from clustering. Whereas, partitioning guarantees that rows are grouped into certain partitions based on value ranges defined partition limit key, clustering controls how rows are physically ordered in a partition or table space. Clustering is controlled by a clustering index and can apply to any type of table space. For more information, see Clustering indexes.

Tables created in earlier Db2 releases might still use index-controlled partitioning, where the partitioning scheme was not defined as part of the table definition. In this case, a partitioning index is required to specify the partitioning scheme. (The partitioning key and the limit key values were specified in the PART VALUES clause of the CREATE INDEX statement.)

Deprecated function: Db2 13 can still process range-partitioned tables and indexes that use index-controlled partitioning. However, such tables and indexes are deprecated. For best results, convert them to use table-controlled partitioning (and a PBR table space) as soon as possible. For more information, see Converting table spaces to use table-controlled partitioning.

Example partitioning index

Begin general-use programming interface information.For example, assume that you created an AREA_CODES table that contains area codes by state by issuing the following CREATE TABLE statement.

CREATE TABLE AREA_CODES
  (AREACODE_NO  INTEGER  NOT NULL,
   STATE        CHAR (2) NOT NULL)

   PARTITION BY RANGE (AREACODE_NO)
   (PARTITION 1 ENDING AT (299),
    PARTITION 2 ENDING AT (399),
    PARTITION 3 ENDING AT (499),
    PARTITION 4 ENDING AT (599),
    PARTITION 5 ENDING AT (699),
    PARTITION 6 ENDING AT (799),
    PARTITION 7 ENDING AT (899),
    PARTITION 8 ENDING AT (MAXVALUE));

Optionally, you can issue the following CREATE INDEX statement to create a partitioning index on the example AREA_CODES table. This index is not required because the partitioning scheme of the AREA_CODES table is defined in its CREATE TABLE statement, and AREA_CODES uses table -controlled partitioning.

CREATE INDEX AREACODE_IX1 ON AREA_CODES (AREACODE_NO)
  CLUSTER PARTITIONED;
Tip: If you use a partitioning index for clustering, the data rows can be physically ordered across the entire table space.
End general-use programming interface information.

The following figure illustrates the partitioning index on the AREA_CODES table.

Figure 2. Partitioning index on the AREA_CODES table
Begin figure description. This figure illustrates the partitioning index on the AREA_CODES table. End figure description.
Related information

Nonpartitioning index (secondary index)

An index that is not a partitioning index is a nonparitioning index or secondary index. You can create a secondary index on a table to enforce a unique constraint, to cluster data, or to provide access paths to data for queries.

The usefulness of an index depends on the columns in its key and the cardinality of the key. Columns that you frequently select, join, group, or order are good candidates for keys. In addition, the number of distinct values in an index key for a large table must be sufficient for Db2 to use the index to retrieve data. Otherwise, Db2 might choose to do a table space scan.

You can create two types of secondary indexes: those that are partitioned (called data-partitioned secondary indexes) and those that are nonpartitioned (called nonpartitioned secondary indexes).

Data-partitioned secondary index (DPSI)
A data-partitioned secondary index (DPSI) is a nonpartitioning index that is physically partitioned according to the partitioning scheme of the underlying data.

A DPSI has as many partitions as the number of partitions in the table space. Each DPSI partition contains keys for the rows of the corresponding table space partition only. For example, if the table space has three partitions, the keys in DPSI partition 1 reference only the rows in table space partition 1; the keys in DPSI partition 2 reference only the rows in table space partition 2, and so on.

Restrictions:
  • You can create a DPSI only on a table in a partitioned table space.
  • You cannot create a DPSI for a partition-by-growth table space.
  • An XML index cannot be a DPSI.

To define a DPSI, use the PARTITIONED keyword in the CREATE INDEX statement and specify an index key that does not match the partitioning key columns. If the leftmost columns of the index that you specify with the PARTITIONED keyword match the partitioning key, Db2 creates the index as a DPSI only if the collating sequence of the matching columns is different.

The use of DPSIs promotes partition independence and therefore provides the following performance advantages, among others:

  • Eliminates contention between parallel LOAD utility jobs with the PART option that target different partitions of a table space
  • Facilitates partition-level operations such as adding a partition or rotating a partition to be the last partition
  • Improves the recovery time of secondary indexes on partitioned table spaces

However, the use of DPSIs does not always improve the performance of queries. For example, for queries with predicates that reference only the columns in the key of the DPSI, Db2 must probe each partition of the index for values that satisfy the predicate.

DPSIs provide performance advantages for queries that meet all of the following criteria:

  • The query has predicates on the DPSI columns.
  • The query contains additional predicates on the partitioning columns of the table that limit the query to a subset of the partitions in the table.
Nonpartitioned secondary index (NPI or NPSI)
A nonpartitioned secondary index (NPI or NPSI) is any index that is not defined as a partitioning index or a partitioned index. An NPI index has one index space that contains keys for the rows of all partitions of the table space.

You can create an NPI on a table in a partitioned table space. These indexes do not apply to nonpartitioned table spaces.

NPIs provide performance advantages for queries that meet the following criteria:

  • The query does not contain predicates on the partitioning columns of the table that limit the query to a small subset of the partitions in the table.
  • The query qualifications match the index columns.
  • The SELECT list columns are included in the index (for index-only access).

Examples of DPSI and NPI advantages

Begin general-use programming interface information.
To understand the advantages of using DPSIs and NPIs, consider the following example indexes on the AREA_CODES table:
A data partitioned secondary index (DPSI) on the STATE column
Assuming that the AREA_CODES table not partitioned on the STATE column, the following CREATE INDEX statement creates a DPSI on the AREA_CODES table.
CREATE INDEX DPSIIX2 ON AREA_CODES (STATE) PARTITIONED;

The following example query can make efficient use of the example DPSI. The number of key values that need to be searched is limited to just the key values of the qualifying partitions, which are only those with partitioning key values that are less than or equal to 300.

SELECT STATE FROM AREA_CODES
 WHERE AREACODE_NO <= 300 AND STATE = 'CA';
A nonpartitioned index (NPI) on the STATE column
Assuming that the AREA_CODES table not partitioned on the STATE column, the following CREATE INDEX statement creates an NPI on the AREA_CODES table.

CREATE INDEX NPSIIX3 ON AREA_CODES (STATE);

The following query can make efficient use of the example NPI. The number of key values that need to be searched is limited to scanning the index key values that are greater than 'CA'.

SELECT STATE FROM AREA_CODES
 WHERE STATE > 'CA';

The following figure illustrates the structure of the example indexes.

Figure 3. DPSI and NPI on the AREA_CODES table
Begin figure description. Relationship of table to indexes from preceding SQL statements. End figure description.
End general-use programming interface information.

DPSIs provide advantages over NPIs for utility processing. For example, utilities such as COPY, REBUILD INDEX, and RECOVER INDEX can operate on physical partitions rather than logical partitions because the keys for a data partition reside in a single DPSI partition. This method can provide greater availability.