ALTER INDEX

Description

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, a 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;

  

• Set the table index to be unavailable.

  ALTER INDEX [ IF EXISTS ] index_name UNUSABLE;

• Rename an index partition.

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

  

• Change the storage parameter of a table index.

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

Parameters

• index_name

  Specifies the 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 complying with Identifier Description.

• RENAME PARTITION index_partition_name TO new_index_partition_name

  Renames an index partition.

• new_index_partition_name

  Specifies the new name of the index partition.

• index_partition_name

  Specifies the name of an index partition.

• SET ( {storage_parameter = value} [, ... ] )

  Changes one or more index-method-specific storage parameters. 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 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 } [, ...] )

  Reset one or more index-method-specific storage parameters to the default value. Similar to the SET statement, REINDEX may be used to completely update the index.

Examples

• Rename an index.

  -- Create the test1 table and create an index for it.
  m_db=# CREATE TABLE test1(col1 INT, col2 INT);
  m_db=# CREATE INDEX aa ON test1(col1);
  
  -- Rename index aa to idx_test1_col1.
  m_db=# ALTER INDEX aa RENAME TO idx_test1_col1;
  
  -- Query the index information in the test1 table.
  m_db=# SELECT tablename,indexname,tablespace FROM pg_indexes WHERE tablename = 'test1';
   tablename |   indexname    | tablespace 
  -----------+----------------+------------
   test1     | idx_test1_col1 | 
  (1 row)

• Rename an index partition.

  -- Create the partitioned table test2.
  m_db=# 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.
  m_db=# CREATE INDEX idx_test2_col1 ON test2(col1) LOCAL(
      PARTITION p1,
      PARTITION p2
  );
  -- Rename the index partition.
  m_db=# ALTER INDEX idx_test2_col1 RENAME PARTITION p1 TO p1_test2_idx;
  m_db=# ALTER INDEX idx_test2_col1 RENAME PARTITION p2 TO p2_test2_idx;
  
  -- Query the partition name of the idx_test2_col1 index.
  m_db=# SELECT relname FROM pg_partition WHERE parentid = 'idx_test2_col1'::regclass;
     relname    
  --------------
   p1_test2_idx
   p2_test2_idx
  (2 rows)

Helpful Links

CREATE INDEX, DROP INDEX, and REINDEX