Indexes with partitioned tables

Indexes can be created as partitioned or nonpartitioned. A partitioned index creates an individual index for each partition. A nonpartitioned index is a single index spanning all partitions of the table.

Partitioned indexes allow you to take advantage of improved optimization of queries. If a unique index is partitioned, columns specified in the index must be the same or a superset of the data partition key.

Use the CREATE INDEX statement to create indexes on partitioned tables. To create an index for each partition, use the PARTITIONED clause.

CREATE INDEX PRODLIB.SAMPLEINDEX
ON PRODLIB.PAYROLL(EMPNUM) PARTITIONED

To create a single index that spans all partitions, use the NOT PARTITIONED clause.

CREATE INDEX PRODLIB.SAMPLEINDEX
ON PRODLIB.PAYROLL(EMPNUM) NOT PARTITIONED

You can only create a partitioned Encoded Vector Index (EVI) over a partitioned table. You cannot create a nonpartitioned EVI over a partitioned table.

In the CREATE INDEX statement in the SQL reference topic collection, you can find more information about creating indexes for partitioned tables.

When creating an SQL unique index, unique constraint, or primary key constraint for a partitioned table, the following restrictions apply:

An index can be partitioned if the keys of the unique index are the same or a superset of the partitioned keys.
If a unique index is created with the default value of NOT PARTITIONED, and the keys of the unique index are a superset of the partitioned keys, the unique index is created as partitioned. If, however, the user explicitly specifies NOT PARTITIONED, and the keys of the unique index are a superset of the partitioned keys, the unique index is created as not partitioned.
When attempting to share an existing unique index for a constraint, any existing unique nonpartitioned index is checked before attempting to share a partitioned index. This checking is useful if the user wants to create a uniquely keyed logical file to be used as the parent key of a referential constraint. If the user creates the uniquely keyed logical file and then wanted to add any primary key, or unique constraint, the keyed logical file index would be used as the nonpartitioned index for the constraint. This would be the preferred method when wanting a nonpartitioned primary key of a partitioned table to be the parent key of a referential constraint. For example:
```
CREATE TABLE PRODLIB.PAYROLL (C1 INT)                          
   PARTITION BY HASH(C1) INTO 3 PARTITIONS                     
CREATE UNIQUE INDEX PRODLIB.PINDEX ON PRODLIB.PAYROLL(C1)     
        NOT PARTITIONED                                        
 ADDPFCST FILE(PRODLIB/PAYROLL) TYPE(*PRIKEY) KEY(C1) 
	
```