ALTER TABLEGROUP

This topic describes how to use the ALTER TABLEGROUP statement to modify partitions in a table group.

Split a partition into multiple partitions

Use the RANGE partitioning method to split a partition

You can split a partition into multiple partitions based on the specified value ranges. 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)}]

Example:

In the tg1 table group, the p1 partition is defined by using the partition p1 values less than(20) clause. You can execute the following statement to split the p1 partition into three partitions named 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 partitions based on a range. Syntax:

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

Example: In the tg1 table group, the p1 partition is defined by using the partition p1 values less than(20) clause. You can split the p1 partition into two partitions: p11 and p12. The value range of p11 is [0,9), and the value range of p12 is [9,20). You can execute the following statement:

  ALTER TABLEGROUP tg1 split PARTITION p1 at(9) into (partition p11, partition p12)

The result of the SQL statement is the same as the result of the following statement:

  ALTER TABLEGROUP tg1 split PARTITION p1 into
  (partition p11 values less than (9),
  partition p12 values less than(20));

Use the LIST partitioning method to split a partition

If you use the LIST partitioning method, you can split a common partition or the default partition into multiple partitions.

Syntax:

ALTER TABLEGROUP tg_name split PARTITION partition_name into
(partition_definition,partition_definition, [, partition_definition] ...);

Example:

In the tg1 table group, the p1, p2, and pd partitions are defined by using the partition p1 values in(1, 2, 3, 4, 5, 6), partition p2 values in(7,8,9), partition pd values in(default) clause. You can execute the following statement to split the p1 partition into three partitions named 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))

The pd partition is the default partition. All partition key values except 1 to 9 are distributed to the default partition. PolarDB-X allows you to split the default partition. If you split the default partition, some data in the default partition is migrated to new partitions. For example, you can execute the following SQL statement to split the pd partition into three partitions named pd0, pd1, and pd2, and migrate values 10 to 15 to the pd0 and pd1 partitions:

  ALTER TABLEGROUP tg1 SPLIT PARTITION pd INTO
  (PARTITION pd0 VALUES IN (10,11,12),
  PARTITION pd1 VALUES IN (13,14,15),
  PARTITION pd2 VALUES IN (default))

You can extract and store data that corresponds to a specific value to a separate partition.Syntax:
```
ALTER TABLEGROUP identifier EXTRACT TO PARTITION pnew BY [HOT] VALUE(value_list);
```

Example:

In the tg1 table group, the p1, p2, and pd partitions are defined by using the partition p1 values in(1, 2, 3, 4, 5, 6), partition p2 values in(7,8,9), partition pd values in(default) clause. You can execute the following SQL statement to extract and store the value 2 to a separate partition named pnew:

  ALTER TABLEGROUP tg1 EXTRACT TO PARTITION pnew BY HOT VALUE(2);

Use the HASH partitioning method to split a partition

You can split a partition into two partitions based on the median hash value of the partition key values. Syntax:
```
ALTER TABLEGROUP identifier split PARTITION identifier;
```

Example:

  ALTER TABLEGROUP tg1 split PARTITION p1;

You can extract hot data and store the hot data in a separate partition.

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

Use the KEY partitioning method to split a partition

You can extract hot data and store the hot data in a separate partition. Syntax:

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

Example:

In the tg1 table group, Column A and Column B are used as partition columns. You can use the KEY partitioning method to extract data in the rows in which the value in Column A is 88 and the value in Column B is 10, and store the data in a new partition named p20.

  ALTER TABLEGROUP tg1 extract to PARTITION p20 by hot value(88,10);

You can split hot data and distribute hot data to multiple partitions. Syntax:

In the following statement, N specifies the number of partitions to which you want to distribute hot data.
```
ALTER TABLEGROUP tgName split into [newPartNamePrefix] PARTITIONS N by hot value(xxx[,..,xxx]);
```

For example, Column A and Column B are used as partition columns in a table group. You can use the KEY partitioning method to distribute hot data to multiple partitions. If a large number of data records in which the value in Column A is 88 exist, you can execute the following statement to evenly distribute the hot 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. The hot data is evenly distributed to the partitions based on the hash values of the values in Column B.

Merge partitions

Syntax:

You can merge multiple partitions into one partition.

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

Example:

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;

Example:

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

Add partitions

You can add partitions to a partition group that is partitioned by using the RANGE or LIST partitioning method.

If you add a partition named pnew to a partition group that is partitioned by using the LIST partitioning method and contains the default partition, data in the default partition that has the same definitions as the data in the pnew partition is migrated to the pnew partition. If a partition group that is partitioned by using the LIST partitioning method does not contain the default partition, you can add the default partition to the partition group. A partition group can contain only one default partition.

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

Delete partitions

You can delete partitions from a partition group that is partitioned by using the RANGE or LIST partitioning method.

ALTER TABLEGROUP tg_name DROP PARTITION partition_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 modify the values in a partition that is partitioned by using the LIST or LIST COLUMN partitioning method.

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

ALTER TABLEGROUP

ALTER TABLEGROUP

Split a partition into multiple partitions

Merge partitions

Migrate partitions

Add partitions

Delete partitions

Rename partitions

Change column values in partitions

results matching ""

No results matching ""