Parameters--Create Index Statement
This statement has the following parameters:
index_name
Defines the name of the index. This must be a valid object name.
table_name
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 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 the key option. If the structure is rtree, only one column can be named.
structure = BTREE | ISAM | HASH | RTREE
Specifies the storage structure of the index. Defaults to isam if the parameter is not included. If the structure is rtree, unique cannot be specified.
Default: ISAM
with with_clause
Specifies a comma-separated list of any of the following items:
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 | 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, be sure that when SQL syntax requires a comma (such as a list of table columns or SQL functions with several parameters), that the comma is followed by a space. For example:
select col1, ifnull(col2, 0), left(col4, 22) from t1: