ALTER INDEX v14

Name

ALTER INDEX -- modify an existing index.

Synopsis

EDB Postgres Advanced Server supports three variations of the ALTER INDEX command compatible with Oracle databases. Use the first variation to rename an index:

ALTER INDEX <name> RENAME TO <new_name>

Use the second variation of the ALTER INDEX command to rebuild an index:

ALTER INDEX <name> REBUILD

Use the third variation of the ALTER INDEX command to set the PARALLEL or NOPARALLEL clause:

ALTER INDEX <name> { NOPARALLEL | PARALLEL [ <integer> ] }

Description

ALTER INDEX changes the definition of an existing index. The RENAME clause changes the name of the index. The REBUILD clause reconstructs an index, replacing the old copy of the index with an updated version based on the index's table.

The REBUILD clause invokes the PostgreSQL REINDEX command; for more information about using the REBUILD clause, see the PostgreSQL core documentation at:

https://www.postgresql.org/docs/current/static/sql-reindex.html

The PARALLEL clause sets the degree of parallelism for an index that can be used to parallelize the rebuilding of an index.

The NOPARALLEL clause resets parallelism to use default values; reloptions shows the parallel_workers parameter as 0.

ALTER INDEX has no effect on stored data.

Parameters

name

The name (possibly schema-qualified) of an existing index.

new_name

New name for the index.

PARALLEL

Include the PARALLEL clause to specify a degree of parallelism; set the parallel_workers parameter equal to the degree of parallelism for the rebuilding of an index. If you specify PARALLEL but no degree of parallelism is provided, the server enforces default parallelism.

NOPARALLEL

Specify NOPARALLEL to reset parallelism to default values.

integer

The integer indicates the degree of parallelism (the number of parallel_workers used when rebuilding an index).

Examples

To change the name of an index from name_idx to empname_idx:

ALTER INDEX name_idx RENAME TO empname_idx;

To rebuild an index named empname_idx:

ALTER INDEX empname_idx REBUILD;

The following example sets the degree of parallelism on an empname_idx index to 7:

ALTER INDEX empname_idx PARALLEL 7;

See also

CREATE INDEX, DROP INDEX