Creating partitioned indexes

When you create a partitioned index for a partitioned table, each data partition is indexed in its own index partition. By default, the index partition is stored in same table space as the data partition it indexes. Data in the indexes is distributed based on the distribution key of the table.

Before you begin

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

About this task

Restrictions

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.

Also, The IN clause of the CREATE INDEX statement is not supported for creating partitioned indexes. By default, index partitions are created in the same table space as the data partitions they index. To specify an alternative table space in which to store the index partition, you must use the partition-level INDEX IN clause of the CREATE TABLE statement to specify a table space for indexes on a partition-by-partition basis. If you omit this clause, the index partitions will reside in the same table space as the data partitions they index.

Procedure

Formulate a CREATE INDEX statement for your table, using the PARTITIONED clause.
Execute the CREATE INDEX statement from a supported Db2® interface.

Example

Note: These examples are for illustrative purposes only, and do not reflect best practices for creating partitioned tables or indexes.

Example 1: Creating a partitioned index in the same table spaces as the data partition.

In this example, assume the SALES table is has been 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))

In this case, the three partitions of the table SALES are stored in table space ts1. Any partitioned indexes created for this table will also be stored in ts1, because that is the table space in which each partition for this table will be stored. To create a partitioned index on the store number, use the following statement:

     CREATE INDEX StoreNum ON sales(store_num) PARTITIONED

Example 2: Choosing an alternative location for all index partitions.

In this example, assume the EMPLOYEE table is has been defined as follows:

     CREATE TABLE employee(employee_number INT, employee_name CHAR, 
                  job_code INT, city CHAR, salary DECIMAL (6,2)) 
                  IN ts1 INDEX IN ts2
        PARTITION BY RANGE (job_code)
        (STARTING FROM (1) ENDING AT (10) INDEX IN ts2,
         STARTING FROM (11) ENDING AT (20) INDEX IN ts2,
         STARTING FROM (21) ENDING AT (30) INDEX IN ts2)

To create a partitioned index on the job codes, use the following statement:

     CREATE INDEX JobCode ON employee(job_code) PARTITIONED

In this example, the partitions of the EMPLOYEE table are stored in table space ts1, however, all index partitions will be stored in ts2.

Example 3: Indexes created in several partitions.

Assume a table called PARTS has been 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);

In this case, the PARTS table consists of three partitions: the first is in table space ts3, the second in ts1 and the 3rd in ts2. If the following statements are issued:

     CREATE INDEX partNoasc ON parts(part_number ASC)  PARTITIONED
     CREATE INDEX manufct on parts(manufacturer DESC) NOT PARTITIONED IN TS3;

then two indexes are created. The first is a partitioned index to order the rows in ascending order of part number. The first index partition is created in table space ts3, the second in ts1 and the third in ts4. The second index is a nonpartitioned index which orders the rows in descending order of the manufacturer's name. This index is created in ts3. Note that the IN clause is allowed in CREATE INDEX statements for nonpartitioned indexes. Also, in this case, because the table PARTS is partitioned, to create a nonpartitioned index, the clause NOT PARTITIONED must be included in the CREATE INDEX statement.