ob add partition来解决split partition需求时带来的问题

概述

OB不支持SPLIT PARTITION，但实际业务场景下不可避免会有该需求，比如复合分区，需要按其中一个分区键增加分区。

为了解决该需求，OB可以通过设置隐藏参数_enable_add_between_range_partitions为true，支持RANGE分区，直接在2个分区之间add partition而不检查边界。但该方案会带来问题，oracle split partition执行时会检查待split的分区中的数据是否要在新分区之间移动来满足分区边界约束，OB add partition单纯在schema级别做新增分区的操作，并不会移动存量数据，可能会导致在add partition后部分存量所在分区不正确，业务SQL按照新的分区边界做分区裁剪后无法获取到数据。

示例

1、业务表TEST.TESTTAB1按AREA,CYCLE进行分区,CYCLE为日期202401,202402这样的数据，需要定期新增分区。

obclient [SYS]> show create table  TEST.TESTTAB1\G
*************************** 1. row ***************************
       TABLE: TESTTAB1
CREATE TABLE: CREATE TABLE "TESTTAB1" (
  "CYCLE" NUMBER(6),
  "AREA" NUMBER(6),
  ......
) COMPRESS FOR ARCHIVE REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by range("AREA","CYCLE")
(
......
partition PART_100_202307 values less than (100,202308),
partition PART_100_202308 values less than (100,202309),
partition PART_100_202309 values less than (100,202310),
partition PART_100_202310 values less than (100,202311),
partition PART_100_202311 values less than (100,202312),
partition PART_100_202312 values less than (100,202401),
partition PART_100_202401 values less than (100,202402),
partition PART_100_202402 values less than (100,202403),
partition PART_100_202403 values less than (100,202404),
partition PART_100_202404 values less than (100,202405),
partition PART_100_202405 values less than (100,202406),
partition PART_100_202406 values less than (100,202407),
partition PART_100_202407 values less than (100,202408),<<<<<该分区为新增分区，新增前已存在CYCLE为202407的数据，在PART_100_MAX中，新增分区后数据仍然在PART_100_MAX
partition PART_100_MAX values less than (100,300001),
......
)
1 row in set (0.007 sec)

2、业务反馈查询不到CYCLE=202407的数据

obclient [SYS]> select * from TEST.TESTTAB1 t
    -> where t.CYCLE = to_number (to_char(add_months(sysdate,1),'yyyymm'))
    -> and AREA = 100
    -> and crm_proc_flag is null;
Empty set (0.009 sec)

obclient [SYS]> 

3、检查发现表中是有202407数据,去掉条件CYCLE

select CYCLE,COUNT(*) from TEST.TESTTAB1 t
where
AREA = 100
and crm_proc_flag is null GROUP BY CYCLE;
+-----------+----------+
| CYCLE | COUNT(*) |
+-----------+----------+
|    202402 |   763471 |
|    202407 |   332411 |
+-----------+----------+
2 rows in set (0.874 sec)

4、通过查看业务SQL执行计划分区裁剪后会查PART_100_202407,但实际存量202407数据在PART_100_MAX中

range_key([T.AREA(0xff8f6df7d540)], [T.CYCLE(0xff8f6df7d840)], [T.__pk_increment(0xff8f6dfd8140)]), range(100,202407,MIN ; 100,202407,MAX),
分区裁剪到 PART_100_202407, 没有数据
partition PART_100_202407 values less than (100,202408),

数据在MAX分区中
partition PART_100_MAX values less than (100,300001)

obclient [SYS]> explain extended select * from TEST.TESTTAB1 t
    -> where t.CYCLE = to_number (to_char(add_months(sysdate,1),'yyyymm'))
    -> and AREA = 100
    -> and crm_proc_flag is null\G
*************************** 1. row ***************************
Query Plan: =============================================
|ID|OPERATOR            |NAME|EST. ROWS|COST|
---------------------------------------------
|0 |EXCHANGE IN REMOTE  |    |1        |47  |
|1 | EXCHANGE OUT REMOTE|    |1        |46  |
|2 |  TABLE SCAN        |T   |1        |46  |
=============================================

Outputs & filters: 
-------------------------------------
 .....
      range_key([T.AREA(0xff8f6df7d540)], [T.CYCLE(0xff8f6df7d840)], [T.__pk_increment(0xff8f6dfd8140)]), range(100,202407,MIN ; 100,202407,MAX), 
      range_cond([T.CYCLE(0xff8f6df7d840) = ?(0xff8f6df80d40)], [T.AREA(0xff8f6df7d540) = 100(0xff8f6df82c90)])


1 row in set (0.004 sec)