oracle交换索引,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错误。