This topic describes how to use ALTER TABLEGROUP statements.

Split partitions in a partition group

Range partitioning

  • You can split a partition into multiple subpartitions based on the specified range.

    Syntax:

    ALTER TABLEGROUP tg_name split PARTITION identifier INTO
     (partition_definition,partition_definition, [, partition_definition] ...);
    
     partition_definition:
        PARTITION partition_name
            [VALUES
                {LESS THAN {(value_list) | MAXVALUE}
                |
                IN (value_list)}]

    Examples:

    Assume that p1 in table group tg1 is defined as partition p1 values less than(20). You can execute the following statement to split p1 into p10, p11, and p12:

    ALTER TABLEGROUP tg1 split PARTITION p1 into
    (partition p10 values less than (8),
    partition p11 values less than(15),
    partition p12 values less than(20))
  • You can split a partition into two subpartitions based on a range.

    Syntax:

    ALTER TABLEGROUP identifier split PARTITION identifier at(number) into
     (PARTITION identifier,
      PARTITION identifier);

    Examples:

    Assume that p1 in table group tg1 is defined as partition p1 values less than(20). If the p1 partition is split into two subpartitions p11 and p12 based on the value 9, the value range of p11 is [0,9) and the value range of p12 is [9,20). SQL statement:
    ALTER TABLEGROUP tg1 split PARTITION p1 at(9) into (partition p11, partition p12)
    This SQL statement is equivalent to the following statement:
    ALTER TABLEGROUP tg1 split PARTITION p1 into
    (partition p11 values less than (9),
    partition p12 values less than(20));
List partitioning
  • Syntax:
    ALTER TABLEGROUP tg_name split PARTITION partition_name into
     (partition_definition,partition_definition, [, partition_definition] ...);
  • Examples:

    Assume that p1 in tg1 is defined as partition p1 values in(1, 2, 3, 4, 5, 6). You can execute the following statement to split p1 into p10, p11, and p12:

    ALTER TABLEGROUP tg1 split PARTITION p1 into
    (partition p10 values in (1,3,5),
    partition p11 values in (2,4),
    partition p12 values in (6))
Hash partitioning
  • You can use the median value of the range of a partition key as the split point and split a partition into two subpartitions.

    Syntax:

    ALTER TABLEGROUP identifier split PARTITION identifier;

    Examples:

    ALTER TABLEGROUP tg1 split PARTITION p1;
  • You can extract hot values to a separate partition.
    ALTER TABLEGROUP tgName extract to PARTITION [newPartName] by hot value(xxx)
Key partitioning
  • You can extract hot values to a separate partition.

    Syntax:

    ALTER TABLEGROUP tgName extract to PARTITION [newPartName] by hot value(xxx[,..,xxx]);

    Examples:

    Assume that the shard key is set to (a,b) and key partitioning is used. Extract data whose shard key is set to (a=88,b=10) to a separate new partition p20.

    ALTER TABLEGROUP tg1 extract to PARTITION p20 by hot value(88,10);
  • You can use the hashing algorithm to distribute hot data to multiple partitions.

    Syntax:

    N specifies that hot values are distributed to the specified number of partitions by using the hashing algorithm.

    ALTER TABLEGROUP tgName split into [newPartNamePrefix] PARTITIONS N by hot value(xxx[,..,xxx]);

    Assume that the shard key is set to (a,b) and key partitioning is used. Assume that multiple data records meet the a=88 condition. Execute the following statement to use the hashing algorithm to evenly distribute the data to five partitions: ALTER TABLEGROUP tgName split into hot88_ PARTITIONS 5 by hot value(88). The five partitions are hot88_1, hot88_2, hot88_3, hot88_4, and hot88_5.

  • Table-level operations are supported. N specifies that hot values are distributed to the specified number of partitions by using the hashing algorithm.
    ALTER tableName split into [newPartNamePrefix] PARTITIONS N by hot value(xxx[,..,xxx]);
    Note To perform table-level operations, make sure that the table group to which the current table belongs contains only the current table.

Merge partitions

Syntax:

You can merge multiple partitions into one partition.

ALTER TABLEGROUP tg_name MERGE PARTITIONS partition_name,...,partition_name TO partition_name;

Examples:

ALTER TABLEGROUP tbl_tg MERGE PARTITIONS p2,p3 to p23;

Migrate partitions

Syntax:

You can migrate a partition to a specific data node.

ALTER TABLEGROUP tg_name MOVE PARTITIONS partition_name,...,partition_name TO dn_id;

Examples:

ALTER TABLEGROUP tg_name MOVE PARTITIONS p2,p4 to 'dn-0' ;

Add partitions

If a partition is split into a group of partitions by range or list, you can add partitions to the partition group.

ALTER TABLEGROUP tg_name ADD PARTITION (partition_definition [,partition_definition] ...)

Delete partitions

If a partition is split into a group of partitions by range or list, you can delete partitions from the partition group.

ALTER TABLEGROUP tg_name DROP PARTITION partitiion_name [,partition_name] ...

Rename partitions

ALTER TABLEGROUP tg_name RENAME PARTITION old_part_name to new_part_name[, old_part_name to new_part_name]

Change column values in partitions

You can change the values in the partitions that are generated by list partitioning or list column partitioning.

ALTER TABLEGROUP tg_name MODIFY PARTITION partition_name ADD/DROP VALUES (value_list)