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错误。

  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值