OceanBase 数据库禁止对于 range 分区的分区表删除分区后，再添加之前的分区，执行 add partition 会报 VALUES LESS THAN value must be strictly increasing for each partition。这么做的目的是为了保证分区内数据的准确性，防止新增旧分区后，有的分区中存在不属于该分区的数据。如果需要解除这个限制需要打开隐藏参数 _enable_add_between_range_partitions。但使用该参数需要注意：表中的数据跟要新增的旧分区不能存在交集。
直接添加之前删除分区，会直接报错。如果开启租户侧隐含参数
_enable_add_between_range_partitions后执行成功。测试例子如下。创建分区表 ENT_SHAREHOLDER_ALTER_HIS 。
MySQL [test]> CREATE TABLE ENT_SHAREHOLDER_ALTER_HIS ( ENT_NAME varchar(900) COLLATE utf8mb4_bin NOT NULL, ACC_DATE date NOT NULL ) partition by range columns(ACC_DATE) (partition p20230924 values less than ('2023-09-24'), partition p20230925 values less than ('2023-09-25'), partition p20230926 values less than ('2023-09-26'), partition p20230927 values less than ('2023-09-27'), partition p20230928 values less than ('2023-09-28'), partition p20230929 values less than ('2023-09-29'), partition p20230930 values less than ('2023-09-30'), partition p20231001 values less than ('2023-10-01'), partition p20231002 values less than ('2023-10-02')); Query OK, 0 rows affected (2.018 sec)删除分区 p20230926 。
MySQL [test]> alter table ENT_SHAREHOLDER_ALTER_HIS drop partition p20230926; Query OK, 0 rows affected (0.17 sec)添加旧分区 p20230926 。
MySQL [test]> alter table ENT_SHAREHOLDER_ALTER_HIS add partition (partition p20230926 values less than ('2023-09-26')); ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition设置租户配置项
_enable_add_between_range_partitions。MySQL [oceanbase]> alter system set _enable_add_between_range_partitions=true tenant='xxxx';设置租户配置项
_enable_add_between_range_partitions之后添加旧分区 p20230926 。MySQL [test]> alter table ENT_SHAREHOLDER_ALTER_HIS add partition (partition p20230926 values less than ('2023-09-26')); Query OK, 0 rows affected (0.15 sec)
如果在添加旧分区时候，此时有之前数据插入进来，会导致分区裁剪后的数据的正确性，测试例子如下。
删除分区 p20230926
MySQL [test]> alter table ENT_SHAREHOLDER_ALTER_HIS drop partition p20230926; Query OK, 0 rows affected (0.061 sec)向分区表 ENT_SHAREHOLDER_ALTER_HIS 插入数据。
MySQL [test2]> insert into ENT_SHAREHOLDER_ALTER_HIS values ('a','2023-09-26'); Query OK, 1 row affected (0.027 sec)MySQL [test2]> insert into ENT_SHAREHOLDER_ALTER_HIS values ('a','2023-09-26'); Query OK, 1 row affected (0.027 sec)MySQL [test2]> insert into ENT_SHAREHOLDER_ALTER_HIS values ('a','2023-09-25'); Query OK, 1 row affected (0.013 sec)查询分区数据。
MySQL [test2]> select * from ENT_SHAREHOLDER_ALTER_HIS partition (p20230927);输出结果如下：
+----------+------------+ | ENT_NAME | ACC_DATE | +----------+------------+ | a | 2023-09-26 | | a | 2023-09-26 | | a | 2023-09-25 | +----------+------------+ 3 rows in set (0.047 sec)添加旧分区 p20230926 。
MySQL [test]> alter table ENT_SHAREHOLDER_ALTER_HIS add partition (partition p20230926 values less than ('2023-09-26')); Query OK, 0 rows affected (0.21 sec)查询分区数据。
MySQL [test]> select * from ENT_SHAREHOLDER_ALTER_HIS partition (p20230926); Empty set (0.08 sec)通过 ACC_DATE 字段查询分区表 ENT_SHAREHOLDER_ALTER_HIS 。
MySQL [test]> select * from ENT_SHAREHOLDER_ALTER_HIS where ACC_DATE ='2023-09-25' ; Empty set (0.12 sec)展示通过 ACC_DATE 字段查询分区表 ENT_SHAREHOLDER_ALTER_HIS 的执行计划。
MySQL [test]> explain select * from ENT_SHAREHOLDER_ALTER_HIS where ACC_DATE ='2023-09-25';输出结果如下：
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ======================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| -------------------------------------------------------- |0 |TABLE SCAN|ent_shareholder_alter_his|990 |383 | ======================================================== Outputs & filters: ------------------------------------- 0 - output([ent_shareholder_alter_his.ENT_NAME], [ent_shareholder_alter_his.ACC_DATE]), filter(nil), access([ent_shareholder_alter_his.ACC_DATE], [ent_shareholder_alter_his.ENT_NAME]), partitions(p10) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.036 sec)
上述测试例子可以看到，开启隐藏参数
_enable_add_between_range_partitions后，当表中数据与新增的旧分区存在交集时候，进行添加分区操作后，分区裁剪下存在数据正确性问题。说明
计划中
p10裁剪出来的分区是新增的p20230926，综上所述不建议打开_enable_add_between_range_partitions来添加之前被删除的分区，如有依赖，需要保证，开启隐藏参数后添加之前的分区前，保证没有新增旧分区数据写入。
适用版本
OceanBase 数据库 V3.x 版本。
