The following is the procedure for defining an index for a table that contains data.

Procedure

To define an index:

1. Use CREATE INDEX to define the index. If a UAP is accessing the table while the index is being defined, the UAP is placed in lock-release wait status.
2. If necessary, the optimizing information collection utility (pdgetcst command) should be executed after the index has been defined. Plug-in indexes are not processed by the optimizing information collection utility. For details about whether execution of the optimizing information collection utility is required, see the manual HiRDB Version 9 Command Reference.
3. Defining an index invalidates the index information of any stored routines that use the table for which that index is defined. If this happens, use the ALTER PROCEDURE or ALTER ROUTINE statement to re-create each stored routine.
   In addition, defining an index for a table specified in a trigger SQL statement invalidates the index information of that trigger. If this happens, use the ALTER TRIGGER or ALTER ROUTINE statement to re-create the trigger.
   

When an index is to be defined for a table that has a large amount of data, it will take a considerable amount of time to create the index entity (to execute CREATE INDEX), and it will not be possible to execute any other definition SQL during this period.

However, if CREATE INDEX is executed with the EMPTY option specified, an index definition is created without creating the actual index entity. This is called an unfinished index. Because the index entity is not created, execution of CREATE INDEX is completed immediately, so that execution of other definition SQLs is not delayed.

Note that the EMPTY option can also be specified for a plug-in index.

Reference note

1. Because no index entity has been created for an unfinished index, the unfinished index cannot be used for retrieval processing, nor can a column be updated in a table for which an unfinished index is defined (if updating is attempted, an SQL error results).
2. The database condition analysis utility (pddbst command) can be used to determine whether an index is unfinished. In the case of condition analysis by index or by RDAREA (logical analysis), an unfinished index is indicated under the status heading; in the case of cluster key and clustering data page storage condition analysis, an unfinished index is reported in a warning message.
3. The index re-creation facility (-k ixrc) of the database reorganization utility (pdrorg command) is used to create an index entity. When an unfinished index's index entity is created, the unfinished status is released. If the table is deleted in its entirety by the PURGE TABLE statement, all indexes for the table are released from unfinished status.
4. An index for a partitioned table is also partitioned and stored in multiple RDAREAs, and the unfinished status is managed for each partitioned index. The database reorganization utility (pdrorg command) can create the index entity for each RDAREA that stores a part of the index. When only a part of the partitioned index is created, SQL code might not execute successfully, depending on the specified conditions.