Updated on 2024-06-03 GMT+08:00

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, system administrators have 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;

    The centralized system does not support distributed global secondary indexes. Therefore, this syntax is not supported.

  • Set the distributed global secondary index to be available.
    ALTER INDEX [ IF EXISTS ] index_name GSIUSABLE;

    The centralized system does not support distributed global secondary indexes. Therefore, this syntax is not supported.

  • Set the index to be visible.
    ALTER INDEX [ IF EXISTS ] index_name VISIBLE;
    • 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;
    • 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

    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.

    • 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. The centralized system does not support distributed global secondary indexes. Therefore, this syntax is not supported.

  • GSIUSABLE

    Internally called by the VACUUM FULL function to modify the status of distributed global secondary indexes. The centralized system does not support distributed global secondary indexes. Therefore, this syntax is not supported.

  • 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 | {storage_type=USTORE}
    (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 | {storage_type=USTORE,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 | {storage_type=USTORE}
    (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;

Helpful Links

CREATE INDEX, DROP INDEX, and REINDEX