CREATE INDEX
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
The CREATE INDEX statement creates an index on an existing table.
The CREATE INDEX statement has the following format:
[EXEC SQL] CREATE [UNIQUE] INDEX [schema.]index_name
ON [schema.]tablename
(column_name {, column_name})[UNIQUE]
[WITH with_clause]
To build a set of indexes on the same table in parallel:
[EXEC SQL] CREATE [UNIQUE] INDEX [schema.]index_name
ON [schema.] tablename
(column_name [ASC|DESC]{, column_name...})[UNIQUE]
[WITH with_clause]){, ([schema.]index_name }
The CREATE INDEX statement for X100 tables has the following format:
CREATE INDEX [schema.]index_name ON
[schema.]tablename (column_name {, column_name})
WITH STRUCTURE = X100_IX | X100_SI
For X100, the CREATE INDEX statement creates an index on an existing table. When the WITH STRUCTURE clause is omitted, it creates a primary (clustered) index (X100_IX). When WITH STRUCTURE=X100_SI is used, it creates a secondary index on additional columns.
Notes:
• When using parallel index syntax, concurrent access is not allowed on readonly tables.
• Vertical (column-level) partitioning is not supported for indexes.
• Secondary indexes are not supported for partitioned tables.
index_name
Defines the name of the index. This must be a valid object name.
tablename
Specifies the table on which the index is to be created.
column_name
Specifies a list of columns from the table to be included in the index. If the WITH KEY option is used, the columns specified as keys must head this list and must appear in the same order in which they are specified in WITH KEY. If the structure is RTREE, only one column can be named.
WITH with_clause
Specifies a comma-separated list of any of the following items:
STRUCTURE = structure
Specifies the storage structure of the index, as one of the following values:
BTREE
ISAM
HASH
RTREE
If the structure is RTREE, UNIQUE cannot be specified.
X100_IX
(Default for X100 tables) Creates a primary (clustered) index for an X100 table. Only one primary index per table is allowed.
X100_SI
Creates a secondary index on additional columns in an X100 table. Can also be specified as VWSI.
Note: Secondary indexes are not supported for partitioned tables.
Default: ISAM
KEY = (columnlist)
Specifies the columns on which the index is keyed. If this parameter is not included, the index is keyed on the columns in the index definition. If the structure is rtree, only one column can be named.
FILLFACTOR = n
Specifies the percentage of each primary data page that can be filled with rows.
Limits: 1 to 100 and must be expressed as an integer literal or integer variable.
Default: Default values differ for each storage structure.
MINPAGES = n
Defines the minimum number of primary pages a hash or compressed hash index table must have. The value can be expressed as an integer literal or integer variable.
Default: 16 for a hash table; 1 for a compressed hash table.
MAXPAGES = n
Defines the maximum number of primary pages that a hash or compressed hash index can have. The value can be expressed as an integer literal or integer variable.
Default: No default
LEAFFILL = n
Defines the percentage full each leaf index page is when the index is created. This option can be used when creating an index with a btree or compressed btree structure.
Limits: 1 to 100 and must be an integer literal or integer variable.
NONLEAFFILL = n
Specifies the percentage full each nonleaf index page is when the index is created. This option can be used when creating an index with a btree or compressed btree structure.
Limits: 1 to 100, and must be an integer literal or integer variable.
LOCATION = (location_name {, location_name})
Specifies the areas on which the index is created. Location_name must be a string literal or string variable.
Default: The default area for the database
ALLOCATION = n
Specifies the number of pages initially allocated for the index.
Limits: An integer between 4 and 8,388,607
Default: 4
EXTEND = n
Specifies the number of pages by which the index is extended when more space is required.
Limits: An integer between 1and 8,388,607
Default: 16
COMPRESSION [= ([[NO]KEY] [,[NO|HI]DATA])] | NOCOMPRESSION
Specifies whether the index key and data are to be compressed. If the structure is RTREE, compression cannot be specified.
Default: NOCOMPRESSION
[NO]PERSISTENCE
Specifies whether the modify statement recreates the index when its base table is modified.
Default: nopersistence (indexes are not recreated).
UNIQUE_SCOPE = STATEMENT | ROW
For unique indexes only. Specifies whether rows are checked for uniqueness one-by-one as they are inserted or after the update is complete. If the structure is rtree, unique_scope cannot be specified.
Default: unique_scope = row
PAGE_SIZE = n
Specifies page size.
PRIORITY = cache_priority
Allows tables to be assigned fixed priorities
Limits: An integer between 0 and 8
Note: If II_DECIMAL is set to comma, you must follow any comma required in SQL syntax (such as a list of table columns or SQL functions with several parameters) by a space. For example:
SELECT col1, IFNULL(col2, 0), LEFT(col4, 22) FROM version;