Automatic subpartition splitting

You can set automatic subpartition splitting rules in OceanBase Database. When the data volume reaches a certain threshold, the database automatically splits subpartitions. This way, the database can automatically scale out as the data volume grows, avoiding issues such as load balancing and performance degradation caused by large data volumes in a single subpartition.

Support for automatic subpartition splitting

The current version supports automatic subpartition splitting only for a table with a primary key that is range-partitioned at the subpartition level.

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 subpartition splitting at the table level

You can create a single automatic subpartition table by specifying the SIZE clause when you create a table by using the CREATE TABLE statement.

Syntax for creating a table with automatic subpartitioning

CREATE TABLE table_name (column_definition_list, PRIMARY KEY(column_name_list))
    PARTITION BY RANGE([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 subpartitioning attributes

ALTER TABLE table_name
    [MODIFY] PARTITION BY RANGE([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 subpartition splitting at the tenant level

You can set the enable_auto_split and auto_split_tablet_size parameters at the tenant level to control automatic subpartition splitting for tables that meet the conditions in the tenant. If you do not specify the SIZE clause when you create a table by using the CREATE TABLE statement, whether to create an automatic subpartition table is determined based on the following tenant-level parameters (the primary key column is used as the subpartitioning key of the automatic subpartition table by default).

• enable_auto_split: a tenant-level parameter that specifies whether to enable automatic subpartition splitting for a tenant. The default value is false.
• auto_split_tablet_size: a tenant-level parameter that specifies the threshold for triggering automatic subpartition splitting. The default value is 128 MB.

If you do not specify the SIZE clause and set enable_auto_split to true, automatic subpartition splitting will be enabled only for tables that meet the subpartition splitting conditions, and will not be enabled for tables that do not meet the conditions. For example, if you create a subpartitioned table, a table that is not range-partitioned, a table without a primary key, or a table whose partitioning key is different from the primary key prefix, the table will be created as a non-automatic subpartition table (if you specify special syntax for automatic subpartition splitting, an error will be returned).

Automatic subpartition splitting behavior

Automatic subpartition splitting DDL operations

• Modify partitioning rules: Only automatic subpartition splitting by range partitioning at the subpartition level based on the primary key is supported. Other partitioning rules cannot be modified.
• Modify the primary key: The automatic subpartitioning key must be the primary key prefix after the primary key is modified.
• Column operations: The restrictions on the prepartitioning key are the same as those on the partitioning key.

DML behavior

During subpartition splitting, two schemas coexist. DML operations on the source and target partitions are equivalent to operations on the same data. For example, write and query operations on the source partition will be converted to corresponding query and write operations on the target partition, and write and query operations on the target partition will be aware of write operations forwarded from the source partition. The following table describes the DML behavior in different scenarios:

Examples

Example 1: Create an auto-partitioned table

1. Disable auto-partitioning.

   AlTER SYSTEM SET enable_auto_split = FALSE;
   

   Note

   You can execute the SHOW PARAMETERS LIKE 'enable_auto_split'; statement to check whether auto-partitioning is enabled.

2. Create an auto-partitioned table named test1_tbl1 without specifying a pre-partitioning key and a split threshold of 10 GB.

   CREATE TABLE test1_tbl1 (col1 NUMBER, col2 NUMBER, col3 VARCHAR2(30), PRIMARY KEY(col1, col2))
       PARTITION BY RANGE() SIZE('10GB');
   

3. Create an auto-partitioned table named test1_tbl2, specify the pre-partitioning key as col1, and set the split threshold to 10 GB.

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

4. Create an auto-partitioned table named test1_tbl3, without specifying a pre-partitioning key and a split threshold (the split threshold will be set to the value of auto_split_tablet_size by default).

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

5. Create an auto-partitioned table named test1_tbl4, specify the pre-partitioning key as col1, col2, set the split threshold to 10 GB, and create partitioning points P0, P1, P2, and P3.

   CREATE TABLE test1_tbl4 (col1 NUMBER, col2 NUMBER, col3 VARCHAR2(30), PRIMARY KEY(col1, col2))
       PARTITION BY RANGE(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 auto-partitioning attributes of the table.

   SELECT OWNER, TABLE_NAME, AUTO_SPLIT, AUTO_SPLIT_TABLET_SIZE
   FROM sys.ALL_TABLES
   WHERE TABLE_NAME LIKE 'TEST1%'
   ORDER BY TABLE_NAME;
   

   The return result is as follows:

   +-------+------------+------------+------------------------+
   | OWNER | TABLE_NAME | AUTO_SPLIT | AUTO_SPLIT_TABLET_SIZE |
   +-------+------------+------------+------------------------+
   | SYS   | TEST1_TBL1 | TRUE       | 10737418240            |
   | SYS   | TEST1_TBL2 | TRUE       | 10737418240            |
   | SYS   | TEST1_TBL3 | TRUE       | 134217728              |
   | SYS   | TEST1_TBL4 | TRUE       | 10737418240            |
   +-------+------------+------------+------------------------+
   4 rows in set
   

Example 2: Modify the auto-partitioning attributes of a table

1. Create an auto-partitioned table named test2_tbl1, specifying the pre-partitioning key as col1 and the split threshold as 10 GB.

   CREATE TABLE test2_tbl1 (col1 NUMBER, col2 NUMBER, col3 VARCHAR2(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 auto-partitioned table named test2_tbl2, specifying the pre-partitioning key as col1 and the split threshold as 10 GB.

   CREATE TABLE test2_tbl2 (col1 NUMBER, col2 NUMBER, col3 VARCHAR2(30), PRIMARY KEY(col1, col2))
       PARTITION BY RANGE(col1) SIZE('10GB');
   

4. Add partitioning points 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) to the test2_tbl2 table.

   ALTER TABLE test2_tbl2
       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));
   

5. Create an auto-partitioned table named test2_tbl3, specifying the pre-partitioning key as col1 and the split threshold as 10 GB.

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

6. Disable auto-partitioning for the test2_tbl3 table. This change does not affect existing partitions and does not schedule new auto-partitioning splits.

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

7. Create an auto-partitioned table named test2_tbl4, specifying the pre-partitioning key as col1 and the split threshold as 10 GB.

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

8. Change the pre-partitioning key of the test2_tbl4 table from col1 to col1, col2.

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

9. Create a non-auto-partitioned table named test2_tbl5.

   CREATE TABLE test2_tbl5 (col1 NUMBER, col2 NUMBER, col3 VARCHAR2(30), PRIMARY KEY(col1, col2));
   

10. Change the test2_tbl5 table to an auto-partitioned table without specifying the SIZE clause. The split threshold will be set to the value of auto_split_tablet_size by default.

    ALTER TABLE test2_tbl5 PARTITION BY RANGE (col1);
    

11. View the auto-partitioning attributes of the table.

    SELECT OWNER, TABLE_NAME, AUTO_SPLIT, AUTO_SPLIT_TABLET_SIZE
    FROM sys.ALL_TABLES
    WHERE TABLE_NAME LIKE 'TEST2%'
    ORDER BY TABLE_NAME;
    

    The return result is as follows:

    +-------+------------+------------+------------------------+
    | OWNER | TABLE_NAME | AUTO_SPLIT | AUTO_SPLIT_TABLET_SIZE |
    +-------+------------+------------+------------------------+
    | SYS   | TEST2_TBL1 | TRUE       | 1073741824             |
    | SYS   | TEST2_TBL2 | TRUE       | 10737418240            |
    | SYS   | TEST2_TBL3 | FALSE      | 0                      |
    | SYS   | TEST2_TBL4 | TRUE       | 10737418240            |
    | SYS   | TEST2_TBL5 | TRUE       | 134217728              |
    +-------+------------+------------+------------------------+
    5 rows in set
    

Example 3: Create a table after enabling auto-partitioning

1. Enable auto-partitioning.

   AlTER SYSTEM SET enable_auto_split = TRUE;
   

2. Create a non-partitioned table named test3_tbl1. Since all non-partitioned tables in the tenant are automatically SIZE-partitioned, the following syntax will automatically partition the table and read the tenant-level configuration item (the default split threshold is 128 MB).

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

3. Create a non-partitioned table named test3_tbl2 for auto-partitioning (the default split threshold is 128 MB). Since no partitioning key is specified, the primary key will be used as the partitioning key 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 auto-partitioning (the split threshold is 1024 MB, which is user-configured).

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

5. 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');
   

6. Tables without primary keys do not meet the requirements for auto-partitioning. Even if enable_auto_split = true, auto-partitioning is not enabled.

   CREATE TABLE test3_tbl5 (col1 INT, col2 INT);
   

7. Tables without primary keys do not meet the requirements for auto-partitioning. An error is returned when you manually enable auto-partitioning.

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

   The return result is as follows:

   OBE-00600: internal error code, arguments: -4007, auto partitioned table without primary key is not supported    
   

8. View the auto-partitioning attributes of the table.

   SELECT OWNER, TABLE_NAME, AUTO_SPLIT, AUTO_SPLIT_TABLET_SIZE
   FROM sys.ALL_TABLES
   WHERE TABLE_NAME LIKE 'TEST3%'
   ORDER BY TABLE_NAME;
   

   The return result is as follows:

   +-------+------------+------------+------------------------+
   | OWNER | TABLE_NAME | AUTO_SPLIT | AUTO_SPLIT_TABLET_SIZE |
   +-------+------------+------------+------------------------+
   | SYS   | TEST3_TBL1 | TRUE       | 134217728              |
   | SYS   | TEST3_TBL2 | TRUE       | 134217728              |
   | SYS   | TEST3_TBL3 | TRUE       | 1073741824             |
   | SYS   | TEST3_TBL4 | FALSE      | 0                      |
   | SYS   | 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.

   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