Oceanbase V3 Oracle模式不支持分区表的split partition

原创张玉龙 2024-05-25

476

概念描述

Oceanbase Oracle模式不支持分区表的split partition，如果需要对分区表进行拆分，常规方法是先删除MAX分区然后再add partition，再把MAX分区也add上，但是这种拆分分区的方式不适合复合分区的场景，比如列表+时间的复合RANGE分区，分区里有多个部分地市的MAX分区，这样就不能通过删除中间的MAX分区再add partition。
Oceanbase 提供一个隐含参数_enable_add_between_range_partitions，可以强制在两个分区之间add partition，但是数据不会像Oracle的split partition一样可以自动迁移到新分区中。

测试验证

创建一个测试分区表，此分区表是列表+时间的复合RANGE分区表

DROP TABLE DBMT.TABLEA PURGE;
CREATE TABLE DBMT.TABLEA
 (    "REGION" NUMBER(5,0) DEFAULT 991 NOT NULL,
      "ID" VARCHAR2(512) NOT NULL,
      "CREATE_TIME" DATE DEFAULT SYSDATE NOT NULL
 ) 
PARTITION BY RANGE ("REGION","CREATE_TIME")(
 PARTITION "PART_991_202306"  VALUES LESS THAN (991, TO_DATE(' 2023-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
 PARTITION "PART_991_202307"  VALUES LESS THAN (991, TO_DATE(' 2023-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
 PARTITION "PART_991_MAX"     VALUES LESS THAN (991, TO_DATE(' 3000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
 PARTITION "PART_992_202306"  VALUES LESS THAN (992, TO_DATE(' 2023-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
 PARTITION "PART_992_202307"  VALUES LESS THAN (992, TO_DATE(' 2023-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
 PARTITION "PART_992_MAX"     VALUES LESS THAN (992, TO_DATE(' 3000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));
-- 创建一个全局索引
create index dbmt.idx_tablea_globle on dbmt.tablea(id);
-- 创建一个分区索引
create index dbmt.idx_tablea_local on dbmt.tablea(REGION) local;
复制

Oceanbase 不支持 split partition

obclient [SYS]> ALTER TABLE DBMT.TABLEA SPLIT PARTITION PART_991_MAX AT (991, TO_DATE(' 2023-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) INTO (PARTITION PART_991_202308, PARTITION PART_991_MAX);
ORA-00600: internal error code, arguments: -4179, split partition not allowed
复制

Oceanbase 隐含参数_enable_add_between_range_partitions默认是False，add partition命令会失败

MySQL [(none)]> select b.tenant_name,a.svr_ip,a.name,a.value,a.info 
from oceanbase.__all_virtual_tenant_parameter_info a, oceanbase.__all_tenant b 
where a.tenant_id=b.tenant_id and a.name = '_enable_add_between_range_partitions';

+-------------+---------------+--------------------------------------+-------+-------------------------------------------------+
| tenant_name | svr_ip        | name                                 | value | info                                            |
+-------------+---------------+--------------------------------------+-------+-------------------------------------------------+
| test        | 133.96.123.36 | _enable_add_between_range_partitions | True  | enable add range partition at not last position |
| test        | 133.96.123.37 | _enable_add_between_range_partitions | True  | enable add range partition at not last position |
| test        | 133.96.123.38 | _enable_add_between_range_partitions | True  | enable add range partition at not last position |
+-------------+---------------+--------------------------------------+-------+-------------------------------------------------+
3 rows in set (0.036 sec)
复制

obclient [SYS]> alter table DBMT.TABLEA add partition PART_991_202308 VALUES LESS THAN (991, TO_DATE(' 2023-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));
ORA-14074: partition bound must collate higher than that of the last partition
复制

插入一条测试数据，数据分布到PART_991_MAX分区中，并且这条数据也属于后面新添加的分区

obclient [SYS]> insert into DBMT.TABLEA values (991,'1',to_date('2023-08-02','YYYY-MM-DD'));
Query OK, 1 row affected (0.007 sec)

obclient [SYS]> commit;
Query OK, 0 rows affected (0.002 sec)

obclient [SYS]> select * from DBMT.TABLEA partition (PART_991_MAX);
+--------+----+---------------------+
| REGION | ID | CREATE_TIME         |
+--------+----+---------------------+
|    991 | 1  | 2023-08-02 00:00:00 |
+--------+----+---------------------+
1 row in set (0.010 sec)
复制

在sys租户下修改参数，业务租户下添加分区

-- 修改参数
MySQL [(none)]> alter system set _enable_add_between_range_partitions = 'True' tenant='test';
-- 查看参数
MySQL [(none)]> select b.tenant_name,a.svr_ip,a.name,a.value,a.info
    -> from oceanbase.__all_virtual_tenant_parameter_info a, oceanbase.__all_tenant b
    -> where a.tenant_id = b.tenant_id and a.name = '_enable_add_between_range_partitions' and b.tenant_name = 'test';
+-------------+---------------+--------------------------------------+-------+-------------------------------------------------+
| tenant_name | svr_ip        | name                                 | value | info                                            |
+-------------+---------------+--------------------------------------+-------+-------------------------------------------------+
| test        | 133.96.123.37 | _enable_add_between_range_partitions | True  | enable add range partition at not last position |
| test        | 133.96.123.36 | _enable_add_between_range_partitions | True  | enable add range partition at not last position |
| test        | 133.96.123.38 | _enable_add_between_range_partitions | True  | enable add range partition at not last position |
+-------------+---------------+--------------------------------------+-------+-------------------------------------------------+
3 rows in set (0.038 sec)
-- 添加分区
obclient [SYS]> alter table DBMT.TABLEA add partition PART_991_202308 VALUES LESS THAN (991, TO_DATE(' 2023-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));
Query OK, 0 rows affected (0.079 sec)
-- 查看刚才插入的数据并没有自动迁移到新分区中
obclient [SYS]> select * from DBMT.TABLEA partition (PART_991_MAX);
+--------+----+-------------+
| REGION | ID | CREATE_TIME |
+--------+----+-------------+
|    991 | 1  | 02-AUG-23   |
+--------+----+-------------+
1 row in set (0.003 sec)
复制

加完分区后，建议将参数再改回False

MySQL [(none)]> alter system set _enable_add_between_range_partitions = 'False' tenant='test';
复制

加分区，索引不会失效

obclient [SYS]> select owner, table_name, index_name, status from dba_indexes where table_name='TABLEA';
+-------+------------+-------------------+--------+
| OWNER | TABLE_NAME | INDEX_NAME        | STATUS |
+-------+------------+-------------------+--------+
| DBMT  | TABLEA     | IDX_TABLEA_GLOBLE | VALID  |
| DBMT  | TABLEA     | IDX_TABLEA_LOCAL  | VALID  |
+-------+------------+-------------------+--------+
2 rows in set (0.024 sec)
复制

加分区，local index也会自动加分区

obclient [SYS]> select index_owner, index_name, partition_position pos, partition_name from dba_ind_partitions where index_name = 'IDX_TABLEA_LOCAL';
+-------------+------------------+-----+-----------------+
| INDEX_OWNER | INDEX_NAME       | POS | PARTITION_NAME  |
+-------------+------------------+-----+-----------------+
| DBMT        | IDX_TABLEA_LOCAL |   1 | PART_991_202306 |
| DBMT        | IDX_TABLEA_LOCAL |   2 | PART_991_202307 |
| DBMT        | IDX_TABLEA_LOCAL |   3 | PART_991_202308 |
| DBMT        | IDX_TABLEA_LOCAL |   4 | PART_991_MAX    |
| DBMT        | IDX_TABLEA_LOCAL |   5 | PART_992_202306 |
| DBMT        | IDX_TABLEA_LOCAL |   6 | PART_992_202307 |
| DBMT        | IDX_TABLEA_LOCAL |   7 | PART_992_MAX    |
+-------------+------------------+-----+-----------------+
7 rows in set (0.023 sec)
复制