Create a dynamic partitioned table

In OceanBase Database, you can specify the dynamic partition management attribute of a table by using DYNAMIC_PARTITION_POLICY during table creation.

This topic describes how to create a dynamic partitioned table by using SQL statements.

Limitations and considerations

• In the current version, creating a dynamic partitioned table requires at least one partition definition.

• Only RANGE COLUMNS partitioning and RANGE partitioning are supported:

  • RANGE COLUMNS partitioning: Dynamic partition management can be enabled for partitioned tables where the partitioning key is of type date, datetime, timestamp, year, or bigint (BIGINT_PRECISION).
  • RANGE partitioning: Dynamic partition management can be enabled for partitioned tables where the partitioning key is of type year or bigint (BIGINT_PRECISION).

• Dynamic partition management can only be enabled for the partitions of a partitioned table and subpartitioned table.

• After you precreate partitions or delete expired partitions, the partitions are not immediately created or deleted. Instead, a dynamic partition management task is automatically scheduled to run next, or you can manually schedule a dynamic partition management task. For more information, see Dynamic partition management tasks.

Privilege requirements

To create a dynamic partitioned table, you must have the CREATE privilege. For more information about the privileges in OceanBase Database, see Privilege types in MySQL-compatible mode.

Syntax

The SQL statement for creating a dynamically partitioned table is as follows:

CREATE TABLE [IF NOT EXISTS] table_name (table_definition_list)
    DYNAMIC_PARTITION_POLICY [=] (dynamic_partition_policy_list)
    partition_option;

dynamic_partition_policy_list:
    dynamic_partition_policy_option [, dynamic_partition_policy_option ...]

dynamic_partition_policy_option:
    ENABLE = {true | false}
    | TIME_UNIT = {'hour' | 'day' | 'week' | 'month' | 'year'}
    | PRECREATE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}
    | EXPIRE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}
    | TIME_ZONE = {'default' | 'time_zone'}
    | BIGINT_PRECISION = {'none' | 'us' | 'ms' | 's'}

Dynamic partition management attributes

For more information about table creation syntax and parameter descriptions, see CREATE TABLE.

Parameter limitations for dynamic partitions

• TIME_UNIT: If the partitioning key is of the date type, the TIME_UNIT parameter cannot be set to hour. If the partitioning key is of the year type, the TIME_UNIT parameter can be set only to year.
• PRECREATE_TIME: You can precreate at most 2,048 partitions.
• TIME_ZONE: If the partitioning key is of the date, datetime, or year type, you can specify a custom time zone offset for the TIME_ZONE parameter. For other types, the value of the TIME_ZONE parameter must be default.
• BIGINT_PRECISION: This parameter can only be set to us, ms, or s when the partitioning key type is bigint. For other partitioning key types, the value of this parameter must be none.
• The values of the TIME_UNIT, TIME_ZONE, and BIGINT_PRECISION parameters specified cannot be modified.

Examples

Create a dynamic partitioned table named test_tbl1 and set the dynamic partitioning attributes as follows:

• Partition boundaries are divided by hour.
• Pre-create partitions for the 3 hours following the current time.
• Delete partitions whose upper boundary is earlier than 1 day before the current time.
• Use Beijing time (UTC+8) to determine the time, ensuring that partition boundaries and expiration logic are based on this time zone.
• The partitioning key col2 is of type DATETIME, so there is no need to configure the timestamp precision for bigint.

CREATE TABLE test_tbl1 (col1 INT, col2 DATETIME)
    DYNAMIC_PARTITION_POLICY(
        ENABLE = true,
        TIME_UNIT = 'hour',
        PRECREATE_TIME = '3 hour',
        EXPIRE_TIME = '1 day',
        TIME_ZONE = '+8:00',
        BIGINT_PRECISION = 'none'
    )
    PARTITION BY RANGE COLUMNS (col2)(
        PARTITION P0 VALUES LESS THAN ('2025-04-15 13:30:00')
    );

References

• Overview of dynamic partitioning
• Query a dynamic partitioned table
• Modify a dynamic partitioned table
• Dynamic partition management tasks