Background information
RANGE partitioning: Multiple rows with column values in an ordered, continuous, and non-overlapping range are assigned to a partition. For more information, see Set partitioning rules.
Partitioning plan: When you insert data into a RANGE-partitioned table, if the inserted data exceeds the upper limit of the current range, the operation fails and an error is returned. The partitioning plan feature is an extension of RANGE partitioning. OceanBase Developer Center (ODC) can automatically manage RANGE-partitioned tables based on the specified partitioning strategy, and you do not need to manually create partitions. This facilitates maintenance of the RANGE-partitioned tables.
This topic describes how to apply a partitioning plan in ODC for automatic partition management, including partition creation and deletion.
Considerations
Only OceanBase MySQL data sources are supported.
Only RANGE-partitioned tables that are partitioned based on a Timestamp-type field and RANGE COLUMNS-partitioned tables that are partitioned based on a Date-type field are supported.
Create a partitioning plan
Notice
- In ODC V4.0.0 and later, the partitioning plan feature is supported only for RANGE-partitioned tables that are partitioned based on a Timestamp-type field and for RANGE COLUMNS-partitioned tables that are partitioned based on a Date-type field. If a RANGE- or RANGE COLUMNS-partitioned table that is partitioned based on a field of another type exists, ODC will skip creating partitioning plans of this type and record a warning log.
- By default, a partitioning plan is executed on the hour.
Example: Create two partitions for the odc_test database in the mysql410 data source every three months. The created partitions are retained for 12 months.
| Parameter | Example value |
|---|---|
| Project name | odc_4.2.0 |
| Data source | mysql410 |
| Database name | odc_test |
On the Ticket tab of the SQL window, choose Partitioning Plan > Create Partitioning Plan.
In the Create Partitioning Plan panel, configure the following parameters.
Parameter Description Data Source The data source to which the partitioning plan belongs. The default value is the current data source. Partitioning Strategy The partitioning strategy. Search for and select a RANGE-partitioned table in the database and edit its partitioning strategy. - You can select Show Only Tables Without Partitioning Strategies next to Partition Strategy to display only tables with no partitioning strategies.
- You can also select multiple RANGE-partitioned tables and configure a partitioning strategy for them at a time.
- In the Partition policy column, click the edit icon and then you can edit the number of partitions to create, partitioning interval, retention period, and naming rules.
- Number of Partitions: the number of partitions to create.
- Partition Interval: the interval for creating partitions, in the unit of day, month, or year.
- Retention Period: the retention period of partitions, in the unit of day, month, or year. After the specified retention period elapses, the created partitions are automatically deleted.
Example: Set the number of partitions to 1, partition interval to 1 Month, and retention period to 12 month, which specifies to create one partition for the current table every month and to automatically delete partitions that were created one month ago.
Remarks The business background of the project, such as the change purpose and expected goal. After you specify the preceding information, click Submit in the lower-right corner of the panel.
After a task is generated, the Ticket tab automatically appears, where you can view the approval status and basic information of the task.
View a partitioning plan task
Task information
In the partitioning plan list on the Ticket tab, click View in the Actions column of a partitioning plan task.
In the Task Details panel, click the Task Information tab and view the basic task information and task settings.
Parameter Description Basic task information The status, ID, type, connection, remarks, creator, and creation time of the task are displayed. Task settings You can view the partitioning strategy of the RANGE-partitioned table selected when you created the partitioning plan task. In the Task Details panel, click Initiate Again in the lower-right corner to initiate the task again.
Task process
In the Task Details panel, click the Task Process tab and view the initiation status, approval status, execution status, and execution result of the task.
You can click Initiate Again in the lower-right corner of the panel to initiate the task again.
Association records
In the Task Details panel, click the Association Records tab and view the ID, database, creation time, and status of the task. You can also click View in the Actions column of a record.
You can click Initiate Again in the lower-right corner of the panel to initiate the task again.
Task logs
In the Task Details panel, click the Task Logs tab to view all logs and alert logs of the task.
Tab Description All Logs Displays the complete information of all task logs, including INFO, ERROR, and WARN logs. You can click Search, Download, or Copy to search for, download, or copy all logs. Alert Logs Displays the ERROR and WARN logs of the task. When a task fails, you can view the error message in the alert logs. You can click Search, Download, or Copy to search for, download, or copy alert logs. You can click Initiate Again to initiate the task again.