Creating nonpartitioned indexes on partitioned tables

When you create a nonpartitioned index on a partitioned table, you create a single index object that refers to all rows in the table. Nonpartitioned indexes are always created in a single table space, even if the table data partitions span multiple table spaces.

Before you begin

This task assumes that your partitioned table has already been created.

Procedure

Formulate a CREATE INDEX statement for your table, using the NOT PARTITIONED clause.
For example:
```
CREATE INDEX indexName ON tableName(column) NOT PARTITIONED
```
Execute the CREATE INDEX statement from a supported Db2® interface.

Example

Example 1: Creating a nonpartitioned index in the same table space as the data partition.

Assume the SALES table is defined as follows:

   CREATE TABLE sales(store_num INT, sales_date DATE, total_sales DECIMAL (6,2)) IN ts1
      PARTITION BY RANGE(store_num) 
      (STARTING FROM (1) ENDING AT (100), 
       STARTING FROM (101) ENDING AT (150),
       STARTING FROM (151) ENDING AT (200))

The three partitions of the SALES table are stored in table space TS1. By default, any indexes created for this table are also stored in TS1, because that was the table space specified for this table. To create a nonpartitioned index STORENUM on the STORE_NUM column, use the following statement:

     CREATE INDEX StoreNum ON sales(store_num) NOT PARTITIONED

Note that the NOT PARTITIONED clause is required, otherwise the index is created as a partitioned index, the default for partitioned tables.

Example 2: Creating a nonpartitioned index in a table space other than the default

Assume that a table called PARTS is defined as follows:

     CREATE TABLE parts(part_number INT, manufacturer CHAR, description CLOB, 
        price DECIMAL (4,2)) IN ts1 INDEX in ts2
        PARTITION BY RANGE (part_number)
        (STARTING FROM (1) ENDING AT (10) IN ts3,
         STARTING FROM (11) ENDING AT (20) INDEX IN ts1,
         STARTING FROM (21) ENDING AT (30) IN ts2 INDEX IN ts4);

The PARTS table consists of three partitions: the first is in table space TS3, the second is in TS2 and the third in TS3. If you issue the following statement a nonpartitioned index that orders the rows in descending order of manufacturer name is created:

     CREATE INDEX manufct on parts(manufacturer DESC) NOT PARTITIONED IN TS3;

This index is created in table space TS3; the INDEX IN clause of the CREATE TABLE statement is overridden by the IN tablespace clause of the CREATE INDEX statement. Because the table PARTS is partitioned, you must include the NOT PARTITIONED clause in the CREATE INDEX statement to create a nonpartitioned index.