Creates an index on the specified table using the specified columns of the table. An index improves the performance of SQL operations whose predicates are based on the indexed column. However, an index slows performance of INSERT, DELETE, and UPDATE operations.

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

CREATE [ UNIQUE ] INDEX index_name
 ON 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 ]...;

Parameters

UNIQUE

Does not allow the table to contain rows with duplicate column values for the set of columns specified for that index.

index_name

Must be unique for the given table.

table_name

The name of the table on which the index is built.

column_name [ , ...]

The columns on which search and retrieval is ordered. These columns are called the index key. When more than one column is specified in the CREATE INDEX statement, a concatenated index is created.

ASC | DESC

Allows the index to be ordered as either ascending (ASC) or descending (DESC) on each column of the concatenated index. The default is ASC.

AREA area_name

The name of the storage area where the index and its entries are stored. The name of the index area must be specified within double quotes. If the specified area does not exist, the database returns an error.

If you do not specify the index area, then the indexes are created in the default index area. You can configure the default index area using following command.
proutil db-name -C setobjectdefaultarea 
“Customer index Area”  index
ENCRYPT WITH cipher

Allows the index to be encrypted by designating an appropriate cipher.

BUFFER_POOL {PRIMARY|ALTERNATE}

Allows the index to be assigned to a primary or alternate buffer pool.

PRO_DESCRIPTION value

Allows you to enter an ABL description. value is an arbitrary character string.

PRO_ACTIVE {'N'|'n'}

Indicates the creation of an inactive index. Inactive indexes can be created for an online database.

FOR TENANT tenant_name

Specifies the name of a tenant. The FOR TENANT phrase can be repeated for as many existing tenants as required.

The FOR phrase cannot be used, if it is not a multi-tenant table.

USING INDEX AREA area_ name

Is used to over-ride the default storage areas associated with the particular tenant. This area is used for database space allocation during the CREATE INDEX statement execution. The INDEX AREA specification is optional.

If this phrase is not specified, the index will be allocated in the default index area for the particular tenant.

Note: The area name is ignored for a multi-tenant index.
USING NO SPACE

Indicates that no space is allocated for index data storage for the table. This phrase is valid only if the tenant partition for the table is allocated NO SPACE.

Note: For a particular tenant, the table and the Index partitions must have the same allocation state.

Notes

  • The first index you create on a table should be the fundamental key of the table. This index cannot be dropped except by dropping the table.
  • An index slows down the performance of INSERT, DELETE, and UPDATE operations.
  • Use PROUTIL to activate indexes.
  • Use CREATE INDEX without the PRO_ACTIVE {'N'|'n'}attribute to create an inactive index. Active indexes can be created for an online database if the following conditions are met:
    • Run CREATE INDEX immediately after creating a table.
    • The index is created on the table.
    • Both the CREATE TABLE and CREATE INDEX are performed within the same transaction (no commit is performed after CREATE TABLE is run).

Examples

Creating a unique index

The following example illustrates how to create a unique index on a table:

CREATE UNIQUE INDEX custindex ON customer ( cust_name );

Creating an inactive word index

The following example shows how to create an inactive word index with the specified description field:

CREATE PRO_WORD INDEX CommentsWordIdx ON pub.customer ( cust_name )
     PRO_DESCRIPTION 'Word index on customer name field'
     PRO_ACTIVE 'n';

Creating a multi-tenant index

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

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