You can use the CREATE INDEX statement to create partitioned indexes. The following types of indexes are available for partitioned tables:

  • Local index
  • Global index

These indexes are selected based on the keys on which the index is defined.

Local Index

A local index must include the partition key column as the leading prefix of its index keys. If there are n partition key columns, then the first n index key components must be the partition key columns. This is also known as a partition aligned index.

Use the following syntax to specify area definitions for each index partition:

Syntax

CREATE [ UNIQUE ] INDEX index_name ON table_name...
  [ FOR PARTITION partition_name USING INDEX AREA area_name]...

Global Index

A global index is a single database object, which contains index entries for all rows, in all partitions, in a partitioned table. For a partitioned table, all indexes that are not partition aligned are global indexes. A partition aligned index can be a local index or a global index. By default, all partition aligned indexes are local indexes. To create global partition aligned indexes, use the GLOBAL reserved keyword, as shown below:

Syntax

CREATE [ GLOBAL ][ UNIQUE ] INDEX index_name 
  ON table_name...
    [ AREA area_name]...

Notes

  • The following semantics apply when using the CREATE INDEX statement for partitioned tables:
    • The partition specified in the FOR PARTITION clause must be an existing partition in the table
    • Partitions can be specified in any order
    • The specified area must be present in the table and must be in the type II area
    • The table specified in the statements must be a partitioned table
  • The following semantics are applicable to local indexes:
    • The existing AREA clause cannot be used for local indexes.
    • The FOR PARTITION clause is only valid for a partitioned table and a local index.
    • Only index areas can be specified in the FOR PARTITION clause in the above syntax.
    • The specified area must be a type II area.
    • The first real local index created for a partitioned table without a partition schema definition is marked as the default index.
    • The initial composite partition of a partitioned table without a partition schema definition is created with NO SPACE for local indexes.
  • The following semantics are applicable to global indexes:
    • If an AREA clause is not specified, the index is created in the default index area of a table partition. A global index can use the area defined in an AREA clause for data in all partitions.
    • An index created using an unpartitioned column as the prefix of index keys is treated as a global index.
    • If an AREA clause is not specified, the index is created in the default index area of a table partition.
    • A global index can use an existing AREA clause.
    • The FOR PARTITION clause cannot be used for creating global indexes.
    • The GLOBAL clause must be used only for partitioned tables. If it is used to create indexes on any other tables (like shared tables or multi-tenant tables), the SQL engine returns an error.
    • The GLOBAL clause can also be used to create a global index for a partitioned table without partition schema definition. The AREA clause must be specified for a global index of a partitioned table without partition schema definition and this area must belong to the type II area.

Examples

Creating local indexes

The following example illustrates creating local indexes for the partitioned table Pub.tpcustomer with partitions:
CREATE INDEX custid_localidx on Pub.tpcustomer (custid,salary)
FOR PARTITION P1 USING INDEX AREA "custidxp1"
FOR PARTITION P2 USING INDEX AREA "custidxp2"
FOR PARTITION P3 USING INDEX AREA "custidxp3";

Creating local indexes on a table with partition-specific index areas

The following example creates a local index on the Pub.tpcustomer table with partition specific index areas for partitions partn1 and partn9.
CREATE INDEX cust_localidx on Pub.tpcustomer ( custid )
FOR PARTITION partn1 USING INDEX AREA "idx1 part1 area"
FOR PARTITION partn9 USING INDEX AREA "idx1 part2 area";

Creating global indexes

The following example illustrates creating a global index using the AREA clause: 
CREATE INDEX custname_globallidx on Pub.tpcustomer (custname) 
 AREA "custidx";

Creating global indexes on a table with a partitioning key column

The following example illustrates creating a global index, where custid is the partitioning key for the table Pub.tpcustomer: 
CREATE GLOBAL INDEX custname_globalidx2 on Pub.tpcustomer (custid) 
 AREA "custidx";

Creating a unique global index

The following example illustrates creating a unique global index, where orderid is the partitioning key for the table Pub.tporderid:
CREATE GLOBAL UNIQUE INDEX custname_globalidx3 on Pub.tporderid (orderid)
 AREA "custidx";