Create a dynamic partitioned table|V4.3.5| docs|Distributed Database

Create a dynamic partitioned table

Last Updated：2025-06-26 07:37:47 Updated

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.

Note

For OceanBase Database V4.3.5, the DYNAMIC_PARTITION_POLICY parameter can be used to create a dynamical partitioned table starting from V4.3.5 BP2.

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

Attribute	Description	Required?	Modifiable?
ENABLE	Specifies whether to enable dynamic partition management. Valid values: `true`: specifies to enable dynamic partition management. This is the default value. `false`: specifies to disable dynamic partition management.	No	Yes
TIME_UNIT	The time unit for partitioning, which indicates the interval of automatic partition boundaries. Valid values: `hour`: partitions are created by hours. `day`: partitions are created by days. `week`: partitions are created by weeks. `month`: partitions are created by months. `year`: partitions are created by years.	Yes	No
PRECREATE_TIME	The time when partitions are pre-created. In each scheduled dynamic partition management task, partitions are pre-created to ensure that max_partition_upper_bound > now() + precreate_time. Valid values: `-1`: the default value, which specifies not to pre-create partitions. `0`: specifies to pre-create only the current partition. `n {hour \\| day \\| week \\| month \\| year}`: specifies to pre-create partitions for the corresponding time span. For example, `3 hour` specifies to pre-create partitions for the last 3 hours. Note If multiple partitions are to be pre-created, the interval of partition boundaries is `TIME_UNIT`. The boundary of the first pre-created partition is the upper boundary of the largest existing partition, rounded up based on `TIME_UNIT`.	No	Yes
EXPIRE_TIME	The partition expiration time. In each scheduled dynamic partition management task, all partitions whose upper boundaries are earlier than now() - expire_time will be deleted. Valid values: `-1`: the default value, which specifies that partitions never expire. `0`: specifies that partitions other than the current partition expire. `n {hour \\| day \\| week \\| month \\| year}`: the partition expiration time. For example, `1 day` specifies that the partition expiration time is 1 day.	No	Yes
TIME_ZONE	The time zone to be used for comparing the current time with the partitioning key values of the `date`, `datetime`, and `year` data types. Valid values: `default`: the default value, which specifies not to configure a time zone and to use the tenant time zone instead. The `time_zone` field must be set to `default` for data types other than the preceding three types. `time_zone`: a custom time zone offset, such as `+08:00`.	No	No
BIGINT_PRECISION	The timestamp precision of the `bigint`-typed partitioning key. Valid values: `none`: the default value, which specifies no precision (that is, the partitioning key is not of the `bigint` type). `us`: microsecond precision. `ms`: millisecond precision. `s`: second precision.	No	No

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

Note

The actions of pre-creating partitions and deleting expired partitions will not be executed immediately after the table is created. You need to wait for the next automatic scheduling of the dynamic partition management task, or manually trigger the dynamic partition management task. For more information, see Dynamic partition management task.

Create a dynamic partitioned table

Note

Limitations and considerations

Privilege requirements

Syntax

Dynamic partition management attributes

Note

Parameter limitations for dynamic partitions

Examples

Note

References