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