DB2 Version 9.7 for Linux, UNIX, and Windows

Partitioned indexes on partitioned tables

A partitioned index is made up of a set of index partitions, each of which contains the index entries for a single data partition. Each index partition contains references only to data in its corresponding data partition. Both system- and user-generated indexes can be partitioned.

A partitioned index becomes particularly beneficial if:

You are rolling data in or out of partitioned tables using the ATTACH PARTITION or DETACH PARTITION clauses of the ALTER TABLE statement. With a nonpartitioned index, the SET INTEGRITY statement that you must run before the data in the newly-attached partition is available can be time-consuming and require large amounts of log space. When you attach a table partition that uses a partitioned index, you still must issue a SET INTEGRITY statement to perform tasks such as range validation and constraint checking. However, if the indexes for the source table match the index partitions for the target table, SET INTEGRITY processing does not incur the performance and logging overhead associated with index maintenance; the newly rolled-in data is accessible more quickly than it would be using nonpartitioned indexes.
You are performing maintenance on data in a specific partition that necessitates an index reorganization. For example, consider a table with 12 partitions, each corresponding to a specific month of the year. You might have a need to update or delete many rows that are specific to one month of the year. This could result in the index becoming fragmented, which might require that you perform an index reorganization. With a partitioned index, you can reorganize just the index partition that corresponds to the data partition where the changes were made, which could save a significant amount of time compared to reorganizing an entire, nonpartitioned index.

There are some types of indexes that cannot be partitioned:

Indexes over nonpartitioned data
Indexes over spatial data
XML column path indexes (system generated)

You must always create these indexes as nonpartitioned. In addition, the index key for partitioned unique indexes must include all columns from the table-partitioning key, whether they are user- or system-generated. The latter would be the case for indexes created by the system for enforcing unique or primary constraints on data.

Figure 1 shows an example of partitioned indexes.

Figure 1. Partitioned indexes that share a table space with data partitions of a table

Illustration of partitioned indexes that share a table space with the data partitions of a table.

In this example, all of the data partitions for table A and all of the index partitions for table A are in a single table space. The index partitions reference only the rows in the data partition with which they are associated. (Contrast a partitioned index with a nonpartitioned index, where the index references all rows across all data partitions). Also, index partitions for a given data partition are in the same index object. This particular arrangement of indexes and index partitions would have been established with statements like the following:

CREATE TABLE A (columns) in ts1 
      PARTITION BY RANGE (column expression)
      (PARTITION PART0 STARTING FROM constant ENDING constant,
       PARTITION PART1 STARTING FROM constant ENDING constant, 
       PARTITION PART2 STARTING FROM constant ENDING constant,
     
   CREATE INDEX x1 ON A (...) PARTITIONED; 
   CREATE INDEX x2 ON A (...) PARTITIONED;

Figure 2 shows another example of a partitioned index.

Figure 2. Partitioned indexes with data partitions and index partitions in different table spaces.

Illustration of partitioned indexes with data partitions and index partitions in different table spaces.

In this example, the data partitions for table A are distributed across two table spaces, TS1, and TS3. The index partitions are also in different table spaces. The index partitions reference only the rows in the data partition with which they are associated. This particular arrangement of indexes and index partitions would have been established with statements like the following:

CREATE TABLE A (columns) 
      PARTITION BY RANGE (column expression)
      (PARTITION PART0 STARTING FROM constant ENDING constant IN ts1 INDEX IN ts2,
       PARTITION PART1 STARTING FROM constant ENDING constant IN ts3 INDEX IN ts4, 
       PARTITION PART2 STARTING FROM constant ENDING constant IN ts3,INDEX IN ts5)
     
   CREATE INDEX x1 ON A (...); 
   CREATE INDEX x2 ON A (...);

Note that in this case, the PARTITIONED clause has been omitted from the CREATE INDEX statement; the indexes will still be created as partitioned indexes, as this is the default for partitioned tables.

Figure 3 shows an example of a partitioned table with both nonpartitioned and partitioned indexes.

Figure 3. Combination of nonpartitioned and partitioned indexes for a partitioned table.

Illustration of index partitions in various table spaces.

In this diagram, index X1 is a nonpartitioned index that references all of the partitions of table T1. Indexes X2 and X3 are partitioned indexes that reside in various table spaces. This particular arrangement of indexes and index partitions would have been established with statements like the following:

   CREATE TABLE t1 (columns) in ts1 INDEX IN ts2  1 
      PARTITION BY RANGE (column expression)
      (PARTITION PART0 STARTING FROM constant ENDING constant  IN ts3,  2 
       PARTITION PART1 STARTING FROM constant ENDING constant  INDEX IN ts5, 
       PARTITION PART2 STARTING FROM constant ENDING constant  INDEX IN ts4,
       PARTITION PART3 STARTING FROM constant ENDING constant  INDEX IN ts4,
       PARTITION PART4 STARTING FROM constant ENDING constant)

   CREATE INDEX x1 ON t1 (...) NOT PARTITIONED; 
   CREATE INDEX x2 ON t1 (...) PARTITIONED;
   CREATE INDEX x3 ON t1 (...) PARTITIONED;

Note that:

The nonpartitioned index X1 is stored in table space TS2, because this is the default specified (see 1 ) for nonpartitioned indexes for table T1.
The index partition for data partition 0 (Part0) is stored in table space TS3, because the default location for an index partition is the same as the data partition it references (see 2 ).
Part4 is stored in TS1, which is the default table space for data partitions in table T1 (see 1 ); the index partitions for this data partition also reside in TS1, again because the default location for an index partition is the same as the data partition it references.

Important: Unlike nonpartitioned indexes, with partitioned indexes you cannot use the INDEX IN clause of the CREATE INDEX statement to specify the table space in which to store index partitions. The only way to override the default storage location for index partitions is to specify the location at the time you create the table, using the partition-level INDEX IN clause of the CREATE TABLE statement. The table-level INDEX IN clause has no effect on index partition placement.

You create partitioned indexes for a partitioned table by including the PARTITIONED option in a CREATE INDEX statement. For example, for a table named SALES partitioned with sales_date as the table-partitioning key, to create a partitioned index, you could use a statement like this:

   CREATE INDEX partIDbydate on SALES (sales_date, partID) PARTITIONED

If you are creating a partitioned unique index, then the table partitioning columns must be included in the index key columns. So, using the previous example, if you tried to create a partitioned index with the following statement:

   CREATE UNIQUE INDEX uPartID on SALES (partID) PARTITIONED

the statement would fail because the column sales_date, which forms the table-partitioning key is not included in the index key.

If you omit the PARTITIONED keyword when you create an index on a partitioned table, the database manager will create a partitioned index by default unless:

You are creating a unique index, and the index key does not include all of the table-partitioning keys
You are creating one of the types of indexes that are described at the beginning of this topic as not able to be created as partitioned indexes.

In either of these cases, the index will be created as a nonpartitioned index.

Whereas creating a nonpartitioned index with a definition that matches that of an existing nonpartitioned index will result in the SQL0605W error, a partitioned index can coexist with a nonpartitioned index with a similar definition. This is intended to allow for easier adoption of partitioned indexes.