Improved performance

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

An index should be created for the following columns:

• Column used as a condition for narrowing the data to be retrieved
• Column used as a condition for table join processing
• Column used as a condition for data sorting or grouping
• Component column for which a referential constraint has been defined (foreign key)

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

• Column that is updated frequently
• Column that contains many duplicated values

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

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 9 Command Reference.

Indexes cannot be specified for columns of the following types:

• BLOB
• BINARY
• Abstract data types

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) MIN{ (index-storage-RDAREA-page-size 2) - 1,242, 4,036}

If the page size of the index storage RDAREA is 4,096 bytes, the maximum key length that can be specified for an index is 806 bytes. For details about index key length, see Table 16-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.

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.

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.

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)