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 partitioned tables with a partitioning key of type date, timestamp, timestamp with local time zone, or number (with specified BIGINT_PRECISION precision) support enabling dynamic partition management.
• 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 TABLE privilege. For more information about the privileges in OceanBase Database, see Privilege types in Oracle-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

• PRECREATE_TIME: You can precreate at most 2,048 partitions.
• TIME_ZONE: If the partitioning key is of the date or timestamp 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 number. 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 TIMESTAMP, so there is no need to configure the timestamp precision for number.

CREATE TABLE test_tbl1 (col1 INT, col2 TIMESTAMP)
    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 (col2)(
        PARTITION P0 VALUES LESS THAN (TIMESTAMP '2024-11-11 13:30:00')
        );

References

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