CREATE INDEX

The CREATE INDEX statement creates an index on one or more columns of a table. You can specify an index in ascending order (ASC) or descending order (DESC).

A DBA must use the FOR TENANT phrase to over-ride the default index storage areas and allocate new database storage areas for the index. A multi-tenant index is generated for a multi-tenant table.

Syntax

The CREATE INDEX statement uses the following syntax:

CREATE [ UNIQUE ] INDEX index_nameON table_name({column_name
 [ ASC | DESC ]}[, ...])
 [ AREA area_name]
 [ ENCRYPT WITH cipher]
 [BUFFER_POOL{PRIMARY|ALTERNATE}]
 [ PRO_DESCRIPTION value | PRO_ACTIVE {'N'|'n'} ];
 [ FOR TENANT {tenant_name_1|DEFAULT}
      USING INDEX AREA area_name | USING NO SPACE ]...
 [ FOR PARTITION partition_name USING INDEX AREA area_name]...;

Syntax to create a global index

The following syntax is used to create a global index:

CREATE [ GLOBAL ][ UNIQUE ] INDEX index_nameON table_name...[ AREA area_name]...

Example: CREATE INDEX statement

The index in this CREATE INDEX example is specified on the single column empno, and is of ascending order on the value of the column, as shown in the following example.

 CREATE INDEX idx_emp ON SPORTS.employee (empno ASC) ;

Example: Creating a multi-tenant index

The following example shows how to define a multi-tenant index:

CREATE PRO_WORD INDEX CommentsWordIdx ON mtcustomer (cust_num)
        	  FOR TENANT DEFAULT USING INDEX AREA "Misc_Index_Area"
;

Example: Creating local indexes for a partitioned table

The following example shows how to create 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"

Example: Creating a global index for a partitioned table

The following example shows how to create local indexes for the partitioned table Pub.tpcustomer with partitions:

CREATE INDEX custname_globallidx on Pub.tpcustomer (custname) AREA "custidx"

For details on using the CREATE INDEX statement to enable transparent data encryption, see Learn about Security and Auditing.

For details on using the CREATE INDEX statement to designate objects for buffer pool assignments, including an alternate buffer pool, see Manage OpenEdge Databases.