ALTER INDEX

Description

ALTER INDEX modifies the definition of an existing index.

Precautions

Only the index owner, a user who has the INDEX permission on the table where the index resides, or a user who has the ALTER ANY INDEX permission can run the ALTER INDEX command. When separation of duties is disabled, the system administrator has this permission by default.

Do not keep a large number of invisible indexes on the same base table. Otherwise, the performance of DML operations such as INSERT, UPDATE, and DELETE may be affected.

Syntax

Rename a table index.

      
           ALTER INDEX [ IF EXISTS ] index_name 
    RENAME TO new_name;

Change the tablespace to which a table index belongs.

      
           ALTER INDEX [ IF EXISTS ] index_name 
    SET TABLESPACE tablespace_name;

Modify the storage parameter of a table index.

      
           ALTER INDEX [ IF EXISTS ] index_name 
    SET ( {storage_parameter = value} [, ... ] );

Reset the storage parameter of a table index.

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

Set a table index or an index partition to be unavailable.

      
           ALTER INDEX [ IF EXISTS ] index_name 
    [ MODIFY PARTITION index_partition_name ] UNUSABLE;

Rebuild a table index or index partition.

      
           ALTER INDEX index_name 
    REBUILD [ PARTITION index_partition_name ];

Rename an index partition.

      
           ALTER INDEX [ IF EXISTS ] index_name 
    RENAME PARTITION index_partition_name TO new_index_partition_name;

Modify the tablespace to which an index partition belongs.

      
           ALTER INDEX [ IF EXISTS ] index_name 
    MOVE PARTITION index_partition_name TABLESPACE new_tablespace;

Set the distributed global secondary index to be ready.
```
ALTER INDEX [ IF EXISTS ] index_name GSIVALID;
```
This syntax is internally called by the CREATE GLOBAL INDEX CONCURRENTLY function to change the status of a global secondary index. You are advised not to use this syntax. Otherwise, the index data may be inconsistent with the table data. To enable a GSI in the UNVALID state, you are advised to use the REINDEX INDEX syntax to rebuild the GSI.
Set the distributed global secondary index to be available.
```
ALTER INDEX [ IF EXISTS ] index_name GSIUSABLE;
```
This syntax is internally called by the VACUUM FULL function to change the status of a global secondary index. You are advised not to use this syntax. Otherwise, the index data may be inconsistent with the table data. To enable a GSI in the UNUSABLE state, you are advised to use the REINDEX INDEX syntax to rebuild the GSI.

Set the index to be visible.
```
ALTER INDEX [ IF EXISTS ] index_name VISIBLE;
```
- This syntax can be set only in the ORA-compatible database (that is, sql_compatibility = 'ORA').
- When disable_keyword_options is set to "visible", the VISIBLE keyword cannot be used.
- This syntax is not supported in the upgrade uncommitted phase.

Set the index to be invisible.
```
ALTER INDEX [ IF EXISTS ] index_name INVISIBLE;
```
- This syntax can be set only in the ORA-compatible database (that is, sql_compatibility = 'ORA').
- When disable_keyword_options is set to "invisible", the INVISIBLE keyword cannot be used.
- This syntax is not supported in the upgrade uncommitted phase.
- In the standby node read scenario, after the index is set to invisible, the execution plan of the query statement may change, and the query performance of the standby node may be affected.

Parameters

index_name
Specifies the index name to be modified.
IF EXISTS
Sends a notice instead of an error if the specified index does not exist.
RENAME TO new_name
Changes only the name of the index. The stored data is not affected.
- new_name
  Specifies the new name of the index.
  
  Value range: a string. It must comply with the naming convention.
SET TABLESPACE tablespace_name
This option changes the index tablespace to the specified tablespace and moves index-related data files to the new tablespace.
- tablespace_name
  Specifies the tablespace name.
  
  Value range: an existing tablespace name
SET ( {storage_parameter = value} [, ... ] )
Changes one or more index-method-specific storage parameters of an index. Note that the index content will not be modified immediately by this statement. You may need to use REINDEX to rebuild the index based on different parameters to achieve the expected effect.
- storage_parameter
  Specifies the name of an index-method-specific parameter. ACTIVE_PAGES indicates the number of index pages, which may be less than the actual number of physical file pages and can be used for optimization. Currently, this parameter is valid only for the local index of the Ustore partitioned table and will be updated by VACUUM and ANALYZE (including AUTOVACUUM). You are advised not to manually set this parameter because it is invalid in distributed mode. When ALTER is executed on GSI, the INTERNAL_MASK, APPEND_MODE_INTERNAL, WAIT_CLEAN_GPI, PARTITION_DDL_FLAG, COLLATE, CROSSBUCKET, and DEDUPLICATION parameters are not supported. The behavior is the same as that of UB-tree. The FILLFACTOR, and INDEXSPLIT parameters are supported. ACTIVE_PAGES and WAIT_CLEAN_CBI are not applicable to GSI.
- value
  Specifies the new value for an index-method-specific storage parameter. This might be a number or a word depending on the parameter.
RESET ( { storage_parameter } [, ...] )
Resets one or more index-method-specific storage parameters of an index to the default value. Similar to the SET statement, REINDEX may be used to completely update the index.
[ MODIFY PARTITION index_partition_name ] UNUSABLE
Sets the indexes on a table or index partition to be unavailable.
REBUILD [ PARTITION index_partition_name ]
Rebuilds indexes on a table or an index partition. If the index contains the lpi_parallel_method option and the value is PARTITION when the index is rebuilt, and the parallel_workers value of the index's table is greater than 0, the index cannot be rebuilt in parallel. If the index does not contain the lpi_parallel_method option or the value of the option is set to AUTO, page-level parallel index is rebuilt by default. For details, see LPI_PARALLEL_METHOD.
RENAME PARTITION index_partition_name TO new_index_partition_name
Renames an index partition.
MOVE PARTITION index_partition_name TABLESPACE new_tablespace
Modifies the tablespace to which an index partition belongs.
new_index_partition_name
Specifies the new name of the index partition.
index_partition_name
Specifies the name of an index partition.
new_tablespace
Specifies a new tablespace.
GSIVALID
Internally called by the CREATE GLOBAL INDEX CONCURRENTLY function to modify the status of distributed global secondary indexes.
GSIUSABLE
This syntax is internally called by the VACUUM FULL function to modify the status of distributed global secondary indexes.
VISIBLE
Sets the index to be visible.
INVISIBLE
Sets the index to be invisible.

Examples

Rename an index.

-- Create the test1 table and create an index for it.
gaussdb=# CREATE TABLE test1(col1 int, col2 int);
gaussdb=# CREATE INDEX aa ON test1(col1);

-- Rename index aa to idx_test1_col1.
gaussdb=# ALTER INDEX aa RENAME TO idx_test1_col1;

-- Query the index information in the test1 table.
gaussdb=# SELECT tablename,indexname,tablespace FROM pg_indexes WHERE tablename = 'test1';
 tablename |   indexname    | tablespace 
-----------+----------------+------------
 test1     | idx_test1_col1 | 
(1 row)

Change the tablespace to which the index belongs.

-- Create the tbs_index1 tablespace.
gaussdb=# CREATE TABLESPACE tbs_index1 RELATIVE LOCATION 'tablespace1/tbs_index1';

-- Change the tablespace to which the idx_test1_col1 index belongs to tbs_index1.
gaussdb=# ALTER INDEX IF EXISTS idx_test1_col1 SET TABLESPACE tbs_index1;

-- Query the index information in the test1 table.
gaussdb=# SELECT tablename,indexname,tablespace FROM pg_indexes WHERE tablename = 'test1';
 tablename |   indexname    | tablespace 
-----------+----------------+------------
 test1     | idx_test1_col1 | tbs_index1
(1 row)

Modify and reset index storage parameters.

-- View details about the idx_test1_col1 index.
gaussdb=# \di idx_test1_col1                         List of relations
 Schema |      Name      | Type  | Owner | Table | Storage 
--------+----------------+-------+-------+-------+---------
 public | idx_test1_col1 | index | omm   | test1 | 
(1 row)

-- Modify the fill factor of the idx_test1_col1 index.
gaussdb=# ALTER INDEX IF EXISTS idx_test1_col1 SET (FILLFACTOR = 70);
-- View details about the idx_test1_col1 index.
gaussdb=# \di idx_test1_col1
                         List of relations
 Schema |      Name      | Type  | Owner | Table |     Storage     
--------+----------------+-------+-------+-------+-----------------
 public | idx_test1_col1 | index | omm   | test1 | {fillfactor=70}
(1 row)

-- Reset the storage parameter of the idx_test1_col1 index.
gaussdb=# ALTER INDEX IF EXISTS idx_test1_col1 RESET (FILLFACTOR);
-- View details about the idx_test1_col1 index.
gaussdb=# \di idx_test1_col1
                     List of relations
 Schema |      Name      | Type  | Owner | Table | Storage 
--------+----------------+-------+-------+-------+---------
 public | idx_test1_col1 | index | omm   | test1 | 
(1 row)

Modify the index availability.

-- Set the idx_test1_col1 index to be unavailable.
gaussdb=# ALTER INDEX IF EXISTS idx_test1_col1 UNUSABLE;
-- Check the availability of the idx_test1_col1 index.
gaussdb=# SELECT indisusable FROM pg_index WHERE indexrelid = 'idx_test1_col1'::regclass;
 indisusable 
-------------
 f
(1 row)

-- Rebuild the idx_test1_col1 index.
gaussdb=# ALTER INDEX idx_test1_col1 REBUILD;
-- Check the availability of the idx_test1_col1 index.
gaussdb=# SELECT indisusable FROM pg_index WHERE indexrelid = 'idx_test1_col1'::regclass;
 indisusable 
-------------
 t
(1 row)

-- Delete.
gaussdb=# DROP INDEX idx_test1_col1;
gaussdb=# DROP TABLE test1;
gaussdb=# DROP TABLESPACE tbs_index1;

Rename an index partition.

-- Create the partitioned table test2.
gaussdb=# CREATE TABLE test2(col1 int, col2 int) PARTITION BY RANGE (col1)(
    PARTITION p1 VALUES LESS THAN (100),
    PARTITION p2 VALUES LESS THAN (200)
);
-- Create a partitioned index.
gaussdb=# CREATE INDEX idx_test2_col1 ON test2(col1) LOCAL(
    PARTITION p1,
    PARTITION p2
);
-- Rename the index partition.
gaussdb=# ALTER INDEX idx_test2_col1 RENAME PARTITION p1 TO p1_test2_idx;
gaussdb=# ALTER INDEX idx_test2_col1 RENAME PARTITION p2 TO p2_test2_idx;

-- Query the partition name of the idx_test2_col1 index.
gaussdb=# SELECT relname FROM pg_partition WHERE parentid = 'idx_test2_col1'::regclass;
   relname    
--------------
 p1_test2_idx
 p2_test2_idx
(2 rows)

Modify the tablespace to which an index partition belongs.

-- Create tablespaces tbs_index2 and tbs_index3.
gaussdb=# CREATE TABLESPACE tbs_index2 RELATIVE LOCATION 'tablespace1/tbs_index2';
gaussdb=# CREATE TABLESPACE tbs_index3 RELATIVE LOCATION 'tablespace1/tbs_index3';

-- Change the tablespace to which the idx_test2_col1 index partition belongs.
gaussdb=# ALTER INDEX idx_test2_col1 MOVE PARTITION p1_test2_idx TABLESPACE tbs_index2;
gaussdb=# ALTER INDEX idx_test2_col1 MOVE PARTITION p2_test2_idx TABLESPACE tbs_index3;

-- Query the tablespace to which the idx_test2_col1 index partition belongs.
gaussdb=# SELECT t1.relname index_name, 
       t2.spcname tablespace_name
FROM pg_partition t1, pg_tablespace t2
WHERE t1.parentid = 'idx_test2_col1'::regclass AND
      t1.reltablespace = t2.oid;
  index_name  | tablespace_name 
--------------+-----------------
 p1_test2_idx | tbs_index2
 p2_test2_idx | tbs_index3
(2 rows)

-- Delete.
gaussdb=# DROP INDEX idx_test2_col1;
gaussdb=# DROP TABLE test2;
gaussdb=# DROP TABLESPACE tbs_index2;
gaussdb=# DROP TABLESPACE tbs_index3;