Manual partition splitting

OceanBase Database allows you to manually split a partition of a partitioned table into multiple partitions. You can specify the partition to be split and the split points to execute the split partition command manually, and adjust the partitions based on your requirements and data growth.

Supported scenarios

OceanBase Database supports manual partition splitting for both rowstore and columnstore tables. In the current version, manual partition splitting is supported only for primary partition tables with RANGE 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 a table whose partition key and primary key prefix are different.
• 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 with 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 SPLIT PARTITION partition_name split_partition_option;

split_partition_option:
    split_at_format
    | split_into_format

split_at_format:
    AT (value) [INTO (PARTITION [split_partition_name], PARTITION [split_partition_name])]

split_into_format:
    INTO (PARTITION split_partition_name VALUES LESS THAN (value)
          [, PARTITION split_partition_name VALUES LESS THAN (value) ...]
           , PARTITION split_partition_name)

Parameters

Examples

Example 1: Manually split a partition of a rowstore table

Create a primary partition table named test_tbl1 with RANGE partitions:

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 two new partitions at the row corresponding to the value 50. After the split, the original p0 partition is divided into two new partitions, whose names are automatically assigned by the system:

  ALTER TABLE test_tbl1 SPLIT PARTITION p0 AT (50);
  

• Split the p1 partition of the test_tbl1 table into two new partitions at the row corresponding to the value 150. After the split, the original p1 partition is divided into two new partitions, one of which is named p1_1 and the other is automatically assigned a name by the system:

  ALTER TABLE test_tbl1
      SPLIT PARTITION p1 AT (150) INTO ( 
          PARTITION p1_1, 
          PARTITION);
  

• Split the p2 partition of the test_tbl1 table into two new partitions at the row corresponding to the value 250. After the split, the original p2 partition is divided into two new partitions named p2_1 and p2_2.

  ALTER TABLE test_tbl1
      SPLIT PARTITION p2 AT (250) INTO (
          PARTITION p2_1,
          PARTITION p2_2);
  

• 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 named p_max_1, p_max_2, and p_max_3:

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

• 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" NUMBER(*,0),
  "COL2" NUMBER(*,0),
  PRIMARY KEY ("COL1")
  ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
  partition by range(col1) size ('128MB')
  (partition "P8195" values less than (50),
  partition "P8196" values less than (100),
  partition "P1_1" values less than (150),
  partition "P8197" values less than (200),
  partition "P2_1" values less than (250),
  partition "P2_2" 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 partition of a columnstore table

Create a primary partition columnstore table named test2_tbl2 with RANGE partitions:

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, whose names are automatically assigned by the system:

  ALTER TABLE test2_tbl2 SPLIT PARTITION p0 AT (30);
  

• 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" NUMBER(*,0),
  "COL2" NUMBER(*,0),
  "COL3" NUMBER(*,0),
  PRIMARY KEY ("COL1")
  ) COMPRESS FOR ARCHIVE 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 "P8194" values less than (30),
  partition "P8195" 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