Changes the definition of an index.
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 ]
ALTER INDEX
changes the definition of an existing index. There are several subforms:
CREATE
privilege on the new tablespace. All indexes in the current database in a tablespace can be moved by using the ALL IN TABLESPACE
form, which will lock all indexes to be moved and then move each one. This form also supports OWNED BY
, which will only move indexes owned by the roles specified. If the NOWAIT
option is specified then the command will fail if it is unable to acquire all of the locks required immediately. Note that system catalogs will not be moved by this command, use ALTER DATABASE
or explicit ALTER INDEX
invocations instead if desired. See also CREATE TABLESPACE
.REINDEX
to rebuild the index to get the desired effects.SET
, a REINDEX
may be needed to update the index entirely.These operations are also possible using ALTER TABLE
.
Changing any part of a system catalog index is not permitted.
To rename an existing index:
ALTER INDEX distributors RENAME TO suppliers;
To move an index to a different tablespace:
ALTER INDEX distributors SET TABLESPACE fasttablespace;
To change an index's fill factor (assuming that the index method supports it):
ALTER INDEX distributors SET (fillfactor = 75);
REINDEX INDEX distributors;
ALTER INDEX
is a Greenplum Database extension.
CREATE INDEX, REINDEX, ALTER TABLE
Parent topic: SQL Commands