In OceanBase AI Database, you can use the DYNAMIC_PARTITION_POLICY clause to specify the dynamic partition management attributes of a table during creation.
This topic describes how to create a dynamic partitioned table by using SQL statements.
Limitations and considerations
You must specify at least one partition definition when creating a dynamic partitioned table in the current version.
Dynamic partition management is supported only for the following types of partitioned tables:
Partitioned tables with RANGE COLUMNS partitioning and a partitioning key of
DATE,DATETIME,TIMESTAMP,YEAR, orBIGINT(withBIGINT_PRECISIONprecision specified).Partitioned tables with RANGE partitioning and a partitioning key of
YEARorBIGINT(withBIGINT_PRECISIONprecision specified).Partitioned tables with LIST COLUMNS partitioning and a partitioning key that is a string time column. The string partitioning key supports
VARCHAR/CHAR, and the format is determined by the value ofTIME_UNIT:- When
TIME_UNIT = 'year': The string partitioning key format isYYYY, for example,2026. - When
TIME_UNIT = 'month': The string partitioning key format isYYYYMM, for example,202608. - When
TIME_UNIT = 'day': The string partitioning key format isYYYYMMDD, for example,20260819. - When
TIME_UNIT = 'hour': The string partitioning key format isYYYYMMDDHH, for example,2026081910.
Note
Dynamic partition management is supported for partitioned tables with LIST COLUMNS partitioning and a string time column as of V4.6.2.1.
- When
Dynamic partition management can be enabled only for a partitioned table or a partition of a subpartitioned table.
Pre-creating partitions and deleting expired partitions after table creation are not executed immediately. You need to wait for the next automatic scheduling of a dynamic partition management task or manually schedule one. For more information about scheduling dynamic partition management tasks, see Dynamic partition management tasks.
Privilege requirements
You must have the CREATE privilege to create a dynamic partitioned table. For more information about privileges in OceanBase AI Database, see Permission classification.
Syntax
The SQL statement for creating a dynamic 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'}
Description of dynamic partition management attributes
Attribute name |
Description |
Required |
Modifiable |
|---|---|---|---|
| ENABLE | Indicates whether to enable dynamic partition management. Valid values:
|
No | Yes |
| TIME_UNIT | The time unit for partitioning, which is the interval at which partition boundaries are automatically created. Valid values:
|
Yes | No |
| PRECREATE_TIME | Represents the pre-create time. When scheduling a dynamic partition management task, partitions are pre-created so that max_partition_upper_bound > now() + precreate_time. Valid values:
Note
|
No | Yes |
| EXPIRE_TIME | Indicates the partition expiration time. When dynamic partition management is scheduled, all partitions whose upper_bound < now() - expire_time are deleted. Valid values:
|
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:
|
No | No |
| BIGINT_PRECISION | The timestamp precision of the bigint-typed partitioning key. Valid values:
|
No | No |
For more information about the parameters in the CREATE TABLE statement, see CREATE TABLE.
Restrictions on dynamic partition parameters
TIME_UNIT: When the partitioning key is of thedatetype, the value of theTIME_UNITparameter cannot behour. When the partitioning key is of theyeartype, the value of theTIME_UNITparameter can only beyear.PRECREATE_TIME: You can pre-create at most 2,048 partitions.TIME_ZONE: TheTIME_ZONEparameter specifies a custom time zone offset. This parameter takes effect only when the partitioning key is of thedate,datetime, oryeartype. For other types, the value of this parameter must bedefault.BIGINT_PRECISION: You can set the value of this parameter tous,ms, orsonly when the partitioning key is of thebiginttype. For other types, the value of this parameter must benone.- The values of the
TIME_UNIT,TIME_ZONE, andBIGINT_PRECISIONparameters cannot be modified after they are specified.
Examples
Range Columns partitioning example
Create a dynamic partitioned table named test_tbl1 with RANGE COLUMNS partitioning. Set the dynamic partitioning attributes as follows:
- Partitions are created every hour.
- Pre-create partitions for the next 3 hours.
- Delete partitions whose upper boundary is earlier than 1 day ago.
- Use Beijing Time (UTC+8) to determine time, so that partition boundaries and expiration logic are based on this time zone.
- The partitioning key
col2is of theDATETIMEtype, so there is no need to configure the timestamp precision forbigint.
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 after the table is created are not executed immediately. You need to wait for the next automatic scheduling of a dynamic partition management task or manually schedule one. For more information, see Dynamic partition management task.
List Columns partitioning example
Create a dynamic partitioned table named test_tbl2 with LIST COLUMNS partitioning. The partitioning key is a string time column in the format YYYYMMDD. Set the dynamic partitioning attributes as follows:
- Partition boundaries are divided by day.
- Pre-create partitions for the next 3 days.
- Delete partitions whose upper boundary is earlier than 30 days ago.
- Use Beijing Time (UTC+8) to determine time, so that partition boundaries and expiration logic are based on this time zone.
- The partitioning key
col2is of theVARCHARtype, so there is no need to configure the timestamp precision forbigint.
obclient> CREATE TABLE test_tbl2 (col1 INT, col2 VARCHAR(20))
DYNAMIC_PARTITION_POLICY(
ENABLE = true,
TIME_UNIT = 'day',
PRECREATE_TIME = '3 day',
EXPIRE_TIME = '30 day',
TIME_ZONE = '+8:00',
BIGINT_PRECISION = 'none'
)
PARTITION BY LIST COLUMNS(col2) (
PARTITION p_20260801 VALUES IN ('20260801')
);