ALTER INDEX Statement

Renames indexes in base or global temporary tables, foreign key role names of indexes and foreign keys explicitly created by a user, or changes the clustered nature of an index on a catalog store table. You cannot rename indexes created to enforce key constraints.

Syntax

ALTER { INDEX index-name
   | [ INDEX ] FOREIGN KEY role-name
   | [ INDEX ] PRIMARY KEY
   | ON [owner.]table-name   { rename-clause | move-clause | cluster-clause} 

rename-clause:
   RENAME TO | AS new-name

move-clause:
   MOVE TO dbspace-name

cluster-clause:
   CLUSTERED | NONCLUSTERED

Parameters

ON – specifies the name of the table that contains the index or foreign key to rename.

RENAME – specifies the new name of the index or foreign key role.

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

cluster-clause – specifies whether the index should be changed to CLUSTERED or NONCLUSTERED. Applies to catalog store tables only and only one index on a table can be clustered.

Examples

Example 1 – move 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;

Example 2 – move DATE index from Dsp5 to Dsp9:
```
ALTER INDEX c4_date ON foo MOVE TO Dsp9
```
Example 3 – rename 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
```
Example 4 – rename 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
```

Usage

You must have CREATE privilege on the new dbspace and be the table owner or have the MANAGE ANY DBSPACE system privilege.

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

Side effects:

Automatic commit. Clears the Results tab in the Results pane in Interactive SQL. Closes all cursors for the current connection.

Standards

SQL—ISO/ANSI SQL compliant.
Sybase—Not supported by Adaptive Server Enterprise.

Permissions

move-clause for materialized view requires one of:

MANAGE ANY DBSPACE system privilege.
ALTER ANY INDEX system privilege.
ALTER ANY OBJECT system privilege.
You own the materialized view along with one of:
- CREATE ANY OBJECT system privilege.
- CREATE privilege on the target dbspace.

move-clause for all other indexes requires one of:

MANAGE ANY DBSPACE system privilege.
ALTER ANY INDEX system privilege.
ALTER ANY OBJECT system privilege.
You own the underlying table or have REFERENCE privilege on the table along with one of:
- CREATE ANY OBJECT system privilege.
- CREATE privilege on the target dbspace.

cluster-clause for materialized view requires one of:

ALTER ANY INDEX system privilege.
ALTER ANY OBJECT system privilege.
You own the materialized view.

cluster-clause for all other indexes, requires one of:

ALTER ANY INDEX system privilege.
ALTER ANY OBJECT system privilege.
REFERENCE privilege on the table.
You own the table.

All other clauses require one of:

ALTER ANY INDEX system privilege.
ALTER ANY OBJECT system privilege.
REFERENCE privilege on the table.
You own the underlying table.