_enable_add_between_range_partitions 租户隐藏配置项的使用说明

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 后执行成功。测试例子如下。

  1. 创建分区表 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)
     

  2. 删除分区 p20230926 。

     MySQL [test]> alter table ENT_SHAREHOLDER_ALTER_HIS drop partition p20230926;
     Query OK, 0 rows affected (0.17 sec)
     

  3. 添加旧分区 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
     

  4. 设置租户配置项 _enable_add_between_range_partitions 。

     MySQL [oceanbase]> alter system set _enable_add_between_range_partitions=true tenant='xxxx';
     

  5. 设置租户配置项 _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)
     

• 如果在添加旧分区时候，此时有之前数据插入进来，会导致分区裁剪后的数据的正确性，测试例子如下。

  1. 删除分区 p20230926

     MySQL [test]> alter table ENT_SHAREHOLDER_ALTER_HIS drop partition p20230926;
     Query OK, 0 rows affected (0.061 sec)
     

  2. 向分区表 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)
     

  3. 查询分区数据。

     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)
     

  4. 添加旧分区 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)
     

  5. 查询分区数据。

     MySQL [test]>  select * from ENT_SHAREHOLDER_ALTER_HIS partition (p20230926);                                        
     Empty set (0.08 sec)
     

  6. 通过 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)
     

  7. 展示通过 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 版本。