Manage partitions

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

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.

1. On the Ticket tab of the SQL window, choose Partitioning Plan > Create Partitioning Plan.

   

2. 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.

3. After you specify the preceding information, click Submit in the lower-right corner of the panel.

4. 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

1. In the partitioning plan list on the Ticket tab, click View in the Actions column of a partitioning plan task.

2. 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.

3. In the Task Details panel, click Initiate Again in the lower-right corner to initiate the task again.

Task process

1. 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.

2. You can click Initiate Again in the lower-right corner of the panel to initiate the task again.

Association records

1. 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.

2. You can click Initiate Again in the lower-right corner of the panel to initiate the task again.

Task logs

1. 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.

2. You can click Initiate Again to initiate the task again.

References

• Write and execute an SQL statement

• Perform PL compilation and debugging

• Use the command-line window

• Edit and export the result set of an SQL statement

• Analyze an SQL statement

• Generate test data

• Database change management

• Synchronization of shadow tables

• Lock-free schema changes