Renames indexes in base or global temporary tables and foreign key role names of indexes and foreign keys explicitly created by a user.
ALTER { INDEX index-name
| [ INDEX ] FOREIGN KEY role-name
| [ INDEX ] PRIMARY KEY
| ON [owner.]table-name { rename-clause | move-clause }
: RENAME TO | AS new-name
MOVE TO dbspace-name
This statement moves the primary key, HG for c5, from dbspace Dsp4 to Dsp8.
CREATE TABLE foo ( c1 INT IN Dsp1, c2 VARCHAR(20), c3 CLOB IN Dsp2, c4 DATE, c5 BIGINT, PRIMARY KEY (c5) IN Dsp4) IN Dsp3); CREATE DATE INDEX c4_date ON foo(c4) IN Dsp5; ALTER INDEX PRIMARY KEY ON foo MOVE TO Dsp8;
Moves DATE index from Dsp5 to Dsp9
ALTER INDEX c4_date ON foo MOVE TO Dsp9
Renames an index COL1_HG_OLD in the table jal.mytable to COL1_HG_NEW:
ALTER INDEX COL1_HG_OLD ON jal.mytable RENAME AS COL1_HG_NEW
Renames a foreign key role name ky_dept_id in table dba.Employees to emp_dept_id:
ALTER INDEX FOREIGN KEY ky_dept_id ON dba.Employees RENAME TO emp_dept_id
The ALTER INDEX statement renames indexes and foreign key role names of indexes and foreign keys that were explicitly created by a user. Only indexes on base tables or global temporary tables can be renamed. You cannot rename indexes created to enforce key constraints.
ON clause The ON clause specifies the name of the table that contains the index or foreign key to rename.
RENAME [ AS | TO ] clause The RENAME clause specifies the new name of the index or foreign key role.
MOVE clause The MOVE clause moves the specified index, unique constraint, foreign key, or primary key to the specified dbspace. For unique constraint or foreign key, you must specify its unique index name.
You must have CREATE privilege on the new dbspace and be the table owner or have DBA or SPACE ADMIN authority.
Attempts to alter an index in a local temporary table
return the error “index not found.” Attempts to
alter a nonuser-created index, such as a default index (FP), return
the error “Cannot alter index. Only indexes in base tables
or global temporary tables with an owner type of USER can be altered.”
Automatic commit. Clears the Results tab in the Results pane in Interactive SQL. Closes all cursors for the current connection.
Must own the table, or have REFERENCES permissions on the table, or have DBA or RESOURCE authority. For ALTER INDEX MOVE TO statements, you must have CREATE privilege on the new dbspace and be the table owner or have DBA or SPACE ADMIN authority.
