Set partition policies-ApsaraDB for OceanBase (Deprecated)(OceanBase)-阿里云帮助中心

This document describes partition creation policies and partition deletion policies.

Partition creation policies

Important

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 index may invalidate a table group. This is because all tables in the same table group must have identical partitions, and creating a new partition can disrupt this consistency.

Custom partition creation policy

A custom partition creation policy uses an SQL expression to generate the upper bound of a partition. This process involves three steps:

Obtain the base time using an SQL expression. The base time is the starting point for partition generation.

Note
To use the time when the partition plan runs as the start time, you can use now() or sysdate.
Define the partition generation interval. The partition plan uses this interval as the time step size to generate multiple partitions. After you enter the desired interval in the Interval input box, OceanBase Developer Center (ODC) uses the ${INTERVAL} expression to reference the interval and adds it to the SQL expression from Step 1.
Generate a partition filter expression for the corresponding partition key type. This converts the time-based SQL expression from the previous steps to the actual data type of the partition key. You can skip this step if the partition key type is the same as the SQL expression type.

Custom partition creation policies support variable references. The following table describes the supported expressions.

Expression name	Description	Example
INTERVAL	The time interval. It describes the step size for partition generation. At runtime, ODC replaces this variable reference with the content from Creation Rule > Details > Interval.	`${INTERVAL}`

Typically, when you use the Custom method to create a partition policy, you need to generate new partitions by incrementally increasing a non-time-based partition key as if it were a time-based key. The following tables can help you quickly write the correct SQL expressions.

Quick reference for partition upper bound generation expressions - MySQL mode

Partition filter expression example	Description	Interval	SQL expression for partition upper bound calculation
`... 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''')`
`... values less than ('202401')`	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''')`
`... values less than ('2024-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''')`
`... values less than ('2024/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')`
`... values less than (202401)`	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'))`

Quick reference for partition upper bound generation expressions - Oracle mode

Partition filter expression example	Description	Interval	SQL expression for partition upper bound calculation
`... 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"''')`
`... values less than ('202401')`	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"''')`
`... values less than ('2024-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"''')`
`... values less than ('2024/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"')`
`... values less than (202401)`	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 increment partition creation policy

Sequential increment provides a convenient way to manage numeric, time, and character type partition keys. The base time for sequential increment can be the current time or a specified time. New partitions are generated sequentially based on the configured time interval.

Partition name generation policy

You can use different policies to generate partition names. The generated partition name is associated with the partition's content.

Partition plans currently offer two types of partition name generation policies:

Prefix and suffix policy: The prefix is a string constant, and the suffix is a reference to a date-type partition filter expression. You can obtain different partition names by modifying the prefix, suffix, base time, and output format.

Important
This policy is valid only for date-type partition keys. The suffix references the value of the SQL expression that corresponds to the specified partition key. Because the referenced partition key is a time type, its SQL expression can be converted to a time value and then formatted as the suffix of the partition name.
Custom policy: This policy uses an SQL expression to generate custom partition names. In the naming expression, you can reference the value of the partition key's corresponding partition filter expression using ${partition_key}.

The following tables provide a quick reference for common partition name generation expressions. ODC provides this quick reference for numeric and character type partition keys only.

Quick reference for partition name generation expressions - MySQL mode

Referenced partition filter expression example	Naming convention expression	Preview
`... values less than (..., 2024, ...)`	`concat('P', ${partition_key})`	P2024
`... values less than (..., '2024', ...)`	`concat('P', ${partition_key})`	P2024
`... values less than (..., 202401, ...)`	`concat('P', ${partition_key})`	P202401
`... values less than (..., '202401', ...)`	`concat('P', ${partition_key})`	P202401
`... values less than (..., '2024-01', ...)`	`date_format(str_to_date(concat(${partition_key}, '-01'), '%Y-%m-%d'), 'P%Y%m')`	P202401
`... values less than (..., '2024-01', ...)`	`date_format(str_to_date(concat(${partition_key}, '-01'), '%Y-%m-%d'), 'P%Y_%m')`	P2024_01
`... values less than (..., '2024/01', ...)`	`date_format(str_to_date(concat(${partition_key}, '/01'), '%Y/%m/%d'), 'P%Y%m')`	P202401
`... values less than (..., '2024/01', ...)`	`date_format(str_to_date(concat(${partition_key}, '/01'), '%Y/%m/%d'), 'P%Y_%m')`	P2024_01
`... values less than (..., 20240101, ...)`	`concat('P', ${partition_key})`	P20240101
`... values less than (..., '20240101', ...)`	`concat('P', ${partition_key})`	P20240101
`... values less than (..., '2024-01-01', ...)`	`date_format(str_to_date(${partition_key}, '%Y-%m-%d'), 'P%Y%m%d')`	P20240101
`... values less than (..., '2024-01-01', ...)`	`date_format(str_to_date(${partition_key}, '%Y-%m-%d'), 'P%Y_%m_%d')`	P2024_01_01
`... values less than (..., '2024/01/01', ...)`	`date_format(str_to_date(${partition_key}, '%Y/%m/%d'), 'P%Y%m%d')`	P20240101
`... values less than (..., '2024/01/01', ...)`	`date_format(str_to_date(${partition_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(${partition_key}, '%Y-%m-%d %H:%i:%s'), 'P%Y%m%d')`	P20240101
`... values less than (..., '2024-01-01 00:00:00', ...)`	`date_format(str_to_date(${partition_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(${partition_key}, '%Y/%m/%d %H:%i:%s'), 'P%Y%m%d')`	P20240101
`... values less than (..., '2024/01/01 00:00:00', ...)`	`date_format(str_to_date(${partition_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(${partition_key}), 'P%Y')`	P2024
	`date_format(from_unixtime(${partition_key}), 'P%Y%m')`	P202401
	`date_format(from_unixtime(${partition_key}), 'P%Y_%m')`	P2024_01
	`date_format(from_unixtime(${partition_key}), 'P%Y%m%d')`	P20240101
	`date_format(from_unixtime(${partition_key}), 'P%Y_%m_%d')`	P2024_01_01

Quick reference for partition name generation expressions - Oracle mode

Referenced partition filter expression example	Naming convention expression	Preview
`... values less than (..., 2024, ...)`	`concat('P', ${partition_key})`	P2024
`... values less than (..., '2024', ...)`	`concat('P', ${partition_key})`	P2024
`... values less than (..., 202401, ...)`	`concat('P', ${partition_key})`	P202401
`... values less than (..., '202401', ...)`	`concat('P', ${partition_key})`	P202401
`... values less than (..., '2024-01', ...)`	`TO_CHAR(TO_DATE(CONCAT(${partition_key}, '-01'), 'YYYY-MM-DD'), '"P"YYYYMM')`	P202401
`... values less than (..., '2024-01', ...)`	`TO_CHAR(TO_DATE(CONCAT(${partition_key}, '-01'), 'YYYY-MM-DD'), '"P"YYYY_MM')`	P2024_01
`... values less than (..., '2024/01', ...)`	`TO_CHAR(TO_DATE(CONCAT(${partition_key}, '/01'), 'YYYY/MM/DD'), '"P"YYYYMM')`	P202401
`... values less than (..., '2024/01', ...)`	`TO_CHAR(TO_DATE(CONCAT(${partition_key}, '/01'), 'YYYY/MM/DD'), '"P"YYYY_MM')`	P2024_01
`... values less than (..., 20240101, ...)`	`concat('P', ${partition_key})`	P20240101
`... values less than (..., '20240101', ...)`	`concat('P', ${partition_key})`	P20240101
`... values less than (..., '2024-01-01', ...)`	`TO_CHAR(TO_DATE(${partition_key}, 'YYYY-MM-DD'), '"P"YYYYMMDD')`	P20240101
`... values less than (..., '2024-01-01', ...)`	`TO_CHAR(TO_DATE(${partition_key}, 'YYYY-MM-DD'), '"P"YYYY_MM_DD')`	P2024_01_01
`... values less than (..., '2024/01/01', ...)`	`TO_CHAR(TO_DATE(${partition_key}, 'YYYY/MM/DD'), '"P"YYYYMMDD')`	P20240101
`... values less than (..., '2024/01/01', ...)`	`TO_CHAR(TO_DATE(${partition_key}, 'YYYY/MM/DD'), '"P"YYYY_MM_DD')`	P2024_01_01
`... values less than (..., '2024-01-01 00:00:00', ...)`	`TO_CHAR(TO_DATE(${partition_key}, 'YYYY-MM-DD HH24:MI:SS'), '"P"YYYYMMDD')`	P20240101
`... values less than (..., '2024-01-01 00:00:00', ...)`	`TO_CHAR(TO_DATE(${partition_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(${partition_key}, 'YYYY/MM/DD HH24:MI:SS'), '"P"YYYYMMDD')`	P20240101
`... values less than (..., '2024/01/01 00:00:00', ...)`	`TO_CHAR(TO_DATE(${partition_key}, 'YYYY/MM/DD HH24:MI:SS'), '"P"YYYY_MM_DD')`	P2024_01_01

Partition deletion policies

Important

If a table contains a global index, deleting a partition will invalidate the global index. Perform this operation with caution. Rebuilding a global index can be time-consuming and may fail, which can cause business disruptions or other online issues.
The option to rebuild a global index is only available in Oracle mode. This option is not available in MySQL mode because MySQL mode rebuilds the global index by default.
When you set a partition deletion policy, consider the impact of pre-created partitions to avoid accidental deletion of historical partitions. For example, a target table is partitioned by month, and you want to keep the partitions for the last 12 months. You enter 12 for Number of partitions to retain. If the creation policy pre-creates partitions for the next 3 months, the system will retain partitions for the past 9 months and the future 3 months.

A partition deletion policy applies to existing partitions and supports the following configurations:

Number of partitions to retain: Retain the N most recent partitions and delete all others.
Set partition data retention period: A partition is deleted only if its upper bound is earlier than the specified retention time point. Otherwise, the partition is not deleted.
Rebuild index after deletion: Controls whether to automatically rebuild the index after deleting a partition to ensure index integrity and query performance.

Example:

Create the order table with two partitions: p2023_01 and p2023_02.

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 policy for the order table.
The partition deletion statement generated based on the policy will delete the p2023_01 partition.

Notes

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 index may invalidate a table group. This is because all tables in the same table group must have identical partitions, and creating a new partition can disrupt this consistency.
If a table contains a global index, deleting a partition will invalidate the global index. Perform this operation with caution. Rebuilding a global index can be time-consuming and may fail, which can cause business disruptions or other online issues.
The option to rebuild a global index is only available in Oracle mode. This option is not available in MySQL mode because MySQL mode rebuilds the global index by default.