Nonpartitioned indexes on partitioned tables

A nonpartitioned index is a single index object that refers to all rows in a partitioned table. Nonpartitioned indexes are always created as independent index objects in a single table space, even if the table data partitions span multiple table spaces.

When you create an index for a partitioned table, the index is a partitioned index by default unless you create one of the following types of indexes:
  • A unique index where the index key does not include all of the table-partitioning columns
  • A spatial index
In these cases, the index that you create is nonpartitioned. However, there are times when it is useful or necessary to create a nonpartitioned index even though your data is partitioned. In these cases, use the NOT PARTITIONED clause of the CREATE INDEX statement to create a nonpartitioned index on a partitioned table. When you create a nonpartitioned index, by default, it is stored in the same table space as the first visible or attached data partition. Figure 1 shows an example of a single index, X1, that references all of the partitions in a table. The index was created in the same table space as the first visible partition for the table.
Figure 1. Nonpartitioned index on a partitioned table
Illustration of a nonpartitioned index on a partitioned table
Figure 2 shows an example of two nonpartitioned indexes. In this case, each index partition is in a table space separate from the table space of the data partitions. Note again how each index references all of the partitions in the table.
Figure 2. Nonpartitioned indexes on a partitioned table, with indexes in their own table spaces
Illustration of two nonpartitioned indexes on a partitioned table with each index in its own table space
You can override the location for a nonpartitioned index at the following times:
  • When you create the table, by using the INDEX IN clause of the CREATE TABLE statement
  • When you create the index, by using the IN clause of the CREATE INDEX statement.
The second approach always takes precedence over the first.

If you roll data into a partitioned table by using the ATTACH PARTITION clause of the ALTER TABLE statement, you must run the SET INTEGRITY statement to bring the attached partition data online for queries. If the indexes are nonpartitioned, bringing the attached partition data online can be a time-consuming operation that uses considerable amounts of log space, because SET INTEGRITY must insert data from the newly attached partition into the nonpartitioned indexes.

SET INTEGRITY is not required to be run after detaching a partition.