oracle交換分割槽所引起的索引失效問題探究測試

oracle交換分割槽（exchange partition）提供了一種方式，讓你在表與表或分割槽與分割槽之間遷移資料，由於其採用了更改資料字典的方式，幾乎不涉及io操作，因此效率極高。exchange partition適用於所有分割槽格式，可以將資料從分割槽表遷移到非分割槽表，也可以從非分割槽表遷移至分割槽表，但不支援range partition 和range partition之間交換。

探究測試一：建立本地索引

SQL>CREATE TABLE ou_a (a INTEGER)
    PARTITION BY RANGE(a)
    (PARTITION p1 VALUES LESS THAN (5),
     PARTITION p2 VALUES LESS THAN (6),
     PARTITION p3 VALUES LESS THAN (7),
     PARTITION p4 VALUES LESS THAN (8),
     PARTITION p5 VALUES LESS THAN (9)
    );

SQL> insert into ou_a values(5);

SQL> insert into ou_a values(6);

SQL> insert into ou_a values(7);

SQL> insert into ou_a values(8);

SQL> commit;

SQL> create index index_ou on ou_a(a) local;
SQL> create table ou_temp (a integer);
SQL> insert into ou_temp values(8);
SQL> commit;

SQL> 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> alter table ou_a exchange partition p2  with table ou_temp including indexes without validation;

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，交換分割槽是分割槽表與表之間的交換，不支援分割槽表與分割槽表之間的交換，可做實驗驗證。同時是資料的遷移過程。

2、without validation，可避免ORA-14099錯誤，但需注意的是有可能會造成索引失效問題。

3、including indexes，交換分割槽時可將索引相互交換，可用來避免索引的失效。

探究測試一：建立全域性索引

SQL> create table ou_part (a integer)
    partition by range(a)
    (
    PARTITION OU_PART_01 VALUES less than(10) tablespace TS_OU_01,
    partition ou_part_02 values less than(20) tablespace ts_ou_02,
    partition ou_part_03 values less than(30) tablespace ts_ou_03,
    partition ou_part_04 values less than(40) tablespace ts_ou_04
    );

SQL>insert into ou_part values (1);

SQL>insert into ou_part values (2);

SQL>insert into ou_part values (3);

SQL>insert into ou_part values (11);

SQL>insert into ou_part values (12);

SQL>insert into ou_part values (13);

SQL>insert into ou_part values (21);

SQL>insert into ou_part values (22);

SQL>insert into ou_part values (23);

SQL>insert into ou_part values (31);

SQL>insert into ou_part values (32);

SQL>insert into ou_part values (33);

SQL> commit;

SQL> create index index_glo on ou_part (a) Global;

SQL> create table t (a integer);

SQL> insert into t values(51);

SQL> commit;

SQL> alter table ou_part exchange partition OU_PART_01 with table t without validation;

SQL> select status from User_Indexes a where a.index_name = 'INDEX_GLO' ;
STATUS
--------
UNUSABLE

此時發現索引已失效，針對本地索引失效後並不會影響其他分割槽索引的查詢，那全域性索引失效後，會有什麼後果勒？

SQL> 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;
SQL> select * from ou_part where a=11;
                                      A
---------------------------------------
                                     11

此時Oracle沒有報ORA-01502錯誤。

此實驗知識點：

1、交換分割槽會使全域性索引失效

2、當全域性索引失效且session set skip_unusable_indexes=false時，查詢時會報ORA-01502錯誤。