alter_table_stmt:ALTER TABLE table_name USING PARTITION POLICY partition_policy_name| ALTER TABLE table_name DROP PARTITION POLICY_ [FORCE]| ALTER TABLE table_name STORAGE_TIER [=] {AUTO_STORAGE | LOCAL_STORAGE | OBJECT_STORAGE}| ALTER TABLE table_name MODIFY PARTITION (partition_name [, partition_name ...]) STORAGE_TIER [=] {AUTO_STORAGE | LOCAL_STORAGE | OBJECT_STORAGE}| ALTER TABLE table_name DROP PARTITION partition_name [, partition_name ...] [UPDATE GLOBAL INDEXES]| ALTER TABLE table_name TRUNCATE PARTITION {ALL | partition_name [, partition_name ...]} [UPDATE GLOBAL INDEXES]
Parameter | Required | Description |
ALTER TABLE table_name USING PARTITION POLICY partition_policy_name | Optional | Modify the policy for partition affinity bound to the table, where table_name and partition_policy_name need to be explicitly specified. |
ALTER TABLE table_name DROP PARTITION POLICY_ [FORCE] | Optional | Unbind the policy for partition affinity bound to the table. The table_name must be explicitly specified.FORCE option:If the parameter is specified, after a successful execution, the current table will no longer be bound to any affinity policy. If not specified, the system will attempt to unbind the currently bound explicit policy for affinity from the table and then bind it to the potential implicit policy for affinity. |
ALTER TABLE table_name STORAGE_TIER [=] | Optional | Modifies the storage tier at the table level, which is used to declare the storage location of data objects in hot/cold tiered storage. The values are as follows: AUTO_STORAGE: automatic mode, where the storage location of data objects is automatically determined by the system based on the hot/cold tiering policy and the instance's default storage tier. LOCAL_STORAGE: local storage, where data objects are stored on local disks. It is suitable for hot data and offers low access latency.OBJECT_STORAGE: COS, where data objects are stored. It is suitable for archiving cold data and offers low storage costs.The modification only affects the storage location of subsequently generated data objects. The migration of existing data objects is asynchronously executed by the system according to the hot/cold tiering policy. |
ALTER TABLE table_name MODIFY PARTITION (partition_name [, partition_name ...]) STORAGE_TIER [=] | Optional | Modifies the storage tier of specified partitions, supporting the specification of one or multiple partitions at a time. Partition-level declarations take precedence over table-level ones. Typical scenarios: archive historical partitions to COS to reduce storage costs, or move hot partitions back to local storage to reduce access latency. Notes: The partition_name must be the name of an existing partition and is only supported for explicit partition tables.After the modification, only the storage location of subsequently newly generated data objects is affected. The migration of existing data objects is asynchronously executed by the system according to the hot/cold tiering policy. |
ALTER TABLE table_name DROP PARTITION ... [UPDATE GLOBAL INDEXES] ALTER TABLE table_name TRUNCATE PARTITION ... [UPDATE GLOBAL INDEXES] | Optional | While executing DROP PARTITION or TRUNCATE PARTITION, synchronously rebuilds the Global Secondary Index (GSI) on the table, ensuring that the GSI remains available after the DDL operation is completed.If the UPDATE GLOBAL INDEXES clause is not specified, after the partition operation is completed, all global secondary indexes of the affected tables will be marked as unavailable. Subsequent queries cannot use the related indexes, including the FORCE INDEX hint. To restore their usage, you need to reindex or recreate the indexes separately.Specifying the UPDATE GLOBAL INDEXES clause: During DDL execution, global secondary indexes are rebuilt synchronously. After completion, the global secondary indexes remain available, and their data stays consistent with the actual rows in the table.This clause only applies to global secondary indexes and does not affect local indexes. If no global secondary indexes exist on the table, this clause will not have any additional effect. |
# Create policy for partition affinity pp1tdsql [demo]> create partition policy pp1 partition by hash(int) partitions 4;Query OK, 0 rows affected (0.02 sec)# Create table t with 4 hash partitionstdsql [demo]> create table t(id INT) partition by hash(id) partitions 4;Query OK, 0 rows affected (0.90 sec)# Bind table t to the policy for partition affinity pp1tdsql [demo]> alter table t using partition policy pp1;Query OK, 0 rows affected (0.64 sec)# Unbind the partition affinity of table t. After the unbinding is complete, table t will be automatically bound to the corresponding policy for implicit affinity.tdsql [demo]> alter table t drop partition policy;Query OK, 0 rows affected (0.66 sec)# Completely unbind the policy for partition affinity of table t. Once completed, table t will not be bound to any policy for partition affinity.tdsql [demo]> alter table t drop partition policy force;Query OK, 0 rows affected (0.61 sec)
tdsql [demo]> ALTER TABLE t1 STORAGE_TIER = LOCAL_STORAGE;Query OK, 0 rows affected
# Archive partition p1 to COStdsql [demo]> ALTER TABLE t1 MODIFY PARTITION (p1) STORAGE_TIER = OBJECT_STORAGE;Query OK, 0 rows affected# Archive multiple historical partitions to COS in a single operationtdsql [demo]> ALTER TABLE t1 MODIFY PARTITION (p0, p1) STORAGE_TIER = OBJECT_STORAGE;Query OK, 0 rows affected
# Create a partition table with a global secondary indextdsql [demo]> CREATE TABLE orders (id INT NOT NULL,user_id INT NOT NULL,created_date DATE NOT NULL,PRIMARY KEY (id, created_date),INDEX gsi_user (user_id) GLOBAL) PARTITION BY RANGE (TO_DAYS(created_date)) (PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')));Query OK, 0 rows affected# Delete the specified partition and synchronously rebuild the global secondary index. After the DDL operation is complete, gsi_user remains available.tdsql [demo]> ALTER TABLE orders DROP PARTITION p202401 UPDATE GLOBAL INDEXES;Query OK, 0 rows affected# Clear the specified partition and synchronously rebuild the global secondary indextdsql [demo]> ALTER TABLE orders TRUNCATE PARTITION p202402 UPDATE GLOBAL INDEXES;Query OK, 0 rows affected# When UPDATE GLOBAL INDEXES is not specified, gsi_user will be marked as unavailable.tdsql [demo]> ALTER TABLE orders DROP PARTITION p202403;Query OK, 0 rows affected, 1 warning
Apakah halaman ini membantu?
Anda juga dapat Menghubungi Penjualan atau Mengirimkan Tiket untuk meminta bantuan.
masukan