Manual partition splitting

OceanBase Database allows you to manually split partitions in a partitioned table. This means you can execute the partition splitting command to specify the partitions to be split and the split points. This feature enables you to adjust partitions based on your needs and data growth.

Supported scenarios

The current version supports manual partition splitting only for primary partitions of tables that are partitioned by RANGE or RANGE COLUMNS. Additionally, only one primary partition can be split into multiple partitions at a time.

Limitations and considerations

• Manual partition splitting is not supported for tables partitioned by LIST or HASH.
• Manual partition splitting is not supported for tables with subpartitions.
• The partitioning key of a manually split table must match the prefix of the primary key.
• Manual partition splitting is not supported for tables without a primary key.
• Manual partition splitting is not supported for columnstore tables.
• If the table is in a TABLEGROUP (table group) that contains multiple tables, manual partition splitting is not supported. However, if the TABLEGROUP contains only the table being split, manual partition splitting is supported.
• Manual partition splitting is not supported for materialized views.
• Manual partition splitting is not supported for global indexes.
• Manual partition splitting is not supported for full-text indexes.
• Manual partition splitting is not supported for GIS indexes.
• Manual partition splitting is not supported for vector indexes.
• Manual partition splitting is not supported for local indexes or partitions of LOB tables.

Syntax

ALTER TABLE table_name REORGANIZE PARTITION partition_name INTO (
        PARTITION split_partition_name VALUES LESS THAN (value)
        [, PARTITION split_partition_name VALUES LESS THAN (value) ...]
         , PARTITION split_partition_name VALUES LESS THAN (source_value));

Parameters

Examples

Create a primary partitioned table named test_tbl1 that is partitioned by RANGE.

CREATE TABLE test_tbl1(col1 INT, col2 INT, PRIMARY KEY(col1))
    PARTITION BY RANGE(col1)
    (PARTITION p0 VALUES LESS THAN(100),
     PARTITION p1 VALUES LESS THAN(200),
     PARTITION p2 VALUES LESS THAN(300),
     PARTITION p_max VALUES LESS THAN (MAXVALUE));

• Split the p0 partition of the test_tbl1 table into three new partitions. The split points are the rows corresponding to the values 30 and 60. After the splitting, the original p0 partition is split into three new partitions named p0_1, p0_2, and p0.

  ALTER TABLE test_tbl1
      REORGANIZE PARTITION p0 INTO (
          PARTITION p0_1 VALUES LESS THAN (30),
          PARTITION p0_2 VALUES LESS THAN (60),
          PARTITION p0 VALUES LESS THAN (100));
  

• Split the p_max partition of the test_tbl1 table into three new partitions. The split points are the rows corresponding to the values 400 and 500. After the splitting, the original p_max partition is split into three new partitions named p_max_1, p_max_2, and p_max_3.

  ALTER TABLE test_tbl1
      REORGANIZE PARTITION p_max INTO (
          PARTITION p_max_1 VALUES LESS THAN (400),
          PARTITION p_max_2 VALUES LESS THAN (500),
          PARTITION p_max_3 VALUES LESS THAN (MAXVALUE));
  

• View the schema and definition of the test_tbl1 table.

  SHOW CREATE TABLE test_tbl1;
  

  The return result is as follows:

  +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
  +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | test_tbl1 | CREATE TABLE `test_tbl1` (
  `col1` int(11) NOT NULL,
  `col2` int(11) DEFAULT NULL,
  PRIMARY KEY (`col1`)
  ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
  partition by range(col1) size ('128MB')
  (partition `p0_1` values less than (30),
  partition `p0_2` values less than (60),
  partition `p0` values less than (100),
  partition `p1` values less than (200),
  partition `p2` values less than (300),
  partition `p_max_1` values less than (400),
  partition `p_max_2` values less than (500),
  partition `p_max_3` values less than (MAXVALUE)) |
  +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set
  

References

ALTER TABLE