oracle exchange partition索引失效问题

        Exchange partition提供了一种方式，让你在表与表或分区与分区之间迁移数据，注意不是将表转换成分区或非分区的形式，而仅只是迁移表中数据(互相迁移)，由于其号称是采用了更改数据字典的方式，因此效率最高(几乎不涉及io操作)。Exchange partition适用于所有分区格式，你可以将数据从分区表迁移到非分区表，也可以从非分区表迁移至分区表，但不支持range partition 和range partition之间交换。

实验：

（一） 创建本地索引SQL> CREATE TABLE ou_a (a INTEGER)
  2  PARTITION BY RANGE(a)
  3  (PARTITION p1 VALUES LESS THAN (5),
  4   PARTITION p2 VALUES LESS THAN (6),
  5  PARTITION p3 VALUES LESS THAN (7),
  6  PARTITION p4 VALUES LESS THAN (8),
  7  PARTITION p5 VALUES LESS THAN (9)
  8  );
 
Table created
SQL> insert into ou_a values(5);
 
1 row inserted
SQL> insert into ou_a values(6);
 
1 row inserted
SQL> insert into ou_a values(7);
 
1 row inserted
SQL> insert into ou_a values(8);
 
1 row inserted
SQL> commit;
 
Commit complete
SQL> create index index_ou on ou_a(a) local;
 
Index created
SQL> create table ou_temp (a integer);
 
Table created
SQL> insert into ou_temp values(8);
 
1 row inserted
SQL> commit;
 
Commit complete
SQL> alter table ou_a exchange partition p2  with table ou_temp;
 
alter table ou_a exchange partition p2  with table ou_temp
 
ORA-14099: 未对指定分区限定表中的所有行
此处说明当交换分区表时，临时表的数据分区键值超出分区的临界值时会报ORA-14099错误，如需要屏蔽该错误，则需使用Without validation，如下：

SQL> alter table ou_a exchange partition p2  with table ou_temp without validation;
 
Table altered

SQL> select a.Partition_Name, a.status from User_Ind_Partitions a where a.Index_Name = 'INDEX_OU';
 
PARTITION_NAME                 STATUS
------------------------------ --------
P1                             USABLE
P2                             UNUSABLE
P3                             USABLE
P4                             USABLE

此时查看索引状态会发现，交换分区后本地索引在分区p2里失效，变为不可用。

SQL> select * from ou_a where a=8;
 
                   A
--------------------
                   8
 
SQL> select * from ou_a;
 
                   A
--------------------
                   8
                   6
                   7
                   8

当索引失效时，查看全表是可以查到两条为8的数据，而走where a=8时确只能查到一条数据。这是因为where a=8时，oracle通过分区修剪去找分区p5，而此时因为8的两个值一个存储在p2一个存储在p5，所以说当使用without validation时会造成很多无效的数据，同时亦会造成索引失效的问题。

那如何保证索引不失效勒，oracle提供了一个参数including indexes，可保证交换分区后索引是有效的。如下：

SQL> CREATE TABLE ou_a (a INTEGER)
  2  PARTITION BY RANGE(a)
  3  (PARTITION p1 VALUES LESS THAN (5),
  4   PARTITION p2 VALUES LESS THAN (6),
  5  PARTITION p3 VALUES LESS THAN (7),
  6  PARTITION p4 VALUES LESS THAN (8),
  7  PARTITION p5 VALUES LESS THAN (9)
  8  );
 
Table created
SQL> insert into ou_a values(5);
 
1 row inserted
SQL> insert into ou_a values(6);
 
1 row inserted
SQL> insert into ou_a values(7);
 
1 row inserted
SQL> insert into ou_a values(8);
 
1 row inserted
SQL> commit;
 
Commit complete
SQL> create index index_ou on ou_a(a) local;
 
Index created
SQL> create table ou_temp (a integer);
 
Table created
SQL> create index index_temp on ou_temp(a);
 
Index created
SQL> insert into ou_temp values(8);
 
1 row inserted
SQL> commit;
 
Commit complete
SQL> alter table ou_a exchange partition p2  with table ou_temp including indexes without validation;
 
Table altered

 SQL> select status from User_Ind_Partitions a where a.Index_Name = 'INDEX_OU';
 
STATUS
--------
USABLE
USABLE
USABLE
USABLE
USABLE

此时发现索引交换过来了，说明including indexes可以将索引交换过来。

以上实验 的知识点

1、exchange partition，交换分区是分区表与表之间的交换，不支持分区表与分区表之间的交换，可做实验验证。同时是数据的迁移过程。

1、without validation，可避免ORA-14099错误，但需注意的是有可能会造成索引失效问题。

2、including indexes，交换分区时可将索引相互交换，可用来避免索引的失效。

（二） 创建全局索引

建表

SQL> create table ou_part (a integer)
  2  partition by range(a)
  3  (
  4  PARTITION OU_PART_01 VALUES less than(10) tablespace TS_OU_01,
  5  partition ou_part_02 values less than(20) tablespace ts_ou_02,
  6  partition ou_part_03 values less than(30) tablespace ts_ou_03,
  7  partition ou_part_04 values less than(40) tablespace ts_ou_04
  8  );
 
Table created
 
Executed in 0.031 seconds
 
SQL> insert into ou_part values (1);
 
1 row inserted
 
Executed in 0 seconds
 
SQL> insert into ou_part values (2);
 
1 row inserted
 
Executed in 0 seconds
 
SQL> insert into ou_part values (3);
 
1 row inserted
 
Executed in 0 seconds
 
SQL> insert into ou_part values (11);
 
1 row inserted
 
Executed in 0 seconds
 
SQL> insert into ou_part values (12);
 
1 row inserted
 
Executed in 0 seconds
 
SQL> insert into ou_part values (13);
 
1 row inserted
 
Executed in 0 seconds
 
SQL> insert into ou_part values (21);
 
1 row inserted
 
Executed in 0 seconds
 
SQL> insert into ou_part values (22);
 
1 row inserted
 
Executed in 0 seconds
 
SQL> insert into ou_part values (23);
 
1 row inserted
 
Executed in 0 seconds
 
SQL> insert into ou_part values (31);
 
1 row inserted
 
Executed in 0 seconds
 
SQL> insert into ou_part values (32);
 
1 row inserted
 
Executed in 0 seconds
 
SQL> insert into ou_part values (33);
 
1 row inserted
 
Executed in 0 seconds
 
SQL> insert into ou_part values (35);
 
1 row inserted
 
Executed in 0 seconds
 
SQL> commit;
 
Commit complete
 
Executed in 0 seconds
 建全局索引
SQL> create index index_glo on ou_part (a) Global;
 
Index created
 
Executed in 0 seconds

中间表

SQL> create table t (a integer);
 
Table created
 
Executed in 0 seconds
 
SQL> insert into t values(51);
 
1 row inserted
 
Executed in 0 seconds
 
SQL> commit;
 
Commit complete

交换分区

SQL> alter table ou_part exchange partition OU_PART_01 with table t without validation;
 
Table altered
 
Executed in 0 seconds
 
SQL> select status from User_Indexes a where a.index_name = 'INDEX_GLO'  ;
 
STATUS
--------
UNUSABLE
 
Executed in 0 seconds

此时发现索引已失效，针对本地索引失效后并不会影响其他分区索引的查询，那全局索引失效后，会有什么后果勒？

SQL> select * from ou_part where a=11;
 
select * from ou_part where a=11
 
ORA-01502: 索引 'SCOTT.INDEX_GLO' 或这类索引的分区处于不可用状态

此时如果去查询表，则会报ORA-01502错误，Oracle针对索引可不用状态提供了一参数skip_unusable_indexes，默认值为false，表示是否跳过unusable索引。

当skip_unusable_indexes=true时，oracle Optimizer会跳过索引，不检查索引的状态。如下所示：

SQL> alter session set skip_unusable_indexes=true;
 
Session altered
 
Executed in 0 seconds
 
SQL> select * from ou_part where a=11;
 
                                      A
---------------------------------------
                                     11
 
Executed in 0 seconds

此时Oracle没有报ORA-01502错误。

此实验知识点：

1、交换分区会使全局索引失效

2、当全局索引失效且session set skip_unusable_indexes=false时，查询时会报ORA-01502错误。