Indexes on partitioned tables operate similarly to indexes on nonpartitioned tables, however they are stored using a different storage model, depending on whether they are partitioned or nonpartitioned indexes.
Whereas the indexes for a regular nonpartitioned table all reside in a shared index object, a nonpartitioned index on a partitioned table is created in its own index object in a single table space, even if the data partitions span multiple table spaces. Both database managed space (DMS) and system managed space (SMS) table spaces support the use of indexes in a different location than the table data. Each nonpartitioned index can be placed in its own table space, including large table spaces. Each index table space must use the same storage mechanism as the data partitions, either DMS or SMS. Indexes in large table spaces can contain up to 229 pages. All of the table spaces must be in the same database partition group.
A partitioned index uses an index organization scheme in which index data is divided across multiple index partitions, according to the partitioning scheme of the table. Each index partition only refers to table rows in the corresponding data partition. All index partitions for a given data partition reside in the same index object.
Starting in DB2® Version 9.7 Fix Pack 1, user-created indexes over XML data on XML columns in partitioned tables can be either partitioned or nonpartitioned. The default is partitioned. System-generated XML region indexes are always partitioned, and system-generated column path indexes are always nonpartitioned. In DB2 V9.7, indexes over XML data are nonpartitioned.
Although a nonpartitioned index always preserves order on the index columns, a partitioned index might lose some order across partitions in certain scenarios; for example, if the partitioning columns do not match the index columns, and more than one partition is to be accessed.
During online index creation, concurrent read and write access to the table is permitted. After such an index has been built, changes that were made to the table during index creation are applied to the new index. Write access to the table is blocked until index creation completes and the transaction commits. In the case of partitioned indexes, each data partition is quiesced to read-only access only while changes that were made to that data partition (during the creation of the index partition) are applied.
Partitioned index support becomes particularly beneficial when you are rolling data in using the ALTER TABLE...ATTACH PARTITION statement. If nonpartitioned indexes exist (not including the XML columns path index, if the table has XML data), issue a SET INTEGRITY statement after partition attachment. This is necessary for nonpartitioned index maintenance, range validation, constraints checking, and materialized query table (MQT) maintenance. Nonpartitioned index maintenance can be time-consuming and require large amounts of log space. Use partitioned indexes to avoid this maintenance cost.
The nonpartitioned index X1 refers to rows in all of the data partitions. By contrast, the partitioned indexes X2 and X3 only refer to rows in the data partition with which they are associated. Table space TS3 also shows the index partitions sharing the table space of the data partitions with which they are associated. This is the default for partitioned indexes.
Case 1:
When an index table space is specified in the CREATE INDEX...IN
tbspace statement, use the specified table space for this index.
Case 2:
When an index table space is specified in the CREATE TABLE...
INDEX IN tbspace statement, use the specified
table space for this index.
Case 3:
When no table space is specified, choose the table space that is used
by the first attached or visible data partition.
create unique index a_idx on sales (a)
Because the table SALES is partitioned, index a_idx will also be created as a partitioned index.
create index b_idx on sales (b)
create table z (a int, b int)
partition by range (a) (starting from (1)
ending at (100) index in ts3)
create index c_idx on z (a) partitioned