Automatic partition splitting

OceanBase Database allows you to predefine automatic partitioning rules, enabling the system to automatically split partitions when data grows to a certain threshold. This feature ensures that tables can scale automatically as data increases, avoiding issues such as unbalanced loads and degraded performance caused by oversized partitions.

Supported scenarios

The current version supports automatic partition splitting only for primary key tables with partitions partitioned by RANGE or RANGE COLUMNS.

Limitations and considerations

• Automatic partition splitting is not supported for tables partitioned by LIST or HASH.
• Automatic partition splitting is not supported for tables with subpartitions.
• Automatic partition splitting is not supported if the partition key of the table being automatically split does not match the prefix of the primary key.
• Automatic partition splitting is not supported for tables without a primary key.
• Automatic partition splitting is not supported for columnstore tables.
• Automatic partition splitting is not supported for columnstore replicas.
• If the table is in a TABLEGROUP (table group) that contains multiple tables, automatic partition splitting is not supported. However, if the TABLEGROUP contains only this table, automatic partition splitting is supported.
• Automatic partition splitting is not supported for materialized views.
• Automatic partition splitting is not supported for full-text indexes.
• Automatic partition splitting is not supported for GIS indexes.
• Automatic partition splitting is not supported for vector indexes.
• Starting from OceanBase Database V4.3.5 BP2, the global_index_auto_split_policy parameter is supported. This parameter is used to control whether the automatic partitioning feature is enabled for newly created global indexes. If automatic partitioning is enabled for a newly created global index, the partitioning rules will be regenerated based on the data volume, that is, pre-splitting will occur.

Automatic partition splitting at the table level

You can configure automatic partitioning at the table level by using the SIZE clause when creating a table.

Syntax for creating a table with automatic partition splitting

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)];

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 ...]

Parameters

Syntax for modifying automatic partition splitting attributes

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 ...]

Parameters

Automatic partition splitting at the tenant level

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. The value specifies whether to enable automatic partition splitting for the tenant. The default value is false.
• auto_split_tablet_size: A tenant-level parameter. The value specifies the threshold for triggering automatic partition splitting after automatic partition splitting is enabled for the tenant. The default value is 128 MB.

If you do not specify the SIZE clause and set enable_auto_split to true, automatic partition splitting is enabled for only tables that meet the partitioning criteria. For example, when you create a subpartitioned table, a table without a primary key, or a table whose partitioning key is different from the primary key prefix, 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).

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 only be modified if the automatic subpartitioning key remains a prefix of the primary key.
• Column operations: Limitations on pre-partitioning keys are the same as those on partitioning keys.

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.

Examples

Example 1: Create an automatically partitioned table

1. 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.

2. Create an automatically partitioned table named test1_tbl1 without specifying a prepartitioning key and 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');
   

3. Create an automatically partitioned table named test1_tbl2 with a prepartitioning key of col1 and a split threshold of 10 GB.

   CREATE TABLE test1_tbl2 (col1 INT, col2 INT, col3 VARCHAR(30), PRIMARY KEY(col1, col2))
       PARTITION BY RANGE(col1) SIZE('10GB');
   

4. Create an automatically partitioned table named test1_tbl3 without specifying a prepartitioning key or a split threshold (the split threshold will use the value of auto_split_tablet_size by default).

   CREATE TABLE test1_tbl3 (col1 INT, col2 INT, col3 VARCHAR(30), PRIMARY KEY(col1, col2))
       PARTITION BY RANGE();
   

5. Create an automatically partitioned table named test1_tbl4 with prepartitioning keys of col1 and col2, a split threshold of 10 GB, and partitioning points of P0, P1, P2, and P3.

   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));
   

6. View 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 "test1%"
   ORDER BY TABLE_NAME;
   

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

Example 2: Modify the automatic partitioning attributes of a table

1. Create an automatically partitioned table named test2_tbl1 with a prepartitioning key of col1 and a split threshold of 10 GB.

   CREATE TABLE test2_tbl1 (col1 INT, col2 INT, col3 VARCHAR(30), PRIMARY KEY(col1, col2))
       PARTITION BY RANGE(col1) SIZE('10GB');
   

2. Modify the split threshold of the test2_tbl1 table to 1 GB.

   ALTER TABLE test2_tbl1 PARTITION BY RANGE() SIZE('1GB');
   

   Or

   ALTER TABLE test2_tbl1 PARTITION BY RANGE(col1) SIZE('1GB');
   

3. Create an automatically partitioned table named test2_tbl2 with a prepartitioning key of col1, a split threshold of 10 GB, and partitioning points of p0 (rows with values less than 100), p1 (rows with values greater than or equal to 100 and less than 200), and p_max (rows with values 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));
   

4. Modify the split threshold of the test2_tbl2 table to 5 GB and change the partitioning points to p0 (rows with values less than 300) and p_max (rows with values 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));
   

5. Create an automatically partitioned table named test2_tbl3 with a prepartitioning key of col1 and a split threshold of 10 GB.

   CREATE TABLE test2_tbl3 (col1 INT, col2 INT, col3 VARCHAR(30), PRIMARY KEY(col1, col2))
       PARTITION BY RANGE(col1) SIZE('10GB');
   

6. Disable automatic partitioning for the test2_tbl3 table. This change does not affect existing partitions, and no new automatic partitioning splits will be scheduled.

   ALTER TABLE test2_tbl3 PARTITION BY RANGE() SIZE('unlimited');
   

7. Create an automatically partitioned table named test2_tbl4 with a prepartitioning key of col1 and a split threshold of 10 GB.

   CREATE TABLE test2_tbl4 (col1 INT, col2 INT, col3 VARCHAR(30), PRIMARY KEY(col1, col2))
       PARTITION BY RANGE(col1) SIZE('10GB');
   

8. Change the prepartitioning key of the test2_tbl4 table from col1 to col1,col2.

   ALTER TABLE test2_tbl4 PARTITION BY RANGE COLUMNS(col1,col2) SIZE('10GB');
   

9. Create a non-partitioned table named test2_tbl5.

   CREATE TABLE test2_tbl5 (col1 INT, col2 INT, col3 VARCHAR(30), PRIMARY KEY(col1, col2));
   

10. Execute the ALTER TABLE statement without the SIZE clause to convert the test2_tbl5 table to an automatically partitioned table. The split threshold will use the value of auto_split_tablet_size by default.

    ALTER TABLE test2_tbl5 PARTITION BY RANGE COLUMNS(col1);
    

11. View 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 "test2%"
    ORDER BY TABLE_NAME;
    

    The return 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 a table after automatic partitioning is enabled

1. Enable automatic partitioning.

   AlTER SYSTEM SET enable_auto_split = TRUE;
   

2. Create a non-partitioned table named test3_tbl1. Because all non-partitioned tables in the tenant are automatically partitioned by using the SIZE partitioning method, the following normal table creation statement will automatically partition the table. The tenant-level configuration item (128 MB by default) will be used as the split threshold.

   CREATE TABLE test3_tbl1 (col1 INT, col2 INT, PRIMARY KEY(col1));
   

3. Create a non-partitioned table named test3_tbl2 for automatic partitioning. Because no partitioning key is specified, the primary key will be used as the partitioning key. The split threshold will use the value of auto_split_tablet_size by default.

   CREATE TABLE test3_tbl2 (col1 INT, col2 INT, PRIMARY KEY(col1))
       PARTITION BY RANGE ();
   

4. Create a non-partitioned table named test3_tbl3 for automatic partitioning. The split threshold will use the value of auto_split_tablet_size by default.

   CREATE TABLE test3_tbl3 (col1 INT, col2 INT, PRIMARY KEY(col1))
       PARTITION BY RANGE () SIZE('1024MB');
   

5. Create a non-partitioned table named test3_tbl4.

   CREATE TABLE test3_tbl4 (col1 INT, col2 INT, PRIMARY KEY(col1))
       PARTITION BY RANGE () SIZE('unlimited');
   

6. 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);
   

7. A table without a primary key does not meet the requirements for automatic partitioning. The automatic partitioning feature cannot be manually enabled for such a table.

   CREATE TABLE test3_tbl6 (col1 INT, col2 INT)
       PARTITION BY RANGE ();
   

   The return result is as follows:

   ERROR 1235 (0A000): auto partitioned table without primary key is not supported
   

8. View 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 return 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

1. Create an automatically partitioned table 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');
   

2. Create a global index idx1_test4_tbl1 on the table test4_tbl1.

   CREATE INDEX idx1_test4_tbl1 ON test4_tbl1 (col2) GLOBAL;
   

3. Enable the automatic partitioning feature for newly created global indexes.

   ALTER SYSTEM SET global_index_auto_split_policy = 'ALL';
   

4. Create a partitioned table test4_tbl2 without automatic partitioning.

   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));
   

5. Create a global index idx1_test4_tbl2 on the table test4_tbl2.

   CREATE INDEX idx1_test4_tbl2 ON test4_tbl2 (col2) GLOBAL;
   

References

• enable_auto_split
• auto_split_tablet_size
• global_index_auto_split_policy
• ALTER TABLE