17认证网概念描述
在 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 1Query OK, 0 rows affected (0.065 sec)
Query OK, 0 rows affected (0.964 sec)
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;想了解更多干货，可通过下方扫码关注
可扫码添加上智启元官方客服微信👇
