ALTER INDEX

Change the definition of an index.

Summary

ALTER INDEX [ IF EXISTS ] name RENAME TO new_name

ALTER INDEX [ IF EXISTS ] name SET TABLESPACE tablespace_name

ALTER INDEX [ IF EXISTS ] name SET ( storage_parameter = value [, ...] )

ALTER INDEX [ IF EXISTS ] name RESET ( storage_parameter  [, ...] )

ALTER INDEX ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
  SET TABLESPACE new_tablespace [ NOWAIT ]

describe

ALTER INDEX Changes the definition of an index that exists. There are some subforms of the following:

RENAME — Modify the index name and has no effect on the stored data.
SET TABLESPACE — Modify the indexed tablespace to the specified tablespace, and then move the data file allocated to the index to the new tablespace as well. To modify the indexed tablespace, you must have CREATE permissions in the new tablespace. All indexes under a tablespace in the current database can be moved in the form of ALL IN TABLESPACE, which will lock all indexes to be moved and then move each index. This approach is also supported in the OWNED BY form, which only moves the index owned by the specified role. If the NOWAIT option is specified, the command will fail if the required lock cannot be obtained immediately. Note that the system directory will not be moved by this command. If necessary, please use ALTER DATABASE or explicitly call ALTER INDEX. See also CREATE TABLESPACE.
IF EXISTS — If the index does not exist, an error will not be thrown, but a reminder will be triggered.
SET — Changes the index-specific storage parameters of the index. The built-in indexing methods all accept one parameter: fillfactor. The fillfactor of the index is a percentage that determines the extent to which the index method tries to package the index page. This command does not modify the index content immediately. Rebuild the index with REINDEX to get the desired effect.
RESET — Resets the index's storage parameters to their default values. The built-in indexing methods all accept one parameter: fillfactor. Like SET, REINDEX may be required to fully update the index.

Parameters

name

The name of the existing index to be modified (optional qualified).

new_name

The new name of the index.

tablespace_name

The tablespace to move the index.

storage_parameter

index-method-specific The name of the storage parameter specified.

value

index-method-specific The value of the stored parameter, which may be based on the number or word of the parameter

Example

Modify the name of an index:

ALTER INDEX distributors RENAME TO suppliers;

Move the index to another tablespace:

ALTER INDEX distributors SET TABLESPACE fasttablespace;

To change the fill factor of the index (assuming that the index method supports it):

ALTER INDEX distributors SET (fillfactor = 75);
REINDEX INDEX distributors;

compatibility