Oceanbase V3 分区表 truncate/drop 空分区对全局索引的影响

概念描述

在 Oceanbase 上，truncate/drop partition 会导致全局索引失效，即使分区是空分区，全局索引也会失效，在 Oracle 数据库上执行相同的测试，truncate/drop partition 空分区，全局索引不会失效。

测试验证

1. truncate partition 空分区，全局索引会失效

-- 创建测试表
drop table dbmt.tablea;
create table dbmt.tablea(id number,c varchar2(100)) partition by range(id) (partition p1 values less than(100), partition p2 values less than(200));
-- 创建全局索引
create index dbmt.idx_tablea on dbmt.tablea(id);
-- 执行 truncate partition
alter table dbmt.tablea truncate partition p1;
-- 查看全局索引状态
obclient [SYS]> select owner, table_name, index_name, status from dba_indexes where index_name = 'IDX_TABLEA';
+-------+------------+------------+----------+
| OWNER | TABLE_NAME | INDEX_NAME | STATUS   |
+-------+------------+------------+----------+
| DBMT  | TABLEA     | IDX_TABLEA | UNUSABLE |
+-------+------------+------------+----------+
1 row in set (0.003 sec)
复制

2. drop partition 空分区，全局索引会失效

-- 创建测试表
drop table dbmt.tablea;
create table dbmt.tablea(id number,c varchar2(100)) partition by range(id) (partition p1 values less than(100), partition p2 values less than(200));
-- 创建全局索引
create index dbmt.idx_tablea on dbmt.tablea(id);
-- 执行 truncate partition
alter table dbmt.tablea drop partition p1;
-- 查看全局索引状态
obclient [SYS]> select owner, table_name, index_name, status from dba_indexes where index_name = 'IDX_TABLEA';
+-------+------------+------------+----------+
| OWNER | TABLE_NAME | INDEX_NAME | STATUS   |
+-------+------------+------------+----------+
| DBMT  | TABLEA     | IDX_TABLEA | UNUSABLE |
+-------+------------+------------+----------+
1 row in set (0.003 sec)
复制

处理失效的全局索引

• Oceanbase 不支持 rebuild index，出现索引失效，只能删除重建

obclient [SYS]> alter index DBMT.IDX_TABLEA rebuild;
ORA-00900: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'rebuild' at line 1

obclient [SYS]> drop index DBMT.IDX_TABLEA;
Query OK, 0 rows affected (0.065 sec)

obclient [SYS]> create index dbmt.idx_tablea on dbmt.tablea(id);
Query OK, 0 rows affected (0.964 sec)

obclient [SYS]> select owner, table_name, index_name, status from dba_indexes where index_name = 'IDX_TABLEA';
Empty set (0.001 sec)
复制

• truncate/drop partition 加上 update global indexes 使其自动维护全局索引

alter table dbmt.tablea truncate partition p1 update global indexes;
alter table dbmt.tablea drop partition p1 update global indexes;
复制