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.
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";
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.
