CREATE INDEX Format 1 (Define index)
Function
CREATE INDEX (format 1) defines an index for one or more columns of a base table.
Privileges
A user can define indexes in public user RDAREAs for tables owned by that user.
A user can define indexes for tables owned by that user in private user RDAREAs for which the user has the usage privilege.
Format 1 (Define Index)
CREATE [UNIQUE] INDEX [authorization-identifier.]index-Identifier
ON [authorization-identifier.]table-identifier(column-name [{ASC|DESC}]
[, column-name [{ASC|DESC}]]...)
[IN {RDAREA-name
|(RDAREA-name|
|((RDAREA-name)[, (RDAREA- name)]...)
|matrix-partitioned-index-storage-RDAREA-specification}]
[index-option]...
matrix-partitioned-index-storage-RDAREA-specification::=
two-dimensional-storage-RDAREA-specification
two-dimensional-storage-RDAREA-specification::=
(matrix-partitioning-RDAREA-list
[, matrix-partitioning-RDAREA-list]...)
matrix-partitioning-RDAREA-list::=
(RDAREA-name[, RDAREA-name]...)
>index-option ::={PCTFREE =unused-space-percentage
|UNBALANCED SPLIT
|EMPTY
|exception-value-specification}
exception-value-specification ::=EXCEPT VALUES (NULL[, NULL]...)
Operands
Specifies that the key values (all the values in the column or columns defined for the index) must be different (the value in each row must be unique).
If duplicate key values are detected during creation or updating of an index that has the UNIQUE attribute, HiRDB returns an error. However, a null value may be duplicated.
When the UNIQUE option is specified, the considerations discussed below should be noted.
The following table indicates the specifiability of UNIQUE in conjunction with row-partitioning.
Table 3-22 Specifiability of UNIQUE in conjunction with row-partitioning of a table
Table partitioning method#1 | Index constituent column for which UNIQUE is specified#2 | Index partitioning method#4 | UNIQUE specifi- ability | ||
---|---|---|---|---|---|
Row-partitioning within a server | Key range partitioning (not matrix partitioning) and FIX hash partitioning | Partitioning key index | Matches the number of table partitions. | Y | |
Does not match the number of table partitions. | -- | ||||
Non-partitioning key index | Including partitioning keys (in any order) | Matches the number of table partitions. | Y | ||
Does not match the number of table partitions (no row-partitioning). | Y | ||||
Not including partitioning keys (in any order) | Matches the number of table partitions. | N | |||
Does not match the number of table partitions (no row-partitioning). | Y#3 | ||||
Flexible hash partitioning | Not applicable | Not applicable | N | ||
Matrix partitioning | Partitioning key index | Matches the number of table partitions. | Y | ||
Does not match the number of table partitions. | -- | ||||
Non-partitioning key index | Including all partitioning keys (in any order) | Matches the number of table partitions. | Y | ||
Does not match the number of table partitions. | N | ||||
Not including any partitioning key (in any order) | Matches the number of table partitions. | N | |||
Does not match the number of table partitions (no row-partitioning). | -- | ||||
Row-partitioning among servers (no partitioning within a server) | Key range partitioning (not matrix partitioning) and FIX hash partitioning | Partitioning key index | Matches the number of table partitions. | Y | |
Does not match the number of table partitions. | -- | ||||
Non-partitioning key index | Including all partitioning keys (in any order) | Matches the number of table partitions. | Y | ||
Does not match the number of table partitions. | -- | ||||
Not including any partitioning key (in any order) | Matches the number of table partitions. | N | |||
Does not match the number of table partitions. | -- | ||||
Flexible hash partitioning | Not applicable | Not applicable | N | ||
Matrix partitioning | Partitioning key index | Matches the number of table partitions. | Y | ||
Does not match the number of table partitions. | -- | ||||
Non-partitioning key index | Including all partitioning keys (in any order) | Matches the number of table partitions. | Y | ||
Does not match the number of table partitions. | -- | ||||
Not including any partitioning key (in any order) | Matches the number of table partitions. | N | |||
Does not match the number of table partitions. | -- | ||||
Row-partitioning among servers (with partitioning within a server) | Key range partitioning (not matrix partitioning) and FIX hash partitioning | Partitioning key index | Matches the number of table partitions. | Y | |
Does not match the number of table partitions. | -- | ||||
Non-partitioning key index | Including all partitioning keys (in any order) | Matches the number of table partitions (also partitioned on the server) | Y | ||
Does not match the number of table partitions (not partitioned on the server) | Y | ||||
Not including any partitioning key (in any order) | Matches the number of table partitions (also partitioned on the server) | N | |||
Does not match the number of table partitions (not partitioned on the server) | N | ||||
Flexible hash partitioning | Not applicable | Not applicable | N | ||
Matrix partitioning | Partitioning key index | Matches the number of table partitions. | Y | ||
Does not match the number of table partitions. | -- | ||||
Non-partitioning key index | Including all partitioning keys (in any order) | Matches the number of table partitions. | Y | ||
Does not match the number of table partitions. | -- | ||||
Not including any partitioning key (in any order) | Matches the number of table partitions. | N | |||
Does not match the number of table partitions. | -- |
A repetition column cannot be specified as a constituent column of an index for which UNIQUE is specified.
Specifies the authorization identifier of the user who will own the created index and a name for the index.
The index name cannot be the same as the table name.
Specifies the authorization identifier of the user who owns the table that is to be indexed and the name of the base table for which the index is being created.
Specifies the names of the RDAREAs in which the index is to be stored.
Specify matrix-partitioned-index-storage-RDAREA-specification when defining an index for a matrix-partitioned table. RDAREA names are subject to the same rules as non-matrix-partitioned tables.
The following rules apply to RDAREAs that store indexes:
Specifies the percentage of unused space to be left in each index page when the index is created. The range of specifiable values is 0 to 99, and the default is 30.
When created in batch by the database load utility and the database reorganization utility, indexes are created in a percentage equal to the percentage of unused space. In other addition or update operations by INSERT or UPDATE statements, the default PCTFREE=0 is assumed.
If rows will be added frequently after the index has been created, a high percentage of unused space should be specified.
Specifies that the key values are to be allocated unevenly among the pages when a page is split.
If the location where a key value is to be inserted is in the first half of the page to be split, more empty space is allocated to the left-side page after the split. If the key value insertion location is in the second half of the page, more empty space is allocated to the right-side page after the split. This is called an unbalanced index split.
For details of unbalanced index splits, see the HiRDB Version 9 System Operation Guide.
Specifies that an unfinished index is to be created.
The EMPTY option improves the capacity for concurrent execution of index definitions. This option is also effective when a table contains a large amount of data and the definitions of multiple indexes must be executed concurrently. The option is not effective for tables that do not contain data.
For details of using the EMPTY option, see the HiRDB Version 9 System Operation Guide.
Specifies that when the index is created, key values that are composed solely of null values are to be excluded.
The exception values option cannot be specified for an index that contains NOT NULL constraint columns. When the exception values option is specified for an index, indexes cannot be unloaded in index order by the database reorganization utility.
Constituent columns of an index for which the exception values option is specified cannot include repetition columns.
Common rules
Table 3-23 Lengths of multicolumn index columns
Data type | When the combined length of the columns does not exceed 255 bytes | When the combined length of the columns exceeds 255 bytes | ||||
---|---|---|---|---|---|---|
Columns comprising a single column index | Columns comprising a multicolumn index | Columns comprising a single column index | Columns comprising a multicolumn index | |||
Fixed length columns only | Variable length columns are also included | Fixed length columns only | Variable length columns are also included | |||
INTEGER | 4 | 5 | 6 | -- | 5 | 7 |
SMALLINT | 2 | 3 | 4 | -- | 3 | 5 |
DECIMAL[(m[,n])] | ![]() ![]() ![]() | ![]() ![]() ![]() | ![]() ![]() ![]() | -- | ![]() ![]() ![]() | ![]() ![]() ![]() |
FLOAT | 8 | -- | -- | -- | -- | -- |
SMALLFLT | 4 | -- | -- | -- | -- | -- |
CHAR(n), MCHAR(n) | n1 | n1 + 1 | n1 + 2 | n1 | n1 + 1 | n1 + 3 |
NCHAR(n) | 2 x n2 | 2 x n2 + 1 | 2 x n2 + 2 | 2 x n2 | 2 x n2 + 1 | 2 x n2 + 3 |
DATE | 4 | 5 | 6 | -- | 5 | 7 |
TIME | 3 | 4 | 5 | -- | 4 | 6 |
TIMESTAMP | 7 + p![]() | 8 + p![]() | 9 + p![]() | -- | 8 + p![]() | 10 + p![]() |
INTERVAL YEAR TO DAY | 5 | 6 | 7 | -- | 6 | 8 |
INTERVAL HOUR TO SECOND | 4 | 5 | 6 | -- | 5 | 7 |
VARCHAR, MVARCHAR | n1 + 1 | -- | n1 + 2 | n1 + 2 | -- | n1 + 3 |
NVARCHAR | 2 x n2 + 1 | -- | 2 x n2 + 2 | 2 x n2 + 2 | -- | 2 x n2 + 3 |
Notes
Examples
CREATE INDEX IDX1
ON STOCK(PCODE ASC)
PCTFREE = 50
CREATE INDEX IDX2
ON STOCK(PNAME,COLOR)
IN RDA1
CREATE INDEX IDX3
ON STOCK(PCODE)
IN ((RDA1),(RDA2),(RDA3))