实验：
（一） 创建本地索引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 alteredSQL> 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错误。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
