Oracle 分区表交换分区操作后索引失效的问题详解

由于交换分区采用的是修改元数据的方式，并不对物理数据做迁移，所以导致交换分区后索引失效

创建分区表

create table p(a NUMBER, b NUMBER , c varchar2(20),d varchar2(20))
partition by range(b)
(partition p1 values less than(100),
partition p2 values less than(200) ,
partition p3 values less than(300));

创建主键在a列

alter table p add constraint pk_p_a primary key(a);

创建local索引在b列

create index l_p_b on p(b) local;

创建global索引在c列

create index g_p_c on p(c)
GLOBAL PARTITION BY hash (c)
(partition pg1,partition pg2);

创建普通索引在d列

create index i_p_d on p(d);

创建普通表t

create table t(a NUMBER, b NUMBER , c varchar2(20),d varchar2(20));

创建主键

alter table t add constraint pk_t_a primary key(a);

如果a列没主键在执行EXCHANGE 时会报ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION。

两表插入数据

insert into p values(1,11,'a','aa');
insert into p values(2,111,'b','bb');
insert into t values(3,222,'c','cc');Commit;

查询索引状态

SQL> select INDEX_NAME,STATUS from dba_indexes where table_name='P';

INDEX_NAME                     STATUS
------------------------------ --------
PK_P_A                         VALID
L_P_B                          N/A
G_P_C                          N/A
I_P_D                          VALID

查询local索引状态

SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where index_name='L_P_B';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
L_P_B                          USABLE   P1
L_P_B                          USABLE   P2
L_P_B                          USABLE   P3

查询global索引状态

SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where index_name='G_P_C';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
G_P_C                          USABLE   PG1
G_P_C                          USABLE   PG2

SQL> select INDEX_NAME,STATUS from dba_indexes where table_name='T';

INDEX_NAME                     STATUS
------------------------------ --------
PK_T_A                         VALID

交换分区操作

ALTER TABLE p EXCHANGE PARTITION p3 WITH TABLE t ;

检查索引状态

SQL> select INDEX_NAME,STATUS from dba_indexes where table_name='P';
 
INDEX_NAME                     STATUS
------------------------------ --------
PK_P_A                         UNUSABLE
L_P_B                          N/A
G_P_C                          N/A
I_P_D                          UNUSABLE

SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where index_name='L_P_B';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
L_P_B                          USABLE   P1
L_P_B                          USABLE   P2
L_P_B                          UNUSABLE P3

SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where index_name='G_P_C';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
G_P_C                          UNUSABLE PG1
G_P_C                          UNUSABLE PG2

SQL> select INDEX_NAME,STATUS from dba_indexes where table_name='T';
 
INDEX_NAME                     STATUS
------------------------------ --------
PK_T_A                         UNUSABLE

结果：除了未被交换的分区的local类型索引有效，其它索引均失效。

重置数据

truncate table t;
truncate table p;

--注：表被truncate后索引状态会自动正常

insert into p values(1,11,'a','aa');
insert into p values(2,111,'b','bb');
insert into t values(3,222,'c','cc');Commit;

使用update indexes子句交换分区

ALTER TABLE p EXCHANGE PARTITION p3 WITH TABLE t update indexes;

检查索引状态

SQL> select INDEX_NAME,STATUS from dba_indexes where table_name='P';
 
INDEX_NAME                     STATUS
------------------------------ --------
PK_P_A                         VALID
L_P_B                          N/A
G_P_C                          N/A
I_P_D                          VALID

SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where index_name='L_P_B';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
L_P_B                          USABLE   P1
L_P_B                          USABLE   P2
L_P_B                          UNUSABLE P3

SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where index_name='G_P_C';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
G_P_C                          USABLE   PG1
G_P_C                          USABLE   PG2

SQL> select INDEX_NAME,STATUS from dba_indexes where table_name='T';
 
INDEX_NAME                     STATUS
------------------------------ --------
PK_T_A                         UNUSABLE

结果：分区表的主键索引、普通索引和全局索引状态都是正常的。被交换分区的local索引和普通表的主键索引都失效了。

重置数据

truncate table t;
truncate table p;

--注：表被truncate后索引状态会自动正常

insert into p values(1,11,'a','aa');
insert into p values(2,111,'b','bb');
insert into t values(3,222,'c','cc');Commit;

普通表在b列添加索引

Create index i_t_b on t(b);

使用update indexest和including indexes子句交换分区

ALTER TABLE p EXCHANGE PARTITION p3 WITH TABLE t including indexes update indexes;

结果：ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION

解决方案

使用update indexes子句交换分区可以最大的保证索引可用性。

使用update indexes子句执行完后分区表的主键索引、普通索引和全局索引状态都是正常的。被交换分区的local索引和普通表的索引都失效。