This topic describes how to set a partition creation strategy and a partition deletion strategy.
Considerations
Consider the following requirements when you configure a partition creation strategy:
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 indexes for a table within a table group can invalidate the table group, because tables in the same table group must have the same partitions.
Consider the following requirements when you configure a partition deletion strategy:
Deleting partitions for a table with global indexes can invalidate the global indexes. Proceed with caution when you delete partitions for a table with global indexes. If you choose to rebuild the global indexes, the operation may take a long time and cause business issues, or the operation may fail and cause online issues.
The rebuild global indexes option is available only in Oracle compatible mode. In MySQL compatible mode, this option is unavailable because the system automatically rebuilds global indexes by default.
Partition creation strategy
In the Create Partitions section, set the following parameters:
Parameter |
Description |
|---|---|
| Number of Partitions | Specifies the number of partitions to precreate. |
| Creation Rule | Specifies the rule for creating partitions. For more information, see the Custom partition creation strategy or Sequential increased partition creation strategy section. |
| Naming Convention | Specifies the naming method for partitions. Valid values: Custom and Prefix + Suffix. |
| Naming Rule | Specifies the naming rule for partitions. For more information, see the Partition name generation strategy section. |
| Copy Statistics from Table's Last Partition to New Partition | Specifies whether to copy the statistics of the last partition to the new partition. |
Custom partition creation strategy
You can create a custom partition creation strategy to generate the upper bound of a partition by using an SQL expression. Perform the following steps:
Use an SQL expression to obtain the reference time, which is the start time for partition creation.
Note
To use the time when the partitioning plan is executed as the start time, use the
now()orsysdatefunction.Set the time interval for generating partitions: The partitioning plan will generate partitions based on the specified time interval. After you enter a time interval in the Interval field, OceanBase Developer Center (ODC) uses the
${INTERVAL}expression to reference this time interval and adds it to the SQL expression generated in Step 1.Generate the partitioning expression of the same data type as the partitioning key: Convert the SQL expression of a time type generated after Step 1 and Step 2 are completed into the actual type of the partitioning key. If the partitioning key and SQL expression are of the same type, skip this step.
You can reference variables in custom partition creation strategies. The following table lists the supported expressions.
Expression |
Description |
Example |
|---|---|---|
| INTERVAL | The time interval for generating partitions. ODC will replace this variable with the value of the Interval field in the Rule column in the Creation Rule section. | ${INTERVAL} |
Generally, if you choose to create a custom partition creation strategy, partitions are generated based on a time type for a partitioning key that is not of a time type. The following quick reference tables can help you quickly write the correct SQL expression.
Expressions for generating upper bounds for partitions in MySQL compatible mode
| Sample partitioning expression | Description | Time interval | SQL expression for calculating the upper bounds of partitions |
... values less than ('2024') |
Generate by year | 1 | date_format(now() + interval ${INTERVAL} year, '''%Y''') |
... values less than ('202401') |
Generate by year | 1 | date_format(now() + interval ${INTERVAL} year, '''%Y01''') |
| Generate by month | 1 | date_format(now() + interval ${INTERVAL} month, '''%Y%m''') |
|
... values less than ('2024-01') |
Generate by year | 1 | date_format(now() + interval ${INTERVAL} year, '''%Y-01''') |
| Generate by month | 1 | date_format(now() + interval ${INTERVAL} month, '''%Y-%m''') |
|
... values less than ('2024/01') |
Generate by year | 1 | date_format(now() + interval ${INTERVAL} year, '''%Y/01''') |
| Generate by month | 1 | date_format(now() + interval ${INTERVAL} month, '''%Y/%m''') |
|
... values less than ('20240101') |
Generate by year | 1 | date_format(now() + interval ${INTERVAL} year, '''%Y0101''') |
| Generate by month | 1 | date_format(now() + interval ${INTERVAL} month, '''%Y%m01''') |
|
| Generate by day | 1 | date_format(now() + interval ${INTERVAL} day, '''%Y%m%d''') |
|
... values less than ('2024-01-01') |
Generate by year | 1 | date_format(now() + interval ${INTERVAL} year, '''%Y-01-01''') |
| Generate by month | 1 | date_format(now() + interval ${INTERVAL} month, '''%Y-%m-01''') |
|
| Generate by day | 1 | date_format(now() + interval ${INTERVAL} day, '''%Y-%m-%d''') |
|
... values less than ('2024/01/01') |
Generate by year | 1 | date_format(now() + interval ${INTERVAL} year, '''%Y/01/01''') |
| Generate by month | 1 | date_format(now() + interval ${INTERVAL} month, '''%Y/%m/01''') |
|
| Generate by day | 1 | date_format(now() + interval ${INTERVAL} day, '''%Y/%m/%d''') |
|
... values less than ('2024-01-01 00:00:00') |
Generate by year | 1 | date_format(now() + interval ${INTERVAL} year, '''%Y-01-01 00:00:00''') |
| Generate by month | 1 | date_format(now() + interval ${INTERVAL} month, '''%Y-%m-01 00:00:00''') |
|
| Generate by day | 1 | date_format(now() + interval ${INTERVAL} day, '''%Y-%m-%d 00:00:00''') |
|
... values less than ('2024/01/01 00:00:00') |
Generate by year | 1 | date_format(now() + interval ${INTERVAL} year, '''%Y/01/01 00:00:00''') |
| Generate by month | 1 | date_format(now() + interval ${INTERVAL} month, '''%Y/%m/01 00:00:00''') |
|
| Generate by day | 1 | date_format(now() + interval ${INTERVAL} day, '''%Y/%m/%d 00:00:00''') |
|
... values less than (2024) |
Generate by year | 1 | date_format(now() + interval ${INTERVAL} year, '%Y') |
... values less than (202401) |
Generate by year | 1 | date_format(now() + interval ${INTERVAL} year, '%Y01') |
| Generate by month | 1 | date_format(now() + interval ${INTERVAL} month, '%Y%m') |
|
... values less than (20240101) |
Generate by year | 1 | date_format(now() + interval ${INTERVAL} year, '%Y0101') |
| Generate by month | 1 | date_format(now() + interval ${INTERVAL} month, '%Y%m01') |
|
| Generate by day | 1 | date_format(now() + interval ${INTERVAL} day, '%Y%m%d') |
|
... values less than (1709222400)Note: Unix timestamp |
Generate by year | 1 | unix_timestamp(str_to_date(date_format(now() + interval ${INTERVAL} year, '%Y-01-01 00:00:00'), '%Y-%m-%d %H:%i:%s')) |
| Generate by month | 1 | unix_timestamp(str_to_date(date_format(now() + interval ${INTERVAL} month, '%Y-%m-01 00:00:00'), '%Y-%m-%d %H:%i:%s')) |
|
| Generate by day | 1 | unix_timestamp(str_to_date(date_format(now() + interval ${INTERVAL} day, '%Y-%m-%d 00:00:00'), '%Y-%m-%d %H:%i:%s')) |
Expressions for generating upper bounds for partitions in Oracle compatible mode
| Sample partitioning expression | Description | Time interval | SQL expression for calculating the upper bounds of partitions |
... values less than ('2024') |
Generate by year | NUMTOYMINTERVAL(1, 'YEAR') |
TO_CHAR(SYSDATE + ${INTERVAL}, '''YYYY''') |
... values less than ('202401') |
Generate by year | NUMTOYMINTERVAL(1, 'YEAR') |
TO_CHAR(SYSDATE + ${INTERVAL}, '''YYYY"01"''') |
| Generate by month | NUMTOYMINTERVAL(1, 'MONTH') |
TO_CHAR(SYSDATE + ${INTERVAL}, '''YYYYMM''') |
|
... values less than ('2024-01') |
Generate by year | NUMTOYMINTERVAL(1, 'YEAR') |
TO_CHAR(SYSDATE + ${INTERVAL}, '''YYYY-"01"''') |
| Generate by month | NUMTOYMINTERVAL(1, 'MONTH') |
TO_CHAR(SYSDATE + ${INTERVAL}, '''YYYY-MM''') |
|
... values less than ('2024/01') |
Generate by year | NUMTOYMINTERVAL(1, 'YEAR') |
TO_CHAR(SYSDATE + ${INTERVAL}, '''YYYY/"01"''') |
| Generate by month | NUMTOYMINTERVAL(1, 'MONTH') |
TO_CHAR(SYSDATE + ${INTERVAL}, '''YYYY/MM''') |
|
... values less than ('20240101') |
Generate by year | NUMTOYMINTERVAL(1, 'YEAR') |
TO_CHAR(SYSDATE + ${INTERVAL}, '''YYYY"01""01"''') |
| Generate by month | NUMTOYMINTERVAL(1, 'MONTH') |
TO_CHAR(SYSDATE + ${INTERVAL}, '''YYYYMM"01"''') |
|
| Generate by day | NUMTODSINTERVAL(1, 'DAY') |
TO_CHAR(SYSDATE + ${INTERVAL}, '''YYYYMMDD''') |
|
... values less than ('2024-01-01') |
Generate by year | NUMTOYMINTERVAL(1, 'YEAR') |
TO_CHAR(SYSDATE + ${INTERVAL}, '''YYYY-"01"-"01"''') |
| Generate by month | NUMTOYMINTERVAL(1, 'MONTH') |
TO_CHAR(SYSDATE + ${INTERVAL}, '''YYYY-MM-"01"''') |
|
| Generate by day | NUMTODSINTERVAL(1, 'DAY') |
TO_CHAR(SYSDATE + ${INTERVAL}, '''YYYY-MM-DD''') |
|
... values less than ('2024/01/01') |
Generate by year | NUMTOYMINTERVAL(1, 'YEAR') |
TO_CHAR(SYSDATE + ${INTERVAL}, '''YYYY/"01"/"01"''') |
| Generate by month | NUMTOYMINTERVAL(1, 'MONTH') |
TO_CHAR(SYSDATE + ${INTERVAL}, '''YYYY/MM/"01"''') |
|
| Generate by day | NUMTODSINTERVAL(1, 'DAY') |
TO_CHAR(SYSDATE + ${INTERVAL}, '''YYYY/MM/DD''') |
|
... values less than ('2024-01-01 00:00:00') |
Generate by year | NUMTOYMINTERVAL(1, 'YEAR') |
TO_CHAR(SYSDATE + ${INTERVAL}, '''YYYY-"01"-"01 00:00:00"''') |
| Generate by month | NUMTOYMINTERVAL(1, 'MONTH') |
TO_CHAR(SYSDATE + ${INTERVAL}, '''YYYY-MM-"01 00:00:00"''') |
|
| Generate by day | NUMTODSINTERVAL(1, 'DAY') |
TO_CHAR(SYSDATE + ${INTERVAL}, '''YYYY-MM-DD "00:00:00"''') |
|
... values less than ('2024/01/01 00:00:00') |
Generate by year | NUMTOYMINTERVAL(1, 'YEAR') |
TO_CHAR(SYSDATE + ${INTERVAL}, '''YYYY/"01"/"01 00:00:00"''') |
| Generate by month | NUMTOYMINTERVAL(1, 'MONTH') |
TO_CHAR(SYSDATE + ${INTERVAL}, '''YYYY/MM/"01 00:00:00"''') |
|
| Generate by day | NUMTODSINTERVAL(1, 'DAY') |
TO_CHAR(SYSDATE + ${INTERVAL}, '''YYYY/MM/DD "00:00:00"''') |
|
... values less than (2024) |
Generate by year | NUMTOYMINTERVAL(1, 'YEAR') |
TO_CHAR(SYSDATE + ${INTERVAL}, 'YYYY') |
... values less than (202401) |
Generate by year | NUMTOYMINTERVAL(1, 'YEAR') |
TO_CHAR(SYSDATE + ${INTERVAL}, 'YYYY"01"') |
| Generate by month | NUMTOYMINTERVAL(1, 'MONTH') |
TO_CHAR(SYSDATE + ${INTERVAL}, 'YYYYMM') |
|
... values less than (20240101) |
Generate by year | NUMTOYMINTERVAL(1, 'YEAR') |
TO_CHAR(SYSDATE + ${INTERVAL}, 'YYYY"01""01"') |
| Generate by month | NUMTOYMINTERVAL(1, 'MONTH') |
TO_CHAR(SYSDATE + ${INTERVAL}, 'YYYYMM"01"') |
|
| Generate by day | NUMTODSINTERVAL(1, 'DAY') |
TO_CHAR(SYSDATE + ${INTERVAL}, 'YYYYMMDD') |
Sequential increased partition creation strategy
Sequential increase can be used to conveniently manage partition keys of numeric, date, and character types. The base time for sequential increase can be set to the current time or a specified time, and new partitions are generated at user-defined intervals.
Partition name generation strategy
You can use different strategies to generate partition names related to the content of the partitions.
At present, two partition name generation strategies are provided:
Prefix + suffix strategy: A partition name generated based on this strategy comprises a prefix and a suffix. The prefix is a string constant, and the suffix references the SQL expression value of the specified partitioning key of a time type. You can modify the prefix, suffix, reference time, and output format to generate different partition names.
Notice
This strategy takes effect only for partitioning keys of a time type. Since the partitioning key referenced by the suffix is of a time type, its SQL expression value can be converted into a time value and then formatted and output as the suffix of the partition name.
Custom strategy: This strategy uses a naming expression to generate partition names that meet user requirements. The SQL expression value of the partitioning key can be referenced in the naming expression through
${partitioning key}.
The following quick reference tables list the common expressions provided by ODC for generating partition names. Partitioning keys in the tables must be of the numeric or character type.
Expressions for generating partition names in MySQL compatible mode
| Sample partitioning expression | Naming expression | Preview |
... values less than (..., 2024, ...) |
concat('P', ${Partitioning key}) |
P2024 |
... values less than (..., '2024', ...) |
||
... values less than (..., 202401, ...) |
concat('P', ${Partitioning key}) |
P202401 |
... values less than (..., '202401', ...) |
||
... values less than (..., '2024-01', ...) |
date_format(str_to_date(concat(${Partitioning key}, '-01'), '%Y-%m-%d'), 'P%Y%m') |
P202401 |
date_format(str_to_date(concat(${Partitioning key}, '-01'), '%Y-%m-%d'), 'P%Y_%m') |
P2024_01 | |
... values less than (..., '2024/01', ...) |
date_format(str_to_date(concat(${Partitioning key}, '/01'), '%Y/%m/%d'), 'P%Y%m') |
P202401 |
date_format(str_to_date(concat(${Partitioning key}, '/01'), '%Y/%m/%d'), 'P%Y_%m') |
P2024_01 | |
... values less than (..., 20240101, ...) |
concat('P', ${Partitioning key}) |
P20240101 |
... values less than (..., '20240101', ...) |
||
... values less than (..., '2024-01-01', ...) |
date_format(str_to_date(${Partitioning key}, '%Y-%m-%d'), 'P%Y%m%d') |
P20240101 |
date_format(str_to_date(${Partitioning key}, '%Y-%m-%d'), 'P%Y_%m_%d') |
P2024_01_01 | |
... values less than (..., '2024/01/01', ...) |
date_format(str_to_date(${Partitioning key}, '%Y/%m/%d'), 'P%Y%m%d') |
P20240101 |
date_format(str_to_date(${Partitioning key}, '%Y/%m/%d'), 'P%Y_%m_%d') |
P2024_01_01 | |
... values less than (..., '2024-01-01 00:00:00', ...) |
date_format(str_to_date(${Partitioning key}, '%Y-%m-%d %H:%i:%s'), 'P%Y%m%d') |
P20240101 |
date_format(str_to_date(${Partitioning key}, '%Y-%m-%d %H:%i:%s'), 'P%Y_%m_%d') |
P2024_01_01 | |
... values less than (..., '2024/01/01 00:00:00', ...) |
date_format(str_to_date(${Partitioning key}, '%Y/%m/%d %H:%i:%s'), 'P%Y%m%d') |
P20240101 |
date_format(str_to_date(${Partitioning key}, '%Y/%m/%d %H:%i:%s'), 'P%Y_%m_%d') |
P2024_01_01 | |
... values less than (..., 1709222400, ...) Note: timestamp, in seconds |
date_format(from_unixtime(${Partitioning key}), 'P%Y') |
P2024 |
date_format(from_unixtime(${Partitioning key}), 'P%Y%m') |
P202401 | |
date_format(from_unixtime(${Partitioning key}), 'P%Y_%m') |
P2024_01 | |
date_format(from_unixtime(${Partitioning key}), 'P%Y%m%d') |
P20240101 | |
date_format(from_unixtime(${Partitioning key}), 'P%Y_%m_%d') |
P2024_01_01 |
Expressions for generating partition names in Oracle compatible mode
| Sample partitioning expression | Naming expression | Preview |
... values less than (..., 2024, ...) |
concat('P', ${Partitioning key}) |
P2024 |
... values less than (..., '2024', ...) |
||
... values less than (..., 202401, ...) |
concat('P', ${Partitioning key}) |
P202401 |
... values less than (..., '202401', ...) |
||
... values less than (..., '2024-01', ...) |
TO_CHAR(TO_DATE(CONCAT(${Partitioning key}, '-01'), 'YYYY-MM-DD'), '"P"YYYYMM') |
P202401 |
TO_CHAR(TO_DATE(CONCAT(${Partitioning key}, '-01'), 'YYYY-MM-DD'), '"P"YYYY_MM') |
P2024_01 | |
... values less than (..., '2024/01', ...) |
TO_CHAR(TO_DATE(CONCAT(${Partitioning key}, '/01'), 'YYYY/MM/DD'), '"P"YYYYMM') |
P202401 |
TO_CHAR(TO_DATE(CONCAT(${Partitioning key}, '/01'), 'YYYY/MM/DD'), '"P"YYYY_MM') |
P2024_01 | |
... values less than (..., 20240101, ...) |
concat('P', ${Partitioning key}) |
P20240101 |
... values less than (..., '20240101', ...) |
||
... values less than (..., '2024-01-01', ...) |
TO_CHAR(TO_DATE(${Partitioning key}, 'YYYY-MM-DD'), '"P"YYYYMMDD') |
P20240101 |
TO_CHAR(TO_DATE(${Partitioning key}, 'YYYY-MM-DD'), '"P"YYYY_MM_DD') |
P2024_01_01 | |
... values less than (..., '2024/01/01', ...) |
TO_CHAR(TO_DATE(${Partitioning key}, 'YYYY/MM/DD'), '"P"YYYYMMDD') |
P20240101 |
TO_CHAR(TO_DATE(${Partitioning key}, 'YYYY/MM/DD'), '"P"YYYY_MM_DD') |
P2024_01_01 | |
... values less than (..., '2024-01-01 00:00:00', ...) |
TO_CHAR(TO_DATE(${Partitioning key}, 'YYYY-MM-DD HH24:MI:SS'), '"P"YYYYMMDD') |
P20240101 |
TO_CHAR(TO_DATE(${Partitioning key}, 'YYYY-MM-DD HH24:MI:SS'), '"P"YYYY_MM_DD') |
P2024_01_01 | |
... values less than (..., '2024/01/01 00:00:00', ...) |
TO_CHAR(TO_DATE(${Partitioning key}, 'YYYY/MM/DD HH24:MI:SS'), '"P"YYYYMMDD') |
P20240101 |
TO_CHAR(TO_DATE(${Partitioning key}, 'YYYY/MM/DD HH24:MI:SS'), '"P"YYYY_MM_DD') |
P2024_01_01 |
Partition deletion strategy
A partition deletion strategy applies to existing partitions. At present, only one deletion strategy is supported, where the latest N partitions are retained and others are deleted. The related configurations are as follows:
Configuration |
Description |
|---|---|
| Number of Reserved Partitions | Retains the latest N partitions and deletes all other partitions. The setting of the number of reserved partitions must consider the impact of pre-created partitions and avoid mistakenly deleting historical partitions. For example, assume that partitions are generated for a table by month. To retain partitions generated in the last 12 months, set Number of Reserved Partitions to 12. If the partition creation strategy pre-creates partitions for three months, partitions created in the last nine months and future three months will be actually retained. |
| Set Partition Data Retention Time | Retains only the partitions within the specified time range and deletes all other partitions. You can use the TIME or parti_key (timestamp or date) method to calculate the time range.
NoteWhen you delete a partition, ensure that the upper bound of the partition is less than the retention time point. Otherwise, the partition will not be deleted. |
| Rebuild Index after Deletion | Specifies whether to rebuild the index after the partition is deleted. |
Here is an example:
Create a table named
orderthat contains thep2023_01andp2023_02partitions.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))Configure a partition deletion strategy for the
ordertable.The partition deletion statement formed based on the partition deletion strategy will delete the
p2023_01partition.