Manual partition splitting

OceanBase Database allows you to manually split partitions in a partitioned table. This feature enables you to specify the partition to be split and the split points to execute the split operation.

Supported scenarios

Both rowstore and columnstore tables in OceanBase Database support manual partition splitting. Currently, only range and range columns partitions of a primary partitioned table can be manually split, and only one partition can be split into multiple partitions.

Limitations and considerations

• Manual partition splitting is not supported for List or Hash partitioned tables.
• Manual partition splitting is not supported for subpartitioned tables.
• Manual partition splitting is not supported for tables where the partition key prefix is different from the primary key prefix.
• Manual partition splitting is not supported for tables without a primary key.
• Manual partition splitting is not supported for heap-organized tables.
• Manual partition splitting is not supported for columnstore replicas.
• Manual partition splitting is not supported for tables containing columnstore local indexes.
• Manual partition splitting is not supported for tables in a TABLEGROUP (table group) that contains multiple tables. If the TABLEGROUP contains only one table, 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 spatial indexes.
• Manual partition splitting is not supported for vector indexes.
• Manual partition splitting is not supported for local indexes or 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

Example 1: Manually split a rowstore partition

Create a primary partitioned table test_tbl1 with a range partition:

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 at the rows corresponding to the values 30 and 60. After the split, the original p0 partition is divided into three new partitions: 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 at the rows corresponding to the values 400 and 500. After the split, the original p_max partition is divided into three new partitions: 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 structure and definition of the test_tbl1 table:

  SHOW CREATE TABLE test_tbl1;
  

  The returned 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
  

Example 2: Manually split a columnstore partition

Create a primary partitioned columnstore table test2_tbl2 with a range partition:

obclient> CREATE TABLE test2_tbl2(col1 INT, col2 INT, col3 INT, PRIMARY KEY(col1))
    PARTITION BY RANGE(col1)
    (PARTITION p0 VALUES LESS THAN(50),
     PARTITION p1 VALUES LESS THAN(100),
     PARTITION p_max VALUES LESS THAN (MAXVALUE))
    WITH COLUMN GROUP (each column);

• Split the p0 partition of the test2_tbl2 table into two new partitions at the row corresponding to the value 30. After the split, the original p0 partition is divided into two new partitions: p0_1 and p0_2:

  obclient> ALTER TABLE test2_tbl2
      REORGANIZE PARTITION p0 INTO (
          PARTITION p0_1 VALUES LESS THAN (30),
          PARTITION p0_2 VALUES LESS THAN (50));
  

• View the structure and definition of the test2_tbl2 table:

  obclient> SHOW CREATE TABLE test2_tbl2;
  

  The returned result is as follows:

  +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
  +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | test2_tbl2 | CREATE TABLE `test2_tbl2` (
  `col1` int(11) NOT NULL,
  `col2` int(11) DEFAULT NULL,
  `col3` int(11) DEFAULT NULL,
  PRIMARY KEY (`col1`)
  ) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
  partition by range(`col1`)
  (partition `p0_1` values less than (30),
  partition `p0_2` values less than (50),
  partition `p1` values less than (100),
  partition `p_max` values less than (MAXVALUE)) WITH COLUMN GROUP(each column) |
  +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set
  

References

ALTER TABLE