3.5.1 Specification format and rules for the CREATE INDEX statement

The CREATE INDEX statement defines an index (a B-tree index, text index, or range index) on a column in a base table. For details about B-tree indexes, text indexes, and range indexes, see B-tree indexes, Text indexes, and Range indexes in the HADB Setup and Operation Guide.

A B-tree index can be defined on multiple columns. A B-tree index defined on only one column is called a single-column index, and a B-tree index defined on multiple columns is called a multiple-column index.

Important

If you define an index for a base table to which row storage segments have been allocated, the index is placed in unfinished status (status in which no index data is created).

For example, no row storage segments have been allocated at the following times. If you define an index for a base table in this status, the index is created normally.

Immediately after a base table is defined
Immediately after the TRUNCATE TABLE statement is run

While a B-tree index is in unfinished status, you cannot perform searches that use the unfinished B-tree index, nor can you execute INSERT, UPDATE, or DELETE statements on the table.

While a text index is in unfinished status, you cannot perform searches that use the unfinished text index, nor can you execute INSERT, UPDATE, or DELETE on the table.

While a range index is in unfinished status, you cannot perform searches that use the unfinished range index, nor can you execute INSERT or UPDATE on the table.

For details about how to release indexes from unfinished status, see the following sections (whichever is applicable) in the HADB Setup and Operation Guide: Steps to take when unfinished status is applied to a B-tree index, Steps to take when unfinished status is applied to a text index, or Steps to take when unfinished status is applied to a range index.

For details about the status in which row storage segments have been allocated, see Notes on defining B-tree indexes (unfinished status of B-tree indexes) in the HADB Setup and Operation Guide.

Organization of this subsection

(1) Specification format
(2) Explanation of specification format
(3) Privileges required at execution
(4) Rules
(5) Examples

(1) Specification format

CREATE-INDEX-statement ::=
     CREATE [UNIQUE] INDEX index-name
          ON table-name (column-name [{ASC|DESC}][,column-name [{ASC|DESC}]]...)
        [IN DB-area-name]
        [PCTFREE=percentage-of-unused-area]
          EMPTY
        [INDEXTYPE {BTREE|TEXT [WORDCONTEXT]|RANGE}]
        [CORRECTIONRULE]
        [DELIMITER {DEFAULT|ALL}]
        [EXCLUDE NULL VALUES]

Note

PCTFREE, EMPTY, INDEXTYPE, CORRECTIONRULE, DELIMITER, and EXCLUDE NULL VALUES are generically called index options.
Index options can be specified in any order.

The following table shows the different options that can be specified depending on which type of index is defined.

Table 3‒4: Options for defining an index
No.	CREATE INDEX option	When defining a B-tree index	When defining a text index	When defining a range index
1	`UNIQUE`	Y	N	N
2	`index-name`	Y	Y	Y
3	`ON` `table-name`	Y	Y	Y
4	`column-name`	Y	Y	Y
5	`{ASC\|DESC}`	Y	N	N
6	`IN` `DB-area-name`	Y	Y	Y
7	`PCTFREE`	Y	Y	N
8	`EMPTY`	Y	Y	Y
9	`INDEXTYPE`	Y	Y	Y
10	`CORRECTIONRULE`	N	Y	N
11	`DELIMITER`	N	Y	N
12	`EXCLUDE` `NULL` `VALUES`	Y	N	N

Legend:

Y: An option that can be specified, or one that must be specified.

N: An option that cannot be specified.

Note: It is not possible to define a primary key using the CREATE INDEX statement. To define a primary key, specify a uniqueness constraint definition using the CREATE TABLE statement.

To Page Top

(2) Explanation of specification format

In the option descriptions, options marked [B-tree index] can be specified during definition of a B-tree index. Options marked [Text index] can be specified during definition of a text index. Options marked [Range index] can be specified during definition of a range index. Options marked [Common] are common to B-tree indexes, text indexes, and range indexes.

● UNIQUE [B-tree index]

Specify this if you want the B-tree index to be a unique index. A unique index is a B-tree index that does not allow duplicate key values (the values of the columns on which the B-tree index is being defined). However, if the key values can include null values, duplicate null values do not result in duplicate keys.

For a multiple-column index, a key value is considered different if its value in any one of the columns is different.

If UNIQUE is specified, you cannot update or add data that would result in a duplicate key value.

Note that you cannot define a unique index for a base table created by using the CREATE TABLE statement with chunk specification.

● index-name [Common]

Specifies the name of the index to be defined. For rules on specifying an index name, see (3) Index name specification format in 6.1.5 Qualifying a name.

Note that you cannot specify the index name of an index that has already been defined.

● ON table-name [Common]

Specifies the name of the base table for which the index is to be defined. For rules on specifying a table name, see (2) Table name specification format in 6.1.5 Qualifying a name.

Note that a viewed table cannot be specified in table-name.

● (column-name [{ASC|DESC}] [,column-name [{ASC|DESC}]]...) [Common]

• For a B-tree index

Specifies the names of the columns on which the B-tree index is being defined, and the ordering of the B-tree index's key values.

column-name:

Specifies the names of the columns on which the B-tree index is being defined. A maximum of 16 column names can be specified. If multiple column names are specified, each column name must be unique.

If multiple column names are specified, the resulting B-tree index is a multiple-column index.

ASC:

Specifies that the B-tree index is to be organized in ascending order of the key values.

DESC:

Specifies that the B-tree index is to be organized in descending order of the key values.

For a single-column index, DESC is ignored. The index's key values are always arranged in ascending order (ASC is assumed).

If neither ASC nor DESC is specified, the system assumes that ASC is specified.

• For a text index or range index

Specifies the name of the column on which the text index or range index is being defined.

In the case of a text index or range index, only one column name can be specified. In addition, ASC and DESC cannot be specified.

Therefore, the specification format in the case of a text index or range index is as follows:

(column-name)

● IN DB-area-name [Common]

Specifies the name of the DB area in which the index is to be defined.

If the IN DB-area-name specification is omitted, the index is stored in the DB area specified for the adb_sql_default_dbarea_shared operand in the server definition.

Note that if the IN DB-area-name specification is omitted when either of the following conditions is met, the CREATE INDEX statement will result in an error:

Specification of the adb_sql_default_dbarea_shared operand is omitted in the server definition.
A non-existent DB area or a DB area other than a data DB area is specified for the adb_sql_default_dbarea_shared operand in the server definition.

● PCTFREE=percentage-of-unused-area [B-tree index] [Text index]

~ <unsigned integer> ((0 to 99)) <<30>> (unit: %)

Specifies the percentage of unused area to maintain in the index page of a B-tree index or text index. Specify a percentage from 0 to 99. If omitted, 30 (%) is assumed.

When data is imported and an index is created or when the index is rebuilt, the B-tree index data or text index data will be stored leaving the percentage of unused area specified here.

For details about the percentage of unused area in an index page, see Allocating an unused area inside a B-tree index page (PCTFREE) or Allocating an unused area inside a text index page (PCTFREE) in the HADB Setup and Operation Guide.

Note that PCTFREE cannot be specified more than once.

● EMPTY [Common]

EMPTY must be specified. If EMPTY is omitted, the CREATE INDEX statement cannot be executed.

EMPTY cannot be specified more than once.

● INDEXTYPE {BTREE|TEXT [WORDCONTEXT]|RANGE} [Common]

Specifies the type of index to be defined.

BTREE:: Specify this if you want to define a B-tree index.
TEXT [WORDCONTEXT]:: Specify this if you want to define a text index. To define a text index for a word-context search, specify TEXT WORDCONTEXT.
RANGE:: Specify this if you want to define a range index.

If specification of INDEXTYPE is omitted, the system assumes that BTREE (B-tree index) is specified.

INDEXTYPE can only be specified once.

● CORRECTIONRULE [Text index]

Specify this option when you define a text index that supports correction search. For details about the correction search in a text index, see Correction search in the HADB Setup and Operation Guide.

Note that correction search cannot be used if the character encoding used on the HADB server is Shift-JIS (if the value of the ADBLANG environment variable is SJIS). In such a case, you cannot specify CORRECTIONRULE.

Also note that you cannot specify the CORRECTIONRULE option more than once.

Note: This option specification is referred to as the notation-correction-search text-index specification.

● DELIMITER {DEFAULT|ALL} [Text index]

Specifies the group of characters that can be used as word delimiters during a word-context search.

DEFAULT:

Handles the following characters as delimiters during a word-context search:

Half-width space (0x20)
Tab (0x09)
Line break (0x0A)
Return (0x0D)
Period (0x2E)
Question mark (0x3F)
Exclamation mark (0x21)

ALL:

Handles the following characters as delimiters during a word-context search:

Half-width space (0x20)
Tab (0x09)
Line break (0x0A)
Return (0x0D)
Single-byte symbols including periods, question marks, and exclamation marks (0x21 to 0x2F, 0x3A to 0x40, 0x5B to 0x60, and 0x7B to 0x7E)

To specify this option, TEXT WORDCONTEXT must be specified for INDEXTYPE.

If specification of DELIMITER is omitted when TEXT WORDCONTEXT is specified for INDEXTYPE, the system assumes that DEFAULT is specified.

Note: The specification of this option is called text-index delimiter specification.

● EXCLUDE NULL VALUES [B-tree index]

If this option is specified and a B-tree index is created, no B-tree index key values that are composed of null values alone will be created. Consider specifying this option if you want to index columns in which most of the values are null.

Specifying this option can reduce the time it takes to create a B-tree index, because no B-tree index key values composed of null values alone will be created. Among other benefits, this can reduce the time it takes to import data and reduce the amount data required for the B-tree index.

Note that you cannot specify this option for a B-tree index that is defined on columns on which the NOT NULL constraint is defined.

Also note that you cannot specify the EXCLUDE NULL VALUES option more than once.

Note: This option is referred to as the null-value exclusion specification.

To Page Top

(3) Privileges required at execution

To execute the CREATE INDEX statement, the CONNECT privilege and schema definition privilege are required.

To Page Top

(4) Rules

(a) Common rules for indexes

An index can only be defined for a base table owned by the current user (the HADB user whose authorization identifier is currently connected to the HADB server). You cannot define an index for a base table owned by another HADB user.
Indexes cannot be defined on viewed tables.
A maximum combined total of 32 B-tree, text, and range indexes can be created for one table.
A maximum combined total of 8,192 B-tree, text, and range indexes can be defined in the system (excluding indexes defined for the base tables of dictionary tables and system tables).
A maximum of 400 indexes can be stored in one DB area.
The same column can have B-tree indexes (single-column indexes), text indexes, and range indexes defined on it.
To define an index for a multi-chunk table, see Points to consider in storing a multi-chunk table in the data DB area in the HADB Setup and Operation Guide.
You cannot define an index for a table that has become non-updatable due to interruption of a command.

(b) Rules for B-tree indexes

When a single-column index is defined, it must satisfy the formula below. You cannot define a single-column index that does not satisfy this formula.

size-of-column-that-comprises-single-column-index ≤ MIN{(a ÷ 3) - 128, 4036 } (unit: bytes)

a: Page size of the DB area where the B-tree index is to be stored

The size of a column that comprises a single-column index can be calculated by using the information in the following table.

Table 3‒5: Size of a column that comprises a single-column index
No.	Data type of the column		Size of the column (unit: bytes)
1	`INTEGER`		8
2	`SMALLINT`		4
3	`DECIMAL(m,n)`	If 1 ≤ `m` ≤ 4	2
		If 5 ≤ `m` ≤ 8	4
		If 9 ≤ `m` ≤ 16	8
		If 17 ≤ `m` ≤ 38	16
4	`DOUBLE` `PRECISION`		8
5	`CHAR(n)`		`n`
6	`VARCHAR(n)`		`n`
7	`DATE`		4
8	`TIME(p)`		3 + ↑`p` ÷ 2↑
9	`TIMESTAMP(p)`		7 + ↑`p` ÷ 2↑
10	`BINARY(n)`		`n`
11	`VARBINARY(n)`		`n`

Legend:

m, n: Positive integers

p: 0, 3, 6, 9, or 12

To define a multiple-column index, the following conditional expression must be satisfied. You cannot define a multiple-column index that does not satisfy this formula.

total-size-of-columns-that-comprise-multiple-column-index ≤ MIN{(a ÷ 3) - 128, 4036 } (unit: bytes)

a: Page size of the DB area where the B-tree index is to be stored

To obtain the total size of the columns that comprise a multiple-column index, see the following table.

Table 3‒6: Size of columns that comprise a multiple-column index
No.	Data type of a column		Size of the columns that comprise a multiple-column index (unit: bytes)^#
			If the total defined size of all columns does not exceed 255 bytes	If the total defined size of all columns exceeds 255 bytes
				If only fixed size columns are included	If variable size columns are also included
1	`INTEGER`		9	9	10
2	`SMALLINT`		5	5	6
3	`DECIMAL(m,n)`	If 1 ≤ `m` ≤ 4	3	3	4
		If 5 ≤ `m` ≤ 8	5	5	6
		If 9 ≤ `m` ≤ 16	9	9	10
		If 17 ≤ `m` ≤ 38	17	17	18
4	`DOUBLE` `PRECISION`		9	9	10
5	`CHARACTER(n)`		`n` + 1	`n` + 1	`n` + 2
6	`VARCHAR(n)`		`n` + 1	--	`n` + 2
7	`DATE`		5	5	6
8	`TIME(p)`		4 + ↑`p` ÷ 2↑	4 + ↑`p` ÷ 2↑	5 + ↑`p` ÷ 2↑
9	`TIMESTAMP(p)`		8 + ↑`p` ÷ 2↑	8 + ↑`p` ÷ 2↑	9 + ↑`p` ÷ 2↑
10	`BINARY(n)`		`n` + 1	`n` + 1	`n` + 2
11	`VARBINARY(n)`		`n` + 1	--	`n` + 2

Legend:

m, n: Positive integers

p: 0, 3, 6, 9, or 12

--: Not applicable

#

If the result calculated based on the formulas under If the total defined size of all columns does not exceed 255 bytes yields a total that exceeds 255 bytes, re-calculate the sizes of the columns using the formulas under If the total defined size of all columns exceeds 255 bytes.

You cannot define more than one of the following kinds of B-tree indexes:
- B-tree indexes that have the same column structure, and where the same ascending or descending order is specified for all columns.
- B-tree indexes that have the same column structure, but where the opposite ascending or descending order is specified for all columns.
A column on which a single-column index is defined can be specified when defining a multiple-column index.
When a multiple-column index is defined, the order in which the columns are specified determines the order of precedence for creating key values.

(c) Rules for text indexes

Text indexes can be defined on columns of the following data types:
- CHARACTER types
- VARCHAR types
You cannot define multiple text indexes with the same indexed columns.
You cannot define a text index for column store tables.

(d) Rules for range indexes

Range indexes cannot be defined on columns of the following data types:
- CHARACTER types whose length exceeds 32 bytes
- VARCHAR types
- BINARY types
- VARBINARY types
You cannot define multiple range indexes with the same indexed columns.

To Page Top

(5) Examples

Example 1: Define a B-tree index

Define a B-tree index for the shops table (SHOPSLIST) as follows:

Define a single-column index (SHOP_CODE_IDX) on the shop code column (SHOP_CODE).
Make the B-tree index a unique index.
Store the B-tree index in the DB area DBAREA01.
Because rows are added frequently to the shops table (SHOPSLIST), let the percentage of unused area in an index page be 50 percent.

CREATE UNIQUE INDEX "SHOP_CODE_IDX"
          ON "SHOPSLIST" ("SHOP_CODE")
        IN "DBAREA01"
        PCTFREE = 50
        EMPTY

Example 2: Define a B-tree index

Define a B-tree index for the shops table (SHOPSLIST) as follows:

Define a multiple-column index (SHOP_RGN_IDX) with the shop code column (SHOP_CODE) and the region code column (RGN_CODE) as the indexed columns.
Sort the key values of the index in ascending order (ASC) for the shop code, and in descending order (DESC) for the region code.
Store the B-tree index in the DB area DBAREA01.

CREATE INDEX "SHOP_RGN_IDX"
          ON "SHOPSLIST" ("SHOP_CODE" ASC,"RGN_CODE" DESC)
        IN "DBAREA01"
        EMPTY

Example 3: Define a text index

Define a text index for the employee table (EMPLOYEE) as follows:

Define a text index (ADDRESS_IDX) on the address column (ADDRESS).

CREATE INDEX "ADDRESS_IDX"
          ON "EMPLOYEE" ("ADDRESS")
        IN "DBAREA01"
        EMPTY
        INDEXTYPE TEXT

If you want the text index to support correction search, define the text index as follows. In this example, the underlined option is added.

CREATE INDEX "ADDRESS_IDX"
      ON "EMPLOYEE" ("ADDRESS")
    IN "DBAREA01"
    EMPTY
    INDEXTYPE TEXT
    CORRECTIONRULE

If you want to define the text index for a word-context search, define the text index as follows. In this example, the underlined options are added.

CREATE INDEX "ADDRESS_IDX"
      ON "EMPLOYEE"("ADDRESS")
    IN "DBAREA01"
    EMPTY
    INDEXTYPE TEXT WORDCONTEXT
    DELIMITER DEFAULT

Example 4: Define a range index

Define a range index for the shops table (SHOPSLIST) as follows:

Define a range index (SHOP_CODE_RIDX) on the shop code column (SHOP_CODE)
Store the range index in the DB area DBAREA01.

CREATE INDEX "SHOP_CODE_RIDX"
          ON "SHOPSLIST" ("SHOP_CODE")
        IN "DBAREA01"
        EMPTY
        INDEXTYPE RANGE

To Page Top