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 TABLE SUBPARTITION

Updated on 2023-10-23 GMT+08:00

Function

ALTER TABLE SUBPARTITION modifies partitions from a level-2 partitioned table, including adding, deleting, clearing, and splitting partitions.

Precautions

  • Currently, partitions from the level-2 partitioned table can be added, deleted, cleared, or split only.
  • The tablespace of the added partition cannot be PG_GLOBAL.
  • The name of the added partition must be different from the names of the existing level-1 and level-2 partitions in the partitioned table.
  • The key value of the added partition must be consistent with the type of partition keys in the partitioned table.
  • If a range partition is added, the key value of the added partition must be greater than the upper limit of the last range partition in the partitioned table. To add a partition to a table with the MAXVALUE partition, you are advised to use the SPLIT syntax.
  • If a list partition is added, the key value of the added partition cannot be the same as that of an existing partition. To add a partition to a table with the DEFAULT partition, you are advised to use the SPLIT syntax.
  • Hash partitions cannot be added. However, if the level-2 partition mode of an level-2 partitioned table is hash but the level-1 partition mode is not hash, you can add a level-1 partition and create the corresponding level-2 partition.
  • If the number of partitions in the target partitioned table has reached the maximum (1048575), partitions cannot be added.
  • If the partitioned table contains only one level-1 or level-2 partition, the partition cannot be deleted.
  • Hash partitions cannot be deleted.
  • Use PARTITION FOR() to choose partitions. The number of specified values in the brackets should be the same as the column number in customized partitions, and they must be consistent.
  • Only level-2 partitions (leaf nodes) can be split. Only range and list partitioning policies can be used and hash partitioning policies are not supported. The list partitioning policy can be used only when the default partition is used.
  • Only the owner of a partitioned table or users granted with the ALTER permission on the partitioned table can run the ALTER TABLE PARTITION command. The system administrator has the permission to run the command by default.
  • If the ALTER statement does not contain UPDATE GLOBAL INDEX, the original GLOBAL index is invalid. In this case, other indexes are used for query. If the ALTER statement contains UPDATEGLOBAL INDEX, the original GLOBAL index is still valid and the index function is correct.

Syntax

  • Modify the syntax of the table partition.
    ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
        action [, ... ];
    action indicates the following clauses for maintaining partitions.
        add_clause    |
        drop_clause   |    
        split_clause  |
        truncate_clause
  • The add_clause syntax is used to add one or more partitions to a specified partitioned table. The syntax can be used in level-1 partitions.
    ADD {partition_less_than_item | partition_list_item } [ ( subpartition_definition_list ) ]

    It can also be used in level-2 partitions.

    MODIFY PARTITION partition_name ADD subpartition_definition

    partition_less_than_item defines a range partition. The syntax is as follows:

    PARTITION partition_name VALUES LESS THAN ( partition_value | MAXVALUE ) [ TABLESPACE tablespacename ]
    partition_list_item defines a list partition. The syntax is as follows:
    PARTITION partition_name VALUES ( partition_value [, ...] | DEFAULT ) [ TABLESPACE tablespacename ]

    subpartition_definition_list contains the subpartition_definition object of one or more level-2 partitions. The syntax is as follows:

    SUBPARTITION subpartition_name [ VALUES LESS THAN ( partition_value | MAXVALUE ) | VALUES ( partition_value [, ...] | DEFAULT )]  [ TABLESPACE tablespace ]
    NOTICE:

    If the level-1 partition is a hash partition, you cannot use ADD to add a level-1 partition. If the level-2 partition is a hash partition, you cannot use MODIFY to add a level-2 partition.

  • The drop_clause syntax is used to remove a partition from a specified partitioned table. The syntax can be used in level-1 partitions.
    DROP PARTITION  { partition_name | FOR (  partition_value )  } [ UPDATE GLOBAL INDEX ]

    It can also be used in level-2 partitions.

    DROP SUBPARTITION  { subpartition_name | FOR (  partition_value, subpartition_value )  } [ UPDATE GLOBAL INDEX ]
    NOTICE:
    • If the level-1 partition is a hash partition, the level-1 partition cannot be deleted. If the level-2 partition is a hash partition, the level-2 partition cannot be deleted.
    • At least one sub-partition must be retained.
  • The split_clause syntax is used to split one partition into different partitions.
    SPLIT SUBPARTITION { subpartition_name} { split_point_clause  } [ UPDATE GLOBAL INDEX ]

    The split_point_clause syntax used to specify a split point in the range partitioning policy is as follows:

    AT ( subpartition_value ) INTO ( SUBPARTITION subpartition_name [ TABLESPACE tablespacename ] , SUBPARTITION subpartition_name [ TABLESPACE tablespacename ] )
    The split_point_clause syntax used to specify a split point in the list partitioning policy is as follows:
    VALUES ( subpartition_value ) INTO ( SUBPARTITION subpartition_name [ TABLESPACE tablespacename ] , SUBPARTITION subpartition_name [ TABLESPACE tablespacename ] )
    NOTICE:
    • The size of the split point should be in the range of the splitting partition key.
    • One partition can be split into only two new partitions.
    • In the range partitioning policy, the current partition is split into two partitions based on the split point. A partition smaller than the size specified by the split point is regarded as one partition, and a partition larger than the size specified by the split point is regarded as the other partition. Therefore, only one split point can be used in the range partitioning policy. In the list partitioning policy, there can be multiple but no more than 64 split points. These split points are extracted from the boundary values of the current partition as a new partition, and the remaining boundary values of the current partition are used as another new partition.
    • Only the default list partition can be split.
  • The truncate_clause syntax is used to remove a specified partition from a partitioned table.
    TRUNCATE SUBPARTITION  { subpartition_name } [ UPDATE GLOBAL INDEX ]

Parameter Description

  • table_name

    Specifies the name of a partitioned table.

    Value range: an existing partitioned table name.

  • subpartition_name

    Specifies the name of a level-2 partition name.

    Value range: an existing level-2 partition name.

  • tablespacename

    Specifies which tablespace the partition moves to.

    Value range: an existing tablespace name.

Examples

See the examples in CREATE TABLE SUBPARTITION.

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