OceanBase AI Database allows you to manually split partitions in a partitioned table, that is, splitting an existing partition into multiple partitions. You can specify the partition to be split and the split points for the new partitions when you run the manual partition-splitting command, so that you can adjust partitions based on your requirements and data growth.
Support for manual partition splitting
Both rowstore and columnstore tables in OceanBase AI Database support manual partition splitting. The current version supports manual partition splitting only for primary partitioned tables that use RANGE or RANGE COLUMNS partitioning, and only allows splitting one partition into multiple partitions.
Limitations and considerations
- You cannot manually split a LIST or HASH partitioned table.
- You cannot manually split a subpartitioned table.
- The partitioning key of the table being manually split must have the same prefix as its primary key.
- You cannot manually split a table without a primary key.
- You cannot manually split a heap-organized table.
- You cannot manually split a columnstore replica.
- You cannot manually split a table that contains a columnstore local index.
- If the
TABLEGROUPcontaining the table has multiple tables, manual partition splitting is not supported; if theTABLEGROUPcontains only this table, manual partition splitting is supported. - You cannot manually split a materialized view.
- You cannot manually split a global index.
- You cannot manually split a full-text index.
- You cannot manually split a spatial index.
- You cannot manually split a vector index.
- You cannot separately split the partition of a local index or a LOB table.
Syntax for manual partition splitting
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
Parameter |
Description |
|---|---|
| table_name | The name of the table. |
| partition_name | The name of the partition to be split. |
| split_partition_name | The name of the new partition. |
| value | The split point of the partition (the value range).
NoticeWhen manually splitting a RANGE partition, ensure that the |
| source_value | The original boundary of the partition to be split (the value range). That is, it defines the split of the last partition, and its value is the same as the value of the original partition. |
Examples
Example 1: Manually split a rowstore partition
Create a primary partitioned table 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
p0partition of thetest_tbl1table into three new partitions at the rows corresponding to the values30and60. After splitting, the originalp0partition is divided into three new partitions:p0_1,p0_2, andp0.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_maxpartition of thetest_tbl1table into three new partitions at the rows corresponding to the values400and500. After splitting, the originalp_maxpartition is divided into three new partitions:p_max_1,p_max_2, andp_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_tbl1table.SHOW CREATE TABLE test_tbl1;The 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 columnstore table 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
p0partition of thetest2_tbl2table into two new partitions at the row corresponding to the value30. After the split, the original partitionp0is divided into two new partitionsp0_1andp0_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_tbl2table.obclient> SHOW CREATE TABLE test2_tbl2;The 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