全面学习分区表及分区索引(10)--交换分区
直白的说就是迁移数据。迁移数据的方式很多,为什么要使用exchange partition的方式呢,表急,听三思慢慢道来。
Exchange partition提供了一种方式,让你在表与表或分区与分区之间迁移数据,注意不是将表转换成分区或非分区的形式,而仅只是迁移表中数据(互相迁移),由于其号称是采用了更改数据字典的方式,因此效率最高(几乎不涉及io操作)。Exchange partition适用于所有分区格式,你可以将数据从分区表迁移到非分区表,也可以从非分区表迁移至分区表,或者从hash partition到range partition诸如此类吧。
其语法很简单:alter table tbname1 exchange partition/subpartition ptname with table tbname2;
Exchange partition迁移的方式也很有意思,言语表达怕大家听不明白,下面直接通过示例来表达:
借用前文中创建的空分区表:t_partition_range,并插入几条记录
SQL> create table t_part_range(id number ,name varchar2(10))
2 partition by range(id)(
3 partition t_range_p1 values less than(10) tablespace part01,
4 partition t_range_p2 values less than(20) tablespace part02,
5 partition t_range_p3 values less than(maxvalue)tablespace part03)
6 ;
Table created
SQL> desc t_part_range;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
ID NUMBER Y
NAME VARCHAR2(10) Y
SQL> insert into t_part_range values (11,'aa');
1 row inserted
SQL> insert into t_part_range values (12,'bb');
1 row inserted
SQL> insert into t_part_range values (13,'cc');
1 row inserted
SQL> commit;
Commit complete
再创建一个非分区表,结构与t_part_range相同 t_part_range_tmp
SQL> create table t_part_range_tmp (id number,name varchar2(10));
Table created
SQL> desc t_part_range_tmp;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
ID NUMBER Y
NAME VARCHAR2(10) Y
执行交换分区(我们知道刚插入到range分区表的数据都在分区t_range_p2中,因此这里指定交换该分区)
SQL> alter table t_part_range exchange partition t_range_p2
2 with table t_part_range_tmp;
Table altered
下面就是见证奇迹的时刻...................................
SQL> select * from t_part_range_tmp;
ID NAME
---------- ----------
11 aa
12 bb
13 cc
BUT...........................
SQL> select * from t_part_range;
ID NAME
---------- ----------
SQL> select * from t_part_range partition(t_range_p2);
ID NAME
---------- ----------
记录成功交换到未分区的表中。
我们再执行一次exchange partition的命令,看看又会发生什么呢
SQL> alter table t_part_range exchange partition t_range_p2
2 with table t_part_range_tmp;
Table altered
SQL> select * from t_part_range_tmp;
ID NAME
---------- ----------
SQL> select * from t_part_range partition(t_range_p2);
ID NAME
---------- ----------
11 aa
12 bb
13 cc
再做个更加明确的测试,我们往未分区的表中加入一些记录后再执行exchange partition,看看会发生什么呢:
SQL> insert into t_part_range_tmp values (14,'test again');
1 row inserted
SQL> insert into t_part_range_tmp values (15,'test again');
1 row inserted
SQL> insert into t_part_range_tmp values (16,'test again');
1 row inserted
SQL> commit;
Commit complete
SQL> alter table t_part_range exchange partition t_range_p2
2 with table t_part_range_tmp;
Table altered
下面又是见证奇迹的时刻.............................
SQL> select * from t_part_range partition(t_range_p2);
ID NAME
---------- ----------
14 test again
15 test again
16 test again
SQL> select * from t_part_range_tmp;
ID NAME
---------- ----------
11 aa
12 bb
13 cc
看看,是不是换过来了。
这就是前面所说的,互相交换的意思~~
此时,疑问:如果表中有未提交的数据怎么办。
SQL> insert into t_part_range_tmp values (18,'not commit');
1 row inserted
SQL> alter table t_part_range exchange partition t_range_p2
2 with table t_part_range_tmp;
Table altered
SQL> select * from t_part_range partition(t_range_p2);
ID NAME
---------- ----------
11 aa
12 bb
13 cc
18 not commit
SQL> select * from t_part_range_tmp;
ID NAME
---------- ----------
14 test again
15 test again
16 test again
看来未提交的数据也exchange 过去了。此时,ROLLBACK。。。
SQL> rollback;
Rollback complete
BUT.........................
SQL> select * from t_part_range partition(t_range_p2);
ID NAME
---------- ----------
11 aa
12 bb
13 cc
18 not commit //该数据居然还在。呵呵,难道他会在Exchange 之前 提交所有未提交的数据?哪位大虾来指点一下。
这就是前面所说的,互相交换的意思~~
注意:
> 涉及交换的两表之间表结构必须一致,除非附加with validation子句;
> 如果是从非分区表向分区表做交换,非分区表中的数据必须符合分区表中指定分区的规则,除非附加without validation子句;
> 如果从分区表向分区表做交换,被交换的分区的数据必须符合分区规则,除非附加without validation子句;
> Global索引或涉及到数据改动了的global索引分区会被置为unusable,除非附加update indexes子句。
提示:
一旦附加了without validation子句,则表示不再验证数据有效性,因此指定该子句时务必慎重
SQL> delete from t_part_range;
4 rows deleted
SQL> delete from t_part_range_tmp;
3 rows deleted
SQL> commit;
Commit complete
SQL> insert into t_part_range_tmp values (8,'no vaild..');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from t_part_range_tmp;
ID NAME
---------- ----------
8 no vaild..
//平时转换时,进行验证..
SQL> alter table t_part_range exchange partition t_range_p2
2 with table t_part_range_tmp;
alter table t_part_range exchange partition t_range_p2
with table t_part_range_tmp
ORA-14099: 未对指定分区限定表中的所有行
//指定no validation
SQL> alter table t_part_range exchange partition t_range_p2
2 with table t_part_range_tmp without validation ;
Table altered
SQL> select * from t_part_range partition(t_range_p2);
ID NAME
---------- ----------
8 no vaild.. //分区2 居然插入成功。 - -! 有天理,没人性! SO..一定要注意!
虽然新插入的记录并不符合t_range_p2分区的范围值,但指定了without validation后,数据仍然转换成功。
直白的说就是迁移数据。迁移数据的方式很多,为什么要使用exchange partition的方式呢,表急,听三思慢慢道来。
Exchange partition提供了一种方式,让你在表与表或分区与分区之间迁移数据,注意不是将表转换成分区或非分区的形式,而仅只是迁移表中数据(互相迁移),由于其号称是采用了更改数据字典的方式,因此效率最高(几乎不涉及io操作)。Exchange partition适用于所有分区格式,你可以将数据从分区表迁移到非分区表,也可以从非分区表迁移至分区表,或者从hash partition到range partition诸如此类吧。
其语法很简单:alter table tbname1 exchange partition/subpartition ptname with table tbname2;
Exchange partition迁移的方式也很有意思,言语表达怕大家听不明白,下面直接通过示例来表达:
借用前文中创建的空分区表:t_partition_range,并插入几条记录
SQL> create table t_part_range(id number ,name varchar2(10))
2 partition by range(id)(
3 partition t_range_p1 values less than(10) tablespace part01,
4 partition t_range_p2 values less than(20) tablespace part02,
5 partition t_range_p3 values less than(maxvalue)tablespace part03)
6 ;
Table created
SQL> desc t_part_range;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
ID NUMBER Y
NAME VARCHAR2(10) Y
SQL> insert into t_part_range values (11,'aa');
1 row inserted
SQL> insert into t_part_range values (12,'bb');
1 row inserted
SQL> insert into t_part_range values (13,'cc');
1 row inserted
SQL> commit;
Commit complete
再创建一个非分区表,结构与t_part_range相同 t_part_range_tmp
SQL> create table t_part_range_tmp (id number,name varchar2(10));
Table created
SQL> desc t_part_range_tmp;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
ID NUMBER Y
NAME VARCHAR2(10) Y
执行交换分区(我们知道刚插入到range分区表的数据都在分区t_range_p2中,因此这里指定交换该分区)
SQL> alter table t_part_range exchange partition t_range_p2
2 with table t_part_range_tmp;
Table altered
下面就是见证奇迹的时刻...................................
SQL> select * from t_part_range_tmp;
ID NAME
---------- ----------
11 aa
12 bb
13 cc
BUT...........................
SQL> select * from t_part_range;
ID NAME
---------- ----------
SQL> select * from t_part_range partition(t_range_p2);
ID NAME
---------- ----------
记录成功交换到未分区的表中。
我们再执行一次exchange partition的命令,看看又会发生什么呢
SQL> alter table t_part_range exchange partition t_range_p2
2 with table t_part_range_tmp;
Table altered
SQL> select * from t_part_range_tmp;
ID NAME
---------- ----------
SQL> select * from t_part_range partition(t_range_p2);
ID NAME
---------- ----------
11 aa
12 bb
13 cc
再做个更加明确的测试,我们往未分区的表中加入一些记录后再执行exchange partition,看看会发生什么呢:
SQL> insert into t_part_range_tmp values (14,'test again');
1 row inserted
SQL> insert into t_part_range_tmp values (15,'test again');
1 row inserted
SQL> insert into t_part_range_tmp values (16,'test again');
1 row inserted
SQL> commit;
Commit complete
SQL> alter table t_part_range exchange partition t_range_p2
2 with table t_part_range_tmp;
Table altered
下面又是见证奇迹的时刻.............................
SQL> select * from t_part_range partition(t_range_p2);
ID NAME
---------- ----------
14 test again
15 test again
16 test again
SQL> select * from t_part_range_tmp;
ID NAME
---------- ----------
11 aa
12 bb
13 cc
看看,是不是换过来了。
这就是前面所说的,互相交换的意思~~
此时,疑问:如果表中有未提交的数据怎么办。
SQL> insert into t_part_range_tmp values (18,'not commit');
1 row inserted
SQL> alter table t_part_range exchange partition t_range_p2
2 with table t_part_range_tmp;
Table altered
SQL> select * from t_part_range partition(t_range_p2);
ID NAME
---------- ----------
11 aa
12 bb
13 cc
18 not commit
SQL> select * from t_part_range_tmp;
ID NAME
---------- ----------
14 test again
15 test again
16 test again
看来未提交的数据也exchange 过去了。此时,ROLLBACK。。。
SQL> rollback;
Rollback complete
BUT.........................
SQL> select * from t_part_range partition(t_range_p2);
ID NAME
---------- ----------
11 aa
12 bb
13 cc
18 not commit //该数据居然还在。呵呵,难道他会在Exchange 之前 提交所有未提交的数据?哪位大虾来指点一下。
这就是前面所说的,互相交换的意思~~
注意:
> 涉及交换的两表之间表结构必须一致,除非附加with validation子句;
> 如果是从非分区表向分区表做交换,非分区表中的数据必须符合分区表中指定分区的规则,除非附加without validation子句;
> 如果从分区表向分区表做交换,被交换的分区的数据必须符合分区规则,除非附加without validation子句;
> Global索引或涉及到数据改动了的global索引分区会被置为unusable,除非附加update indexes子句。
提示:
一旦附加了without validation子句,则表示不再验证数据有效性,因此指定该子句时务必慎重
SQL> delete from t_part_range;
4 rows deleted
SQL> delete from t_part_range_tmp;
3 rows deleted
SQL> commit;
Commit complete
SQL> insert into t_part_range_tmp values (8,'no vaild..');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from t_part_range_tmp;
ID NAME
---------- ----------
8 no vaild..
//平时转换时,进行验证..
SQL> alter table t_part_range exchange partition t_range_p2
2 with table t_part_range_tmp;
alter table t_part_range exchange partition t_range_p2
with table t_part_range_tmp
ORA-14099: 未对指定分区限定表中的所有行
//指定no validation
SQL> alter table t_part_range exchange partition t_range_p2
2 with table t_part_range_tmp without validation ;
Table altered
SQL> select * from t_part_range partition(t_range_p2);
ID NAME
---------- ----------
8 no vaild.. //分区2 居然插入成功。 - -! 有天理,没人性! SO..一定要注意!
虽然新插入的记录并不符合t_range_p2分区的范围值,但指定了without validation后,数据仍然转换成功。