Core values
AP scenarios (analytical scenarios) typically face the following challenges:
- Massive data growth: Logs, user behavior, and sensor data are continuously written.
- Complex resource management: Manually creating/deleting partitions is time-consuming and error-prone.
- Storage cost pressure: Automatic cleanup of historical data is needed to prevent storage bloat.
Solution for dynamic partitioning
By setting predefined rules (such as time units, precreation periods, and expiration times), OceanBase automatically manages partitions, achieving:
- Zero operational overhead: Precreate partitions using scheduled tasks without manual intervention. (Dynamic partitioning creates partitions based on predefined rules through scheduled tasks; OceanBase 4.4.2 and 4.5.0 support Interval partitioning, which automatically creates partitions during data writes.)
- Smart cleanup: Delete outdated partitions as needed to free up storage space.
- Query acceleration: Partition pruning reduces the amount of data scanned.
Feature highlights
- Regularly create new partitions based on
TIME_UNIT(such as hours, days, or months) to ensure automatic expansion of data by time range. - Partition boundaries are divided based on time units (such as
hourorday), suitable for scenarios where data is stored in a time series (such as logs or transaction records). - Specify the
PRECREATE_TIMEto precreate future partitions for a certain period, preventing partition shortages due to sudden data spikes. - Regularly clean up expired partitions based on
EXPIRE_TIMEto free up storage space. - Support for custom time zones (
TIME_ZONE) to ensure consistency across different time zones. - Support for configuring the precision of
biginttype timestamps (such asBIGINT_PRECISION) to meet varying time precision requirements.
Key scenarios
Modifying dynamic partition properties
Changing business requirements
- To enable or disable dynamic partition management, adjust the
ENABLEparameter. - To retain data for a longer period (such as extending from 30 to 90 days), adjust the dynamic partition
EXPIRE_TIMEparameter.
- To enable or disable dynamic partition management, adjust the
Handling business peaks
- During promotions, to prevent partition shortages, adjust the dynamic partition
PRECREATE_TIMEparameter to precreate more partitions.
- During promotions, to prevent partition shortages, adjust the dynamic partition
Here are examples of modifying dynamic partition properties:
-- Set the precreation period to 3 days, expiration time to 90 days, and enable dynamic partitioning
ALTER TABLE user_behavior DYNAMIC_PARTITION_POLICY(
PRECREATE_TIME = '3 DAY',
EXPIRE_TIME = '90 DAY',
ENABLE = TRUE
);
-- Temporarily disable dynamic partition management
ALTER TABLE user_behavior DYNAMIC_PARTITION_POLICY(ENABLE = FALSE);
Extended configuration scenarios for dynamic partitions
Scenario: Extending data retention period due to compliance requirements
-- Extend the expiration time to 90 days ALTER TABLE user_behavior DYNAMIC_PARTITION_POLICY( EXPIRE_TIME = '90 DAY' );
Partition management
OceanBase manages dynamic partitions using built-in scheduled tasks. Manual adjustments are supported in the following scenarios:
Adjusting the scheduled task's execution time to avoid business peaks:
CALL DBMS_SCHEDULER.SET_ATTRIBUTE( 'SCHEDULED_MANAGE_DYNAMIC_PARTITION_DAILY', 'START_DATE', '2025-11-14 02:00:00' );- Assume the current time is
2025-11-13 15:00:00and the default next execution time is2025-11-14 00:00:00. To schedule it to run at 2:00 AM daily, change the next execution time to2025-11-14 02:00:00.
- Assume the current time is
Special business needs:
CALL DBMS_PARTITION.MANAGE_DYNAMIC_PARTITION();- Manually execute a dynamic partition management task to immediately clean up expired partitions or precreate future partitions (such as for urgent storage recovery).
Designing dynamic partition tables
CREATE TABLE user_behavior (
user_id BIGINT,
event_time DATETIME NOT NULL,
event_type VARCHAR(50),
device_info VARCHAR(255)
)
DYNAMIC_PARTITION_POLICY(
ENABLE = TRUE,
TIME_UNIT = 'hour',
PRECREATE_TIME = '3 hour',
EXPIRE_TIME = '1 DAY',
TIME_ZONE = '+8:00'
)
PARTITION BY RANGE COLUMNS(event_time) (
PARTITION p_start VALUES LESS THAN ('2025-11-13 13:30:00')
);
Validate the dynamic partitioning strategy
Step 1: Verify table configuration
show create table user_behavior\G
The output is as follows:
*************************** 1. row ***************************
Table: user_behavior
Create Table: CREATE TABLE `user_behavior` (
`user_id` bigint(20) DEFAULT NULL,
`event_time` datetime NOT NULL,
`event_type` varchar(50) DEFAULT NULL,
`device_info` varchar(255) DEFAULT NULL
) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 DYNAMIC_PARTITION_POLICY = (ENABLE = TRUE, TIME_UNIT = 'HOUR', PRECREATE_TIME = '3HOUR', EXPIRE_TIME = '1DAY', TIME_ZONE = '+8:00', BIGINT_PRECISION = 'NONE')
partition by range columns(`event_time`)
(partition `p_start` values less than ('2025-11-13 13:30:00'))
1 row in set
Validate partitions:
-- Trigger partition expansion, wait for the system to automatically schedule, or manually execute it in the test environment:
CALL DBMS_PARTITION.MANAGE_DYNAMIC_PARTITION();
SHOW CREATE TABLE user_behavior\G
When at least one partition definition is specified, pre-created partitions are not immediately created after table creation. Instead, they are created during a dynamic partitioning management schedule or when manually executed. During dynamic partitioning management, the system creates partitions at intervals of time_unit until the maximum partition boundary exceeds now() + precreate_time. The first partition boundary is the ceiling of the existing maximum partition boundary based on time_unit. For example, if the current time is 2025-11-13 14:50:00, after one dynamic partitioning management schedule, the partitioning results are as follows:
*************************** 1. row ***************************
Table: user_behavior
Create Table: CREATE TABLE `user_behavior` (
`user_id` bigint(20) DEFAULT NULL,
`event_time` datetime NOT NULL,
`event_type` varchar(50) DEFAULT NULL,
`device_info` varchar(255) DEFAULT NULL
) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 DYNAMIC_PARTITION_POLICY = (ENABLE = TRUE, TIME_UNIT = 'HOUR', PRECREATE_TIME = '3HOUR', EXPIRE_TIME = '1DAY', TIME_ZONE = '+8:00', BIGINT_PRECISION = 'NONE')
partition by range columns(`event_time`)
(partition `p_start` values less than ('2025-11-13 13:30:00'),
partition `P2025111313` values less than ('2025-11-13 14:00:00'),
partition `P2025111314` values less than ('2025-11-13 15:00:00'),
partition `P2025111315` values less than ('2025-11-13 16:00:00'),
partition `P2025111316` values less than ('2025-11-13 17:00:00'),
partition `P2025111317` values less than ('2025-11-13 18:00:00'))
1 row in set
If the existing maximum partition boundary is earlier than the current time, the system attempts to supplement historical partitions. For example, if the existing maximum partition is PARTITION P0 VALUES LESS THAN ('2025-11-13 11:30:00') and the current time is 2025-11-13 12:34:56, after one dynamic partitioning management schedule, the partitions are:
PARTITION P0 VALUES LESS THAN ('2025-11-13 11:30:00'),
PARTITION P2025111311 VALUES LESS THAN ('2025-11-13 12:00:00'),
PARTITION P2025111312 VALUES LESS THAN ('2025-11-13 13:00:00'),
PARTITION P2025111313 VALUES LESS THAN ('2025-11-13 14:00:00'),
PARTITION P2025111314 VALUES LESS THAN ('2025-11-13 15:00:00'),
PARTITION P2025111315 VALUES LESS THAN ('2025-11-13 16:00:00')
Step 2: Insert test data
-- Insert test data covering different time ranges
-- Assume the current time is 2025-11-13 15:00:00
-- (a) Insert data for the current hour (should fall into P2025111315)
INSERT INTO user_behavior (user_id, event_time, event_type, device_info)
VALUES (1001, '2025-11-13 15:25:00', 'click', 'iPhone15');
-- (b) Insert data for the next hour (should fall into P2025111316, which has been pre-created)
INSERT INTO user_behavior (user_id, event_time, event_type, device_info)
VALUES (1002, '2025-11-13 16:10:00', 'purchase', 'MacBook');
-- (c) Insert data from 25 hours ago (2025-11-12 13:00:00, which falls into the first partition)
INSERT INTO user_behavior (user_id, event_time, event_type, device_info)
VALUES (1003, '2025-11-12 13:00:00', 'old_event', 'Android');
-- (d) Insert data within the initial partition range (< 13:30)
INSERT INTO user_behavior (user_id, event_time, event_type, device_info)
VALUES (1004, '2025-11-13 13:20:00', 'view', 'iPad');
-- View partition expansion
SHOW CREATE TABLE user_behavior\G
Example output:
*************************** 1. row ***************************
Table: user_behavior
Create Table: CREATE TABLE `user_behavior` (
`user_id` bigint(20) DEFAULT NULL,
`event_time` datetime NOT NULL,
`event_type` varchar(50) DEFAULT NULL,
`device_info` varchar(255) DEFAULT NULL
) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 DYNAMIC_PARTITION_POLICY = (ENABLE = TRUE, TIME_UNIT = 'HOUR', PRECREATE_TIME = '3HOUR', EXPIRE_TIME = '1DAY', TIME_ZONE = '+8:00', BIGINT_PRECISION = 'NONE')
partition by range columns(`event_time`)
(partition `p_start` values less than ('2025-11-13 13:30:00'),
partition `P2025111313` values less than ('2025-11-13 14:00:00'),
partition `P2025111314` values less than ('2025-11-13 15:00:00'),
partition `P2025111315` values less than ('2025-11-13 16:00:00'),
partition `P2025111316` values less than ('2025-11-13 17:00:00'),
partition `P2025111317` values less than ('2025-11-13 18:00:00'),
partition `P2025111318` values less than ('2025-11-13 19:00:00'))
1 row in set
Step 3: Query verification
-- Query active users in the last 2 hours
SELECT
DATE_FORMAT(event_time, '%Y-%m-%d %H:00') AS hour_slot,
COUNT(*) AS event_count,
COUNT(DISTINCT user_id) AS active_users
FROM user_behavior
WHERE event_time >= NOW() - INTERVAL 2 HOUR
GROUP BY hour_slot
ORDER BY hour_slot;
Sample data:
+------------------+-------------+--------------+
| hour_slot | event_count | active_users |
+------------------+-------------+--------------+
| 2025-11-13 13:00 | 1 | 1 |
| 2025-11-13 15:00 | 1 | 1 |
| 2025-11-13 16:00 | 1 | 1 |
+------------------+-------------+--------------+
3 rows in set
Step 4: Verify data cleanup (optional)
Wait for more than 24 hours, or manually advance the system time (in a test environment), and trigger management again:
-- Assume the current time is 2025-11-14 15:00:00
CALL DBMS_PARTITION.MANAGE_DYNAMIC_PARTITION();
-- Check if partitions before 2025-11-13 14:00 have been removed
SHOW CREATE TABLE user_behavior\G
Step 5: Monitor partition management tasks
Sample output:
SELECT JOB_NAME, ENABLED, NEXT_RUN_DATE
FROM oceanbase.DBA_SCHEDULER_JOBS
WHERE JOB_NAME = 'SCHEDULED_MANAGE_DYNAMIC_PARTITION_HOURLY';
Expected output:
+-------------------------------------------+---------+----------------------------+
| JOB_NAME | ENABLED | NEXT_RUN_DATE |
+-------------------------------------------+---------+----------------------------+
| SCHEDULED_MANAGE_DYNAMIC_PARTITION_HOURLY | 1 | 2025-11-13 16:00:00.000000 |
+-------------------------------------------+---------+----------------------------+
1 row in set
Step 6: View task execution history
SELECT
TIMESTAMP,
VALUE1 AS success_tables,
VALUE2 AS failed_tables,
COST_TIME,
RET_CODE
FROM oceanbase.DBA_OB_TENANT_EVENT_HISTORY
WHERE event = 'MANAGE_DYNAMIC_PARTITION'
ORDER BY TIMESTAMP DESC
LIMIT 3;
Expected output:
+----------------------------+--------------------------+---------------+-----------+----------+
| TIMESTAMP | success_tables | failed_tables | COST_TIME | RET_CODE |
+----------------------------+--------------------------+---------------+-----------+----------+
| 2025-11-13 15:00:00.173069 | [500020, 500053] | [] | 164938 | 0 |
| 2025-11-13 14:47:56.816717 | [500003, 500020, 500053] | [] | 63801 | 0 |
| 2025-11-13 14:00:00.125075 | [500020] | [] | 117113 | 0 |
+----------------------------+--------------------------+---------------+-----------+----------+
3 rows in set
This can be used to troubleshoot whether the partition management task was successfully executed.
Step 7: Verify boundary behavior (optional)
Try inserting data outside the pre-created range (assuming the current time is 2025-11-13 15:07:00 and the data to be inserted is 2025-11-13 20:00:00, with the pre-created upper limit being 2025-11-13 19:00:00):
INSERT INTO user_behavior VALUES (999, '2025-11-13 20:00:00', 'future', 'test');
-- Error: ERROR 1526 (HY000): Table has no partition for value
Related views
Dictionary views
You can query the DBA_OB_DYNAMIC_PARTITION_TABLES view to obtain the attributes of a dynamic partition table.
The
DBA_OB_DYNAMIC_PARTITION_TABLESview is visible to all tenants and contains the dynamic partition table data of the current tenant.The
oceanbase.CDB_OB_DYNAMIC_PARTITION_TABLESview is visible only to the sys tenant and contains the dynamic partition table data of all tenants.
Dynamic performance views
You can query the dynamic performance view V$OB_DYNAMIC_PARTITION_TABLES to obtain the attributes of a dynamic partition table. This view has better query performance because it reads data from memory.
- In the sys tenant, the
V$OB_DYNAMIC_PARTITION_TABLESview displays the dynamic partition table data of all tenants. - In a user tenant, the
V$OB_DYNAMIC_PARTITION_TABLESview displays the dynamic partition table data of the current tenant.