RENAME INDEX statement

Use the RENAME INDEX statement to change the name of an existing index. This statement is an extension to the ANSI/ISO standard for SQL.

Syntax


1  RENAME INDEX? owner.  old_index TO new_index
Element Description Restrictions Syntax
new_index New name that you declare here for the index Name must be unique to the database (or to the session, if old_index is on a temporary table) Identifier
old_index Index name that new_index replaces Must exist, but it cannot be any of the following:
  • An index on a system catalog table
  • A system-generated constraint index
  • A Virtual-Index Interface (VII)
 Identifier
owner Owner of index Must be the owner of old_index Owner name

Usage

You can rename an index if you are the owner of the index or have the DBA privilege on the database.

When you rename an index, the database server changes the index name in the sysindexes, sysconstraints, sysobjstate, and sysfragments system catalog tables. (But for an index on a temporary table, no system catalog tables are updated.)

You can rename a system-generated index that implements a primary-key or foreign-key constraint. When you rename a system-generated index, the index is not automatically dropped if you drop the corresponding constraint. The name of a system-generated index consists of a white space, a number, an underscore, and a number. Omit the white space at the beginning of the system-generated index name in the RENAME INDEX statement. For example, to rename an index that is named " 100_1" to idx1, run the following statement:
RENAME INDEX 100_1 TO idx1;
You can include the owner name before the system-generated index name, for example: 
RENAME INDEX jmm.100_1 TO idx1;

Indexes on system catalog tables cannot be renamed.

If you want to change the name of a system-generated index that implements a constraint, use the ALTER TABLE ... DROP CONSTRAINT statement to drop the constraint, and then use the ALTER TABLE ... ADD CONSTRAINT statement to define a new constraint that has the same definition as the constraint that you dropped, but for which you declare the new name.

By default, SPL routines that use the renamed index are reoptimized when they are next executed after the index is renamed. When automatic recompilation is disabled, however, SPL routines that use the renamed index are automatically recompiled on their next use if the renamed index is associated with a directly referenced table. If the table is only referenced indirectly, however, execution can fail with error -710. For more information about enabling or disabling automatic recompilation after changes to the schema of a referenced table, see the IFX_AUTO_REPREPARE session environment option. For more information about the AUTO_REPREPARE configuration parameter, see your HCL OneDB™ Administrator's Reference.