oceanbase oracle模式分区表创建疑惑

OceanBase 技术问题
#1

【 使用环境 】测试环境
【 使用版本 】 OceanBase 3.2.2
【问题描述】alter table 添加一级分区和二级分区, 子分区名称相同添加成功了。但是用修改后得表查询出得创建sql在创建又会出现相同得子分区名的错误。
【复现sql】
CREATE TABLE zore_t2_f_rrr(col1 INT,col2 INT)
PARTITION BY RANGE(col1)
SUBPARTITION BY RANGE(col2)
(PARTITION p0 VALUES LESS THAN(100)
(SUBPARTITION sp0 VALUES LESS THAN(2020),
SUBPARTITION sp1 VALUES LESS THAN(2021)
),
PARTITION p1 VALUES LESS THAN(200)
(SUBPARTITION sp2 VALUES LESS THAN(2020),
SUBPARTITION sp3 VALUES LESS THAN(2021),
SUBPARTITION sp4 VALUES LESS THAN(2022)
)
);

添加一级分区：
ALTER TABLE zore_t2_f_rrr ADD PARTITION p2 VALUES LESS THAN(300)
(SUBPARTITION sp5 VALUES LESS THAN(2020),
SUBPARTITION sp6 VALUES LESS THAN(2021),
SUBPARTITION sp7 VALUES LESS THAN(2022)
);
添加二级分区：

ALTER TABLE zore_t2_f_rrr MODIFY PARTITION p1 ADD
SUBPARTITION sp5 VALUES LESS THAN(2023),
SUBPARTITION sp6 VALUES LESS THAN(2024);

用show create table 查询出的sql 语句创建表报错重复的分区名
obclient> show create table ZORE_T2_F_RRR;
CREATE TABLE “ZORE_T2_F_RRR” (
“COL1” NUMBER(38),
“COL2” NUMBER(38)
) COMPRESS FOR ARCHIVE REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
partition by range(col1) subpartition by range(col2)
(partition P0 values less than (100) (
subpartition SP0 values less than (2020),
subpartition SP1 values less than (2021)),
partition P1 values less than (200) (
subpartition SP2 values less than (2020),
subpartition SP3 values less than (2021),
subpartition SP4 values less than (2022),
subpartition SP5 values less than (2023),
subpartition SP6 values less than (2024)),
partition P2 values less than (300) (
subpartition SP5 values less than (2020),
subpartition SP6 values less than (2021),
subpartition SP7 values less than (2022)))

1 个赞
#3

你好，你提的这个技术问题牵涉到OceanBase企业版范围内的功能细节。针对此类问题，建议你通过以下方式寻求帮助：
1.如你所在的企业客户已签署OceanBase企业版销售合同，请你联系客户经理；
2.如你所在的企业客户尚未签署OceanBase企业版销售合同，你可通过OceanBase官网商务咨询页面留下你的联系方式，OceanBase企业版的业务顾问会在一个工作日内与你联系。
https://www.oceanbase.com/contactus?fromPage=https%3A%2F%2Fwww.oceanbase.com%2Fsoftwarecenter-enterprise&dataSources=softwarecenter-enterprise_footercontact_d2022

2 个赞
#4

在 OB 3.2.4.1 ORACLE 租户复现了这个问题 ， OB 在二级分区表的新增子分区和新建分区表上对二级分区名的唯一性没有执行相同的逻辑，导致出现这种异常/矛盾的数据。
这点跟 ORACLE 行为不一致，大概率是 BUG 。 等后续版本修复。

  • OB 执行报错
obclient [SYS]> CREATE TABLE "T_PART_ERR" (
    ->   "COL1" NUMBER,
    ->   "COL2" NUMBER
    -> ) COMPRESS FOR ARCHIVE REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
    ->  partition by range(col1) subpartition by range(col2)
    -> (partition P0 values less than (100) (
    -> subpartition SP0 values less than (2020),
    -> subpartition SP1 values less than (2021)),
    -> partition P1 values less than (200) (
    -> subpartition SP2 values less than (2020),
    -> subpartition SP3 values less than (2021),
    -> subpartition SP4 values less than (2022),
    -> subpartition SP5 values less than (2023),
    -> subpartition SP6 values less than (2024)),
    -> partition P2 values less than (300) (
    -> subpartition SP5 values less than (2020),
    -> subpartition SP6 values less than (2021),
    -> subpartition SP7 values less than (2022)));
ORA-14159: duplicate subpartition name SP5
obclient [SYS]> select last_trace_id() from dual;
+-----------------------------------+
| LAST_TRACE_ID()                   |
+-----------------------------------+
| YB420A00003E-0005FFA6D6CC9755-0-0 |
+-----------------------------------+
1 row in set (0.004 sec)

根据这个trace_id 查看 observer.log ，没有提示更多信息。

image
image1636×883 49 KB

查看元数据视图

SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, HIGH_VALUE, HIGH_VALUE_LENGTH, SUBPARTITION_POSITION 
FROM DBA_TAB_SUBPARTITIONS
WHERE table_name = 'T_PART'
ORDER BY TABLE_OWNER, TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME ;
TABLE_OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE HIGH_VALUE_LENGTH SUBPARTITION_POSITION
TPCC T_PART P0 SP0 2020 4 1
TPCC T_PART P0 SP1 2021 4 2
TPCC T_PART P1 SP2 2020 4 1
TPCC T_PART P1 SP3 2021 4 2
TPCC T_PART P1 SP4 2022 4 3
TPCC T_PART P1 SP5 2023 4 4
TPCC T_PART P1 SP6 2024 4 5
TPCC T_PART P2 SP5 2020 4 1
TPCC T_PART P2 SP6 2021 4 2
TPCC T_PART P2 SP7 2022 4 3
  • ORACLE 12C 执行
    ORACLE 12C 在新增子分区的时候就检查了子分区名的唯一性。
    image
    image717×168 3.2 KB
2 个赞