ALTER INDEX (Alter index definition)

Function

ALTER INDEX changes the name of an index defined by CREATE INDEX.

Privileges

Owner of the index
A user can only specify an index owned by that user.

Format

The item numbers in the following formats correspond to the operand numbers below.

Item no.Format
1

ALTER INDEX [authorization-identifier.]index-identifier alter-index-definition-operation

alter-index-definition-operation::=alter-index-name-definition

Operands

1) authorization-identifier.index-identifier
authorization-identifier
Specifies the authorization identifier of the owner of the index whose definition is to be changed.
index-identifier
Specifies the name of the index whose definition is to be changed.
2) alter-index-name-definition::=
RENAME INDEX TO authorization-identifier [WITH PROGRAM]

Specify this to change the index name.

index-identifier
Specifies the new name of the index.
The rules for the index identifier are as follows.
  1. You can change the names of a B-tree index defined in CREATE INDEX, an index with a specified index format, and a partial structure index.
  2. You cannot use a name more than once for an index in a schema, an index with a specified index format, or a partial structure index.
WITH PROGRAM
Specify to prevent the procedure for using the index or the valid SQL object of a trigger when changing the index name.
The index definition cannot be changed if there is a valid SQL object of a procedure, or trigger that uses the index, and WITH PROGRAM is omitted.

Notes

  1. Although a definition SQL statement can be executed from a Java procedure, if the procedure that calls the Java procedure issues an SQL statement using an index, then issuing ALTER INDEX for the same index in the called Java procedure results in an error.
  2. ALTER INDEX cannot be specified from an X/Open-compliant UAP running under OLTP.
  3. To execute an SQL object of a procedure or trigger that has been disabled by specifying WITH PROGRAM, you must execute ALTER ROUTINE, ALTER PROCEDURE, or ALTER TRIGGER, and re-create the SQL object of the procedure or trigger.
  4. When a valid SQL object of a procedure or trigger is disabled by specifying WITH PROGRAM, the columns related to the disabled procedure or trigger in the SQL_ROUTINE_RESOURCES, SQL_TRIGGER_USAGE, SQL_TRIGGER_COLUMNS, and SQL_ROUTINE_PARAMS tables are deleted.
  5. If there is a procedure or trigger that accesses a table in which is defined an index being modified by ALTER INDEX, and that index is not used, executing ALTER INDEX renders the index information in the SQL object of the procedure or trigger unavailable for use. In this case, although the procedure that uses the unavailable index information can be executed, its performance is degraded because the index must be recompiled each time the procedure is executed. In addition, the procedure or trigger for calling the procedure that uses the disabled index information can no longer be executed. This means that ALTER ROUTINE, ALTER PROCEDURE, or ALTER TRIGGER must be executed for the procedure or trigger that uses the disabled index information and the SQL object must be re-created. To determine whether the index information is disabled, check the INDEX_VALID column in the SQL_ROUTINE dictionary table.
  6. When executing the database load utility (pdload) or database reorganization utility (pdrorg) and creating an index using the index information output mode, specifying ALTER INDEX to change the index name before the index is created prevents it from being created from the completed index information file. If the index name was changed accidentally, use ALTER INDEX to change the name of the index back to its original name, and then change the name of the index again after it has been created.
  7. When performing a delayed batch creation of a plug-in index, using ALTER INDEX to change the plug-in index name before the index is created prevents it from being created from the completed index information file. If the plug-in index name was changed accidentally, change the name of the plug-in index back to its original name, and then change the name of the plug-in index again after it has been created.
  8. ALTER INDEX can be used to change the name of an unfinished index (index immediately after CREATE INDEX specifying EMPTY is executed).

Examples

Changes the index name from (IDX1) to (IDX2).

   ALTER INDEX IDX1 RENAME INDEX TO IDX2