Manage partitioning plans

This topic describes how to apply a partitioning plan in OceanBase Developer Center (ODC) for automatic partition management, including partition creation and deletion.

Background information

The partitioning plan feature is an extension of RANGE partitioning. ODC can automatically manage RANGE-partitioned tables based on the specified partitioning strategies, and you do not need to manually create partitions. This facilitates maintenance of the RANGE-partitioned tables.

• RANGE partitioning: RANGE partitioning is the most commonly used partitioning method. It is often used when you want to query tables with dates. In RANGE partitioning, the database maps rows to partitions based on ranges of partitioning key values. For more information, see Set partitioning rules and Partitioning types.

• Partitioning plan: When you insert data into a RANGE-partitioned table, if the inserted data exceeds the upper bound of the current range, the operation fails and an error is returned.

• Creation strategy: Table data is divided into corresponding partitions based on the specified time interval and data range. Two partition creation methods are supported:

  Sequential Increment: A method for creating partitioning strategies, applicable only to time, numeric, and character field types.

  

  • Custom: You can define a custom SQL expression for partitioning for any field type.

    

• Deletion strategy: Based on the specified number of partitions allowed, the latest partitions are retained and redundant partitions are deleted.

Partitioning plan execution process

1. Set a partition creation strategy and a partition deletion strategy for a table.
2. Set a strategy execution cycle.
3. Approve the job process.
4. Execute the partitioning plan.

Considerations

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

• Only RANGE-partitioned tables are supported.

• When the upper bound of the last partition in a RANGE-partitioned table reaches the value specified by MAXVALUE, no partition can be created. In this case, you cannot create a partitioning strategy via the ODC partitioning plan job.

• By default, a partitioning plan is executed on the hour.

• Creating partitions for a table within a table group can fail or break the load balancing status. Proceed with caution when you decide to configure a partition creation strategy for such a table.

• Creating partitions for a table within a table group can invalidate the table group, because tables in the same table group must have the same number of partitions.

• Deleting partitions from a table that contains a global index will invalidate the global index. Proceed with caution. Choosing to rebuild the global index may cause business issues due to the time-consuming process or online issues resulting from rebuilding failures.

  • Partition deletion may block new partition creation: When a table contains a global index, deleting partitions may block subsequent new partition creation operations. The system will trigger a risk warning. It is recommended to perform partition deletion during off-peak hours.
  • Global index fields: The system returns information about the global index fields in the table, helping you assess the impact of partition deletion on your business.

• The option of rebuilding global indexes is provided only in Oracle compatible mode. In MySQL compatible mode, global indexes will be rebuilt by default.

Create a partitioning plan

Example: Create a partition for the order table in the doc_test_data_1 database of the mysql441 data source at a time interval of 1 month.

| Parameter | Example value | |Project name | odc_test | |Data source | mysql441 | |Database name |doc_test_data_1| |Database name |order| |Table name |order |

1. In the SQL window, edit the SQL statements 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. Click Jobs > Partitioning Plan > Create New in the SQL window.

3. Specify the following information.

   Parameter	Description
   Database	Select the database for which the partitioning plan is created.
   Partitioning Strategy	Select the target RANGE-partitioned table in the specified database and click to set partitioning strategies. For more information, see Set partitioning strategies and Examples. Note You can select the Show Only Unconfigured Tabls option next to Partitioning Strategy to display only tables with no partitioning strategies. You can also select multiple RANGE-partitioned tables and configure partitioning strategies for them at a time.
   Execution Mode	Set the execution mode for all partition creation strategies by selecting Periodic Execution, Immediate Execution, or Scheduled Execution. For example, execute at 2:00 a.m. every day. Customize Deletion Strategy Execution Period: If you do not select this option, the partition deletion strategies will be scheduled together with partition creation strategies.
   Job Settings	Task Error Handling Method: Stop Task: This is the default option. When you select this option, the task is aborted if an error occurs when you run the script. Ignore Error and Continue: When you select this option, the system skips the statement where an error occurs and continues to execute other statements in the script. Execution Timeout. The default value is 2 hours.

4. After you specify the preceding information, click Submit.

5. After the job is generated, you can view the job information in the Job > Partitioning Plan list.

View a partitioning plan job

Job information

1. Go to Job > Partitioning Plan, click the name of the target task in the list to view the job details. You can also click ··· in the Actions column to perform more operations on the job.

   • Terminate: If the job execution mode is set to periodic execution or scheduled execution, you can terminate the job. You can also select the target jobs and click Batch Terminate at the top to terminate multiple jobs.
   • Disable/Enable: If the job execution mode is set to periodic execution or scheduled execution, you can disable the job to temporarily stop its execution. The job will resume execution after it is enabled.
   • Edit: If the job status is disabled, you can edit the job to update its content.
   • Delete: If the job status is completed or terminated, you can delete the job.
   • View: View the job details.
   • Clone: Clone the job to quickly generate a new job with the same configuration.
   • Share: Copy the job access address.

2. In the job details panel, the Basic Information tab is displayed by default. You can view the job type, database information, variable configuration, partitioning strategy, and execution method.

3. In the job details panel, click the Execution Records tab to view the execution records of the job.

4. In the job details panel, click the Operation Records tab to view the approval status and change records of the job.

Batch import and export

Batch export

1. Go to Tasks > Partitioning Plan.

2. Select the target tasks and click Batch Actions > Batch Export.

3. Confirm the export content and click Export to complete the batch export.

4. After the export is completed, the export result is displayed on the right side of the page. You can view the key and view the exported file locally.

   Notice

   Keep the key properly. It is required when you import the exported file.

Batch import

1. Go to Jobs > Partitioning Plan.

2. Click the ∨ icon next to Create New and select Import Partitioning Plan.

3. Select the file to import, enter the file key, select the target project, and click Next: Import.

4. Select the tasks to import and the target database, and click Import after you select I acknowledge the relevant business risks..

5. After the tasks are imported, you can view them in the list.

References

• Set partitioning strategies

• Examples