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