Manage partitioning plans

Background information

Partitioning plans are an extended feature for range partitions. In ODC, you can set partitioning policies to automatically manage range-partitioned tables. This eliminates the need to manually create new partitions and simplifies table maintenance.

• Range partitioning: Range partitioning is the most common type of partitioning and is often used with dates. In range partitioning, the database maps rows to partitions based on the value range of the partition key. For more information, see Set partitioning rules and Partition types.

• Partitioning plan: A partitioning plan is an extended feature for range partitions. It lets you use ODC to automatically manage range-partitioned tables based on policies that you set. This eliminates the need to manually create new partitions and simplifies the maintenance of range-partitioned tables.

• Creation policy: ODC supports automatic table partitioning based on the partitioning rules that you set. You can create a partitioning policy using sequential increments or a custom expression.

  • Sequential increment: This creation method applies only to time, number, and character field types.

    

  • Custom: This creation method supports all field types and lets you create a partitioning policy with a custom SQL expression.

    

• Deletion policy: A deletion policy retains a specified number of the most recent partitions and deletes any excess partitions.

Partitioning plan execution flow

1. Set a creation or deletion policy for the table.

2. Specify the policy execution cycle.

3. The task goes through the approval process.

4. The partitioning plan is executed.

Notes

• Only OceanBase MySQL and OceanBase Oracle data sources are supported.

• Only range-partitioned tables are supported.

• If the upper limit of the last partition in a range-partitioned table is MAXVALUE, new partitions cannot be added. Therefore, you cannot use an ODC partitioning plan task to create a partitioning policy.

• By default, partitioning plans are executed at the top of every hour (00 minutes and 00 seconds).

• If a partitioning plan already exists for a database, a newly created and approved plan overwrites the existing one.

• If a table belongs to a table group, creating a partition may fail or disrupt load balancing. Configure the creation policy with caution.

• Creating a new partition can invalidate a table group. This happens because all tables in a table group must have identical partitions, and adding a new partition to only one table disrupts this consistency.

• If a table has a global index, deleting a partition invalidates the global index. Proceed with caution. Rebuilding the global index is time-consuming and may cause service disruptions if the rebuild fails.

• The option to rebuild the global index is available only in Oracle mode. This option is not available in MySQL mode because MySQL mode rebuilds the global index by default.

Create a partitioning plan

Example: In the `test_data` database of the `mysql4253` data source, create one new partition for the `order` table each month.

1. In the SQL window, enter the SQL statement to create the `order` table.

   

   CREATE TABLE `order` (
   `time` date NOT NULL,
    `parti_key` int(11),
    `name` varchar(120) DEFAULT NULL
   ) partition by range columns(time, parti_key)
   (partition p2023_01 values less than ('2023-01-01', 20230101),
   partition p2023_02 values less than ('2023-02-01', 20230201))

2. In the SQL development window, on the Tickets tab, click Partitioning Plan > Create Partitioning Plan.

   

3. In the Create Partitioning Plan panel, specify the following information.

   

   Item	Description
   Database	Select the database for which you want to create the partitioning plan.
   Partitioning policy	Select the checkboxes of the range-partitioned tables in the database and click to edit the corresponding partitioning policies. For more information about the configuration, see Set a partitioning policy and Partitioning policy examples. Note Select the Show only tables without a policy checkbox to the right of Partitioning policy to filter for tables that do not have a policy. You can select multiple range-partitioned tables to set their partitioning policies in a batch.
   Set policy execution cycle	Execution cycle for creation policy: Set the execution time for all partition creation policies, for example, 2:00 AM every night. Execution cycle for deletion policy: Select the checkbox to customize the execution cycle for the deletion policy. This sets the execution time for all partition deletion policies. If you do not select this checkbox, the deletion policies are scheduled and executed together with the creation policies. Example: The execution cycle for the creation policy is 3:00 AM every night, and the execution cycle for the deletion policy is 1:00 AM every night. This means that all partition creation policies for the database run at 3:00 AM every night, and all partition deletion policies run at 1:00 AM every night.
   Task settings	Select how to handle task errors: Stop Task: This is the default option. If an error occurs while the script is running, the task stops. Ignore Error and Continue Task: If you select this option, the task skips the statement that caused the error and continues to run.
   Execution timeout	The default value is 2 hours.
   Remarks	Describe the business background of the project, such as the purpose of the change and the expected goals.

4. After you specify the information, click Submit in the lower-right corner of the panel to create the partitioning plan task.

5. After the task is created, the Task Hub panel appears. In the Task Hub, you can view the approval status and information of the task.

   

View a partitioning plan

Task information

1. In the list of partitioning plans on the Tickets tab, click View in the Actions column.

   

2. In the Task Details panel that appears, click the Task Information tab to view the basic information and settings of the task.

   Item	Description
   Basic task information	Displays information such as Task Status, Task ID, Task Type, Data Source, Risk Level, Remarks, Creator, and Creation Time.
   Task settings information	Displays the partitioning policy information for the range-partitioned tables selected when the partitioning plan task was created.

3. In the lower-right corner of the Task Information panel, click Resubmit to resubmit the task.

Task flow

1. In the Task Details panel, click the Task Flow tab to view information such as Task Submission Status, Approval Status, Execution Status, and Completion Result.

2. In the lower-right corner of the panel, click Resubmit to resubmit the task.

Associated records

1. In the Task Details panel, click the Associated Records tab to view information such as Task ID, Database, Creation Time, Task Status, and the View action.

2. In the lower-right corner of the panel, click Resubmit to resubmit the task.

Task logs

1. In the Task Details panel, click the Task Logs tab to view all logs and alert logs for the task.

   

   Information	Description
   All logs	All logs displays the complete log information for the task, including INFO, ERROR, and WARN logs. Click the Find, Download, and Copy buttons to search for information, or download or copy all log information.
   Alert logs	Alert logs separately display the ERROR and WARN logs for the task. When a task fails, you can view the error message in the alert logs. Click the Find, Download, and Copy buttons to search for information, or download or copy the alert log information.

2. Click Resubmit to resubmit the task.

Import partitioning plan tasks

After you migrate instances from ApsaraDB for OceanBase to OB Cloud, you can also migrate their corresponding partitioning plan tasks.

Step 1: Export partitioning plan tasks from ApsaraDB for OceanBase

1. In the left navigation pane of the ApsaraDB for OceanBase console, click Data Development.

2. Click Tickets > Partitioning Plan. On the Partitioning Plan page, select the target tickets and click Batch Export.

   

3. On the Export Scheduled Tasks page, confirm the export tasks and click Export All.

   

4. After the export is successful, copy the key from the pop-up window and save it in a secure place. This key is required when you import the partitioning plan.

   

Step 2: Import partitioning plan tasks to OB Cloud

1. In the navigation pane on the left of the ApsaraDB for OceanBase console, click Ecosystem Integration and choose SQL Development > OceanBase Developer Center.

   

2. In Developer Center, click Tickets > Partitioning Plan, and on the Partitioning Plan page, click Import Partitioning Plan.

   

3. On the Import Partitioning Plan page, upload the ZIP file exported in Step 1, enter the File Key, select the target project, and click Next: Preview.

   

4. On the preview page, select the new target database and the I have confirmed that the database objects are consistent between the old and new databases for the imported tickets checkbox, and then click Import.

   

5. After the import is successful, you can see the imported partitioning plan in the partitioning plan list.

   

References

• Set a partitioning policy

• Partitioning policy examples