In OceanBase AI Database, automatic partition splitting rules are predefined. When data grows to a certain level, the database automatically splits partitions. This enables database tables to scale out automatically as data increases, thereby avoiding load imbalance and performance issues caused by excessive data volume in a single partition.
Support for automatic partition splitting
Both rowstore and columnstore tables in OceanBase AI Database support automatic partition splitting. The current version supports automatic partition splitting only for primary key tables that use RANGE or RANGE COLUMNS partitioning.
Limitations and considerations
- Automatic splitting is not supported for LIST or HASH partitioned tables.
- Automatic splitting is not supported for subpartitioned tables.
- The partitioning key of a table undergoing automatic splitting must have the same prefix as its primary key.
- Automatic partition splitting is not supported for tables without a primary key.
- Automatic partition splitting is not supported for heap-organized tables.
- Automatic partition splitting for columnstore replicas is not supported.
- Automatic partition splitting is not supported for tables containing local columnstore indexes.
- If a table belongs to a
TABLEGROUPthat contains multiple tables, automatic partition splitting is not supported; if theTABLEGROUPcontains only this one table, automatic partition splitting is supported. - Automatic partition splitting for materialized views is not supported.
- Automatic splitting of full-text indexes is not supported.
- Automatic splitting of spatial indexes is not supported.
- Automatic splitting of vector indexes is not supported.
- OceanBase AI Database supports the global_index_auto_split_policy configuration item, which controls whether automatic partitioning is enabled for newly created global indexes. If automatic partitioning is enabled for a new global index, the partitioning rules are regenerated based on the data volume, that is, pre-splitting occurs.
Table-level automatic partition splitting
You can configure automatic partitioning at the table level by using the SIZE clause when you execute the CREATE TABLE statement.
Syntax for creating a table
CREATE TABLE table_name (column_definition_list, PRIMARY KEY(column_name_list))
PARTITION BY RANGE [COLUMNS]([column_name_list]) [SIZE('size_value')]
[(range_partition_list)]
[table_column_group_option];
column_definition_list:
column_definition[, column_definition ...]
column_name_list:
column_name[, column_name ...]
range_partition_list:
PARTITION partition_name VALUES LESS THAN (value_list)[, PARTITION partition_name VALUES LESS THAN (value_list) ...]
value_list:
value[, value ...]
table_column_group_option:
WITH COLUMN GROUP(all columns)
| WITH COLUMN GROUP(each column)
| WITH COLUMN GROUP(all columns, each column)
Parameters
Parameter |
Description |
|---|---|
| table_name | The name of the table. |
| column_definition_list | The list of column definitions, including column names, data types, and constraints. For more information, see CREATE TABLE. |
| PRIMARY KEY | The primary key of the table. |
| PARTITION BY RANGE [COLUMNS]([column_name_list]) | The pre-partitioning key. When automatic partition splitting is triggered, the specified pre-partitioning key is used as the actual partitioning key (if column_name_list is not specified, the primary key is used as the partitioning key).
Notice
|
| SIZE('size_value') | (Optional) The threshold for partition splitting, which ranges from 1 MB to +∞. If the SIZE() clause is not specified, the value of the auto_split_tablet_size parameter at the tenant level is used as the default value. size_value can be set to unlimited, which indicates that partition splitting is not triggered and no new automatic partition splitting task is scheduled.
NoticeIn scenarios with large amounts of table data, do not set |
| range_partition_list | (Optional) Predefined partitioning positions (partitioning rules).
NoticeIf you specify predefined partitioning positions, you must specify the pre-partitioning key. Otherwise, an error is returned. |
| table_column_group_option | (Optional) The storage format options of the table. The details are as follows:
|
Syntax for modifying a table
ALTER TABLE table_name
PARTITION BY RANGE [COLUMNS]([column_name_list]) [SIZE('size_value')]
[(range_partition_list)];
column_name_list:
column_name[, column_name ...]
range_partition_list:
PARTITION partition_name VALUES LESS THAN (value_list)[, PARTITION partition_name VALUES LESS THAN (value_list) ...]
value_list:
value[, value ...]
Note
If no partitioning rule is defined when using the ALTER TABLE ... PARTITION BY ... statement, the table's automatic partition attribute will be modified using Online DDL, without affecting the original partition definition.
Parameters
Parameter |
Description |
|---|---|
| column_name_list | The pre-partitioning key of a non-partitioned table. When you execute the ALTER TABLE statement to modify the automatic partition splitting attribute of a non-partitioned table, if the pre-partitioning key is specified in the RANGE() clause, the corresponding record in the schema is modified; otherwise, the original setting is retained.
NoticeIf you specify multiple columns as the pre-partitioning key, you must use |
| SIZE('size_value') | (Optional) The threshold for partition splitting. The value ranges from 1 MB to +∞. When size_value is set to unlimited, partition splitting is not triggered, and no new automatic partition splitting task is scheduled.
NoticeIn scenarios where the table contains a large amount of data, we recommend that you do not set |
| range_partition_list | (Optional) The partitioning rules. If you do not specify the SIZE clause but specify partitioning definitions, only the partitioning rules are modified, and the existing automatic partition splitting definitions are retained. |
Tenant-level automatic partition splitting
You can configure the enable_auto_split and auto_split_tablet_size parameters at the tenant level to control automatic partition splitting for tables that meet the criteria within a tenant. If the SIZE clause is omitted when creating a table, the system determines whether the table will use automatic partitioning based on the following tenant-level parameters (by default, the primary key column is used as the partitioning key):
- enable_auto_split: A tenant-level parameter that specifies whether to enable automatic partition splitting for the tenant.
- auto_split_tablet_size: A tenant-level parameter that specifies the threshold for triggering automatic partition splitting after automatic partition splitting is enabled for the tenant.
If you omit the SIZE clause and set enable_auto_split to true, automatic partition splitting is enabled only for tables that meet the partitioning criteria. It does not take effect on tables that do not meet the criteria. For example, when you create a subpartitioned table, a table that is not partitioned by RANGE, a table without a primary key, or a table whose partitioning key does not have the same prefix as the primary key, the table is created as a non-automatic partitioned table (if you manually specify special DDL statements for automatic partition splitting, an error is returned).
Note
You can query the automatic partition splitting attributes of a table by using the information_schema.TABLES view or the oceanbase.CDB_TABLES view in the sys tenant.
Automatic partition splitting behavior
DDL operations for automatic partition splitting
- Modify partitioning rules: Only changes to partitioning rules that use RANGE partitioning by primary key are supported. Other partitioning rule modifications are not allowed.
- Modify the primary key: The primary key can be modified only if the automatic partitioning key remains a prefix of the primary key.
- Column operations: The restrictions on pre-partitioning keys are the same as those on partitioning keys.
Note
The restrictions on other DDL operations are the same as those on non-automatic partitioned tables.
DML behavior
During partition splitting, the source and destination schemas coexist, and DML operations on both partitions are treated as operating on the same dataset. Specifically, writes and queries on the source partition are redirected to the corresponding destination partition, while writes and queries on the destination partition account for forwarded writes from the source partition. The following table outlines the behavior of DML operations in different scenarios.
Scenario |
DML on the source partition (INSERT INTO t1 PARTITION(p0) ...) |
DML involving the source partition (SELECT * FROM t1) |
DML on the destination partition |
DML involving the destination partition |
|---|---|---|---|---|
| Before the partition splitting transaction is committed | Normal | Normal | Error. The behavior is the same as that for operations on non-existent partitions. | Not applicable. |
| During partition splitting | May result in an error. The behavior is the same as that for operations on non-existent partitions. | No error. The system retries write and query statements that fail due to stream switching. | Normal | Normal |
| After the partition splitting transaction is committed | Error. The behavior is the same as that for operations on non-existent partitions. | Not applicable. | Normal | Normal |
Examples
Example 1: Create a table-level auto-partitioned table
Create an auto-partitioned rowstore table
Disable automatic partitioning.
ALTER SYSTEM SET enable_auto_split = FALSE;Note
You can execute
SHOW PARAMETERS LIKE 'enable_auto_split';to check whether automatic partitioning is enabled.Create an auto-partitioned table named
test1_tbl1. Do not specify a pre-partitioning key, but set the split threshold to 10 GB.CREATE TABLE test1_tbl1 (col1 INT, col2 INT, col3 VARCHAR(30), PRIMARY KEY(col1, col2)) PARTITION BY RANGE() SIZE('10GB');Create an auto-partitioned table named
test1_tbl2. Specifycol1as the pre-partitioning key and set the split threshold to 10 GB.CREATE TABLE test1_tbl2 (col1 INT, col2 INT, col3 VARCHAR(30), PRIMARY KEY(col1, col2)) PARTITION BY RANGE(col1) SIZE('10GB');Create an auto-partitioned table named
test1_tbl3. Do not specify a pre-partitioning key or a split threshold (the default value ofauto_split_tablet_sizewill be used).CREATE TABLE test1_tbl3 (col1 INT, col2 INT, col3 VARCHAR(30), PRIMARY KEY(col1, col2)) PARTITION BY RANGE();When creating an auto-partitioned table named
test1_tbl4, specifycol1, col2as the pre-partitioning keys and set the split threshold to 10 GB. Also, pre-create the partitioning pointsP0,P1,P2, andP3.CREATE TABLE test1_tbl4 (col1 INT, col2 INT, col3 VARCHAR(30), PRIMARY KEY(col1, col2)) PARTITION BY RANGE COLUMNS(col1, col2) SIZE('10GB') (PARTITION P0 VALUES LESS THAN (100, MAXVALUE), PARTITION P1 VALUES LESS THAN (500, MAXVALUE), PARTITION P2 VALUES LESS THAN (800, MAXVALUE), PARTITION P3 VALUES LESS THAN (MAXVALUE, MAXVALUE));Query the auto-partitioning attributes of the table.
SELECT TABLE_SCHEMA, TABLE_NAME, AUTO_SPLIT, AUTO_SPLIT_TABLET_SIZE FROM information_schema.TABLES WHERE TABLE_NAME LIKE "test1%" ORDER BY TABLE_NAME;The returned result is as follows:
+--------------+------------+------------+------------------------+ | TABLE_SCHEMA | TABLE_NAME | AUTO_SPLIT | AUTO_SPLIT_TABLET_SIZE | +--------------+------------+------------+------------------------+ | db_test | test1_tbl1 | TRUE | 10737418240 | | db_test | test1_tbl2 | TRUE | 10737418240 | | db_test | test1_tbl3 | TRUE | 134217728 | | db_test | test1_tbl4 | TRUE | 10737418240 | +--------------+------------+------------+------------------------+ 4 rows in set
Create an auto-partitioned columnstore table
Create an auto-partitioned columnstore table named
test1_cg1. Do not specify a pre-partitioning key, but set the split threshold to 10 GB.obclient> CREATE TABLE test1_cg1 (col1 INT, col2 INT, col3 VARCHAR(30), PRIMARY KEY(col1)) PARTITION BY RANGE() SIZE('10GB') WITH COLUMN GROUP (each column);Create an auto-partitioned hybrid rowstore-columnstore table named
test1_cg2. Do not specify a pre-partitioning key, but set the split threshold to 10 GB.obclient> CREATE TABLE test1_cg2 (col1 INT, col2 INT, col3 VARCHAR(30), PRIMARY KEY(col1)) PARTITION BY RANGE() SIZE('10GB') WITH COLUMN GROUP (all columns, each column);
Example 2: Modify the auto-partitioning attributes of a table
Create an auto-partitioned table named
test2_tbl1, specifying the pre-partitioning key ascol1and the split threshold as 10 GB.CREATE TABLE test2_tbl1 (col1 INT, col2 INT, col3 VARCHAR(30), PRIMARY KEY(col1, col2)) PARTITION BY RANGE(col1) SIZE('10GB');Modify the auto-partitioning threshold for table
test2_tbl1to 1 GB.ALTER TABLE test2_tbl1 PARTITION BY RANGE() SIZE('1GB');or
ALTER TABLE test2_tbl1 PARTITION BY RANGE(col1) SIZE('1GB');Create an auto-partitioned table named
test2_tbl2, specifying the pre-partitioning key ascol1and the split threshold as 10 GB. Also, pre-create the partitioning pointsp0(for rows with values less than 100),p1(for rows with values greater than or equal to 100 and less than 200), andp_max(for rows greater than or equal to 200).CREATE TABLE test2_tbl2 (col1 INT, col2 INT, col3 VARCHAR(30), PRIMARY KEY(col1, col2)) PARTITION BY RANGE(col1) SIZE('10GB') (PARTITION p0 VALUES LESS THAN(100), PARTITION p1 VALUES LESS THAN(200), PARTITION p_max VALUES LESS THAN (MAXVALUE));Modify the auto-partitioning threshold for table
test2_tbl2to 5 GB, and change the pre-created partitioning points top0(for rows with values less than 300) andp_max(for rows greater than or equal to 300).ALTER TABLE test2_tbl2 PARTITION BY RANGE(col1) SIZE('5GB') (PARTITION p0 VALUES LESS THAN(300), PARTITION p_max VALUES LESS THAN (MAXVALUE));Create an auto-partitioned table named
test2_tbl3, specifying the pre-partitioning key ascol1and the split threshold as 10 GB.CREATE TABLE test2_tbl3 (col1 INT, col2 INT, col3 VARCHAR(30), PRIMARY KEY(col1, col2)) PARTITION BY RANGE(col1) SIZE('10GB');Disable auto-partitioning for table
test2_tbl3. This modification does not affect existing partitions, and no new auto-partitions will be scheduled for splitting.ALTER TABLE test2_tbl3 PARTITION BY RANGE() SIZE('unlimited');Create an auto-partitioned table named
test2_tbl4, specifying the pre-partitioning key ascol1and the split threshold as 10 GB.CREATE TABLE test2_tbl4 (col1 INT, col2 INT, col3 VARCHAR(30), PRIMARY KEY(col1, col2)) PARTITION BY RANGE(col1) SIZE('10GB');Change the pre-partitioning key for table
test2_tbl4fromcol1tocol1,col2.ALTER TABLE test2_tbl4 PARTITION BY RANGE COLUMNS(col1,col2) SIZE('10GB');Create a non-partitioned table named
test2_tbl5.CREATE TABLE test2_tbl5 (col1 INT, col2 INT, col3 VARCHAR(30), PRIMARY KEY(col1, col2));Change table
test2_tbl5to an auto-partitioned table without specifying theSIZEclause. The split threshold will use the default value ofauto_split_tablet_size.ALTER TABLE test2_tbl5 PARTITION BY RANGE COLUMNS(col1);Query the auto-partitioning attributes of the table.
SELECT TABLE_SCHEMA, TABLE_NAME, AUTO_SPLIT, AUTO_SPLIT_TABLET_SIZE FROM information_schema.TABLES WHERE TABLE_NAME LIKE "test2%" ORDER BY TABLE_NAME;The query result is as follows:
+--------------+------------+------------+------------------------+ | TABLE_SCHEMA | TABLE_NAME | AUTO_SPLIT | AUTO_SPLIT_TABLET_SIZE | +--------------+------------+------------+------------------------+ | db_test | test2_tbl1 | TRUE | 1073741824 | | db_test | test2_tbl2 | TRUE | 5368709120 | | db_test | test2_tbl3 | FALSE | 0 | | db_test | test2_tbl4 | TRUE | 10737418240 | | db_test | test2_tbl5 | TRUE | 134217728 | +--------------+------------+------------+------------------------+ 5 rows in set
Example 3: Create tables with automatic partitioning enabled
Enable automatic partitioning.
ALTER SYSTEM SET enable_auto_split = TRUE;Create a non-partitioned table named
test3_tbl1. Because all non-partitioned tables in the tenant are automatically partitioned by using theSIZEpartitioning method, the following normal table creation statement will automatically partition the table. The tenant-level parameter (128 MB by default) will be used as the split threshold.CREATE TABLE test3_tbl1 (col1 INT, col2 INT, PRIMARY KEY(col1));Create a non-partitioned table named
test3_tbl2with automatic partitioning. No partitioning key is specified, so the primary key is used by default as the partitioning key.CREATE TABLE test3_tbl2 (col1 INT, col2 INT, PRIMARY KEY(col1)) PARTITION BY RANGE ();Create a non-partitioned table named
test3_tbl3with automatic partitioning (split threshold set to 1024 MB using user configuration).CREATE TABLE test3_tbl3 (col1 INT, col2 INT, PRIMARY KEY(col1)) PARTITION BY RANGE () SIZE('1024MB');Create a non-auto-partitioned table named
test3_tbl4.CREATE TABLE test3_tbl4 (col1 INT, col2 INT, PRIMARY KEY(col1)) PARTITION BY RANGE () SIZE('unlimited');A table without a primary key does not meet the requirements for automatic partitioning. Even if
enable_auto_split = true, automatic partitioning will not be enabled.CREATE TABLE test3_tbl5 (col1 INT, col2 INT);A table without a primary key does not meet the requirements for automatic partitioning. Manually enabling automatic partitioning will result in an error.
CREATE TABLE test3_tbl6 (col1 INT, col2 INT) PARTITION BY RANGE ();The query result is as follows:
ERROR 1235 (0A000): auto partitioned table without primary key is not supportedView the automatic partitioning attributes of the table.
SELECT TABLE_SCHEMA, TABLE_NAME, AUTO_SPLIT, AUTO_SPLIT_TABLET_SIZE FROM information_schema.TABLES WHERE TABLE_NAME LIKE "test3%" ORDER BY TABLE_NAME;The query result is as follows:
+--------------+------------+------------+------------------------+ | TABLE_SCHEMA | TABLE_NAME | AUTO_SPLIT | AUTO_SPLIT_TABLET_SIZE | +--------------+------------+------------+------------------------+ | db_test | test3_tbl1 | TRUE | 134217728 | | db_test | test3_tbl2 | TRUE | 134217728 | | db_test | test3_tbl3 | TRUE | 1073741824 | | db_test | test3_tbl4 | FALSE | 0 | | db_test | test3_tbl5 | FALSE | 0 | +--------------+------------+------------+------------------------+ 5 rows in set
Example 4: Enable automatic partitioning for global indexes
Note
For tables with automatic partitioning enabled, even if the parameter global_index_auto_split_policy is not enabled, any created global indexes will still use automatic partitioning.
Create an auto-partitioned table named
test4_tbl1(with a split threshold of 1024 MB, using user configuration).CREATE TABLE test4_tbl1 (col1 INT, col2 INT, col3 INT, PRIMARY KEY(col1)) PARTITION BY RANGE () SIZE('1024MB');Create a global index named
idx1_test4_tbl1on thetest4_tbl1table.CREATE INDEX idx1_test4_tbl1 ON test4_tbl1 (col2) GLOBAL;Create a new global index with the auto-partitioning feature enabled.
ALTER SYSTEM SET global_index_auto_split_policy = 'ALL';Create a non-auto-partitioned partitioned table named
test4_tbl2.CREATE TABLE test4_tbl2(col1 INT, col2 INT, col3 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));Create a global index named
idx1_test4_tbl2on thetest4_tbl2table.CREATE INDEX idx1_test4_tbl2 ON test4_tbl2 (col2) GLOBAL;