CREATE INDEX statement
The CREATE INDEX statement defines a new index for a table.
CREATE INDEX indexOwner.indexName | This option specifies the fully qualified name of the new index. | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
SQLID SQLID | This option specifies the current SQLID. | ||||||||||
TABLE tableOwner.tableName | This option specifies the table on which the index is being created. A global temporary table cannot be referenced in a CREATE INDEX statement. | ||||||||||
KEYCOLUMNS (columnName order, ...) | This option specifies the column list that defines the key for this index. order values for the KEYCOLUMNS parameter
| ||||||||||
KEYTEXT (expression) | This option specifies the parts of the XML documents in an XML column that are indexed (XMLPATTERN). | ||||||||||
XMLdataType | For an XML index, This option specifies the SQL data type. The XMLdataType can be one of the following types:
| ||||||||||
PERIOD BUSINESS_TIME | This option indicates whether the BUSINESS_TIME values are unique. The starting and ending columns of BUSINESS_TIME are defined in the table on which the index is created. PERIOD BUSINESS_TIME parameter values
| ||||||||||
UNIQUE | This option indicates whether index values must be unique. UNIQUE parameter values
| ||||||||||
DSSIZE integer G | This option specifies the maximum size for each index partition in gigabytes. | ||||||||||
AUX | This option indicates whether the index is an auxiliary index for a large object (LOB) column in an auxiliary table. AUX parameter values
| ||||||||||
COMMENT 'string' | This option specifies the comment for the index. | ||||||||||
PART 0 indexPartitionParameters | This option specifies a nonpartitioned index according to indexPartitionParameters (see indexPartitionParameters). | ||||||||||
NUMPARTS number (PART number indexPartitionParameters) | Defines the number of partitions for the index. For a partitioned index, NUMPARTS defines the parameters for PART number according to indexPartitionParameters (see indexPartitionParameters). | ||||||||||
indexOptions | Provides additional options for indexes (see indexOptions). |
indexPartitionParameters
indexPartitionParameters includes the following parameters.
VCAT catalogName | This option specifies the volume catalog to be used for the index. | ||||||||
---|---|---|---|---|---|---|---|---|---|
STOGROUP | This option specifies the new default storage group for the index. STOGROUP parameter values
| ||||||||
PRIQTY number | This option specifies the primary space that is allocated for the Db2 defined data set. | ||||||||
SECQTY number | This option specifies the secondary space that is allocated for the Db2 defined data set. | ||||||||
ERASE | This option indicates whether to erase the data sets when they are deleted by a statement that drops the index. ERASE parameter values
| ||||||||
FREEPAGE number | This option specifies that a free page is added after every number of pages upon index creation or reorganization. | ||||||||
PCTFREE number | This option specifies the percentage of free space left on each page of the index upon creation or reorganization. | ||||||||
GBPCACHE | This option specifies which index pages are cached to the group buffer pool. GBPCACHE parameter values
| ||||||||
DSSIZE integer G | This option specifies the maximum size for the index partition in gigabytes. | ||||||||
VALUES (constant, ...) | This option specifies the replacement key limit list for this part of a partitioned index. |
indexOptions
indexOptions includes the following parameters.
CLUSTER | This option indicates whether the index is defined as a clustering index. CLUSTER parameter values
| ||||||||
---|---|---|---|---|---|---|---|---|---|
PADDED | This option indicates how columns that are defined as VARCHAR are stored in the index. PADDED parameter values
| ||||||||
COMPRESS | This option indicates whether index data is compressed. COMPRESS parameter values
| ||||||||
DEFINE | This option specifies when the data sets for the index are created. DEFINE parameter values
| ||||||||
PARTITIONED | This option indicates whether the index is partitioned. PARTITIONED parameter values
| ||||||||
BUFFERPOOL | This option specifies the new default buffer pool for the index. BUFFERPOOL parameter values
| ||||||||
CLOSE | This option indicates whether the index can be closed when the index is not being used. CLOSE parameter values
| ||||||||
PIECESIZE integer keyword | This option specifies the maximum PIECESIZE for a nonpartitioned index. The default PIECESIZE is 2 G (2 GB) for indexes backed by non-LARGE table spaces and 4 G (4 GB) for indexes backed by LARGE table spaces. The subsequent keyword (K, G, or M) indicates the value of the specific integer. PIECESIZE keyword values
| ||||||||
COPY | This option indicates whether to use a utility to create an image copy of the index. COPY parameter values
|
Related topic