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.

• 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 Job > 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.
   Strategy Execution Cycles	Creation Strategy Execution Period: Set an execution cycle for all partition creation strategies, for example, at 2:00 a.m. every day. Customize Deletion Strategy Execution Period: Select this option and set an execution cycle for all partition deletion strategies. If you do not select this option, the partition deletion strategies will be scheduled together with partition creation strategies. Example: Set the execution cycle to 3:00 a.m. every day for partition creation strategies, and to 1:00 a.m. every day for partition deletion strategies. The system will execute all partition creation strategies at 3:00 a.m. every day and all partition deletion strategies at 1:00 a.m. every day.
   Job Settings	Select a method for handling task errors. Valid values: 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 Value	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 Jobs > Partitioning Plan list.

View a partitioning plan job

Job information

1. Access Jobs > Partitioning Plan. In the partitioning plan job list, click the name of the target job to view the job details. You can also click ··· in the Actions column to perform more operations on the job.

   • Terminate: You can terminate a job that is periodically or regularly executed.
   • Disable/Enable: You can disable a job that is periodically or regularly executed. After you enable the job again, it will be executed.
   • Edit: You can edit a disabled job.
   • Delete: You can delete a completed or stopped job.
   • View: You can view the job details.
   • Clone: You can clone the job to quickly generate a new job with the same configuration.
   • Share: You can copy the access address of the job.

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

References

• Set partitioning strategies

• Examples