Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive

ALTER INDEX

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

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;
    NOTE:

    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;
    NOTE:

    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;
    NOTE:
    • 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;
    NOTE:
    • 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

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback