14.2.1 Creating an index

Organization of this subsection
(1) Effects of indexes
(2) Criteria
(3) Creation procedure
(4) Common rules
(5) Data types for which indexes cannot be defined
(6) Maximum index key length
(7) Notes

(1) Effects of indexes

Improved performance
Table retrieval performance improves when an index is created for a column that is used as the key for table retrieval.

(2) Criteria

An index should be created for the following columns:

An index should not be created for the following columns (if an index is created for such a column, retrieval performance will be degraded):

(3) Creation procedure

The CREATE INDEX definition SQL is used to create an index for a table.

(4) Common rules

  1. A maximum of 255 indexes can be defined per table.
  2. Indexes can be defined for columns with null values or columns with no rows.
  3. Indexes cannot be created for view tables.
  4. When optimizing indexes based on cost, use the optimizing information collection utility (pdgetcst command) to collect optimizing information as necessary to improve the accuracy of optimization. For details about the necessity of executing this utility, see Optimizing information collection levels in the manual HiRDB Version 8 Command Reference.

(5) Data types for which indexes cannot be defined

Indexes cannot be specified for columns of the following types:

(6) Maximum index key length

The length of an index key must satisfy the following condition; if this condition is not satisfied, the index cannot be defined:

Index key length (bytes)
[Figure]MIN { (index-storage-RDAREA-page-size[Figure] 2) - 1242, 4036}

If the page size of the index storage RDAREA is 4096 bytes, the maximum key length that can be specified for an index is 806 bytes. For details about index key length, see Table 17-5 List of index key lengths.

For a multicolumn index, the total index key length is the total of the key lengths of the columns that make up the index.

(7) Notes

The same index cannot be created more than once for the same table. The following examples show how indexes can be regarded as being the same index in spite of having different index names.

[Figure]Single-column index

CREATE INDEX index-1 ON table-1 (column-1 ASC)
CREATE INDEX index-2 ON table-1 (column-1 DESC)

In this case, index-2 is treated as the same index as index-1. Therefore, index-1, which was defined first, is the valid one.
[Figure]Multicolumn index

CREATE INDEX index-1 ON table-1 (column-1 ASC, column-2 ASC)
CREATE INDEX index-2 ON table-1 (column-1 DESC, column-2 DESC)

or

CREATE INDEX index-1 ON table-1 (column-1 ASC, column-2 DESC)
CREATE INDEX index-2 ON table-1 (column-1 DESC, column-2 ASC)

In this case, index-1 and index-2 are treated as the same index. Therefore, index-1, which was defined first, is the valid one. In the following case, on the other hand, the indexes are treated as different indexes:

CREATE INDEX index-1 ON table-1 (column-1 DESC, column-2 DESC)
CREATE INDEX index-2 ON table-1 (column-1 ASC, column-2 DESC)