Manual partition splitting

OceanBase Database allows you to manually split partitions in a partitioned table. This means that you can manually execute the partition splitting command to specify the partitions to be split and the split points.

Supported scenarios for manual partition splitting

The current version supports manual partition splitting only for primary partitions of range-partitioned tables.

Limitations and restrictions

• Manual partition splitting is not supported for list-partitioned or hash-partitioned tables.
• Manual partition splitting is not supported for subpartitions.
• The partitioning key of a manually split table cannot be 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 columnstore tables.
• Manual partition splitting is not supported when the table is in a table group that contains multiple tables.
• 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.
• Local indexes and partitions of LOB tables cannot be split separately.

Syntax for manual partition splitting

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

Create a primary partitioned table named test_tbl1 for range-partitioned tables.

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 split into two new partitions, and the names of the new partitions are automatically generated 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 split into two new partitions, one of which is named p1_1, and the other is automatically generated 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 split 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 split 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 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" 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
  

References

ALTER TABLE