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


>>-RENAME INDEX--+--------+--old_index--TO--new_index----------><
                 '-owner.-'

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 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.

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 IBM® Informix Administrator's Reference.