巧妙使用exchange partition的一个案例(r6笔记第1天)

前几天写过一篇文章讨论过分区表的在线重定义，其实就是另外一个分区表和现有的分区表做数据字典信息的交换 http://blog.itpub.net/23718752/viewspace-1734195/ 当然了除此之外还是需要一些相应的权限，在这个过程中会在内部做类似物化视图一样的数据刷新，保持数据表的可访问性。 在不同的场景中还是会有不同的取舍，比如现在的场景，情况发生了一些变化，分区存在问题，要重新分区是肯定的，除此之外，开发希望把一些旧数据做一些清理，比如根据时间来分区，可能对于开发来说，最近一段时间的数据保留就可以了，之前很旧的数据就只是需要做一个备份，直接清掉，基本评估下来，可能90%以上的数据都需要做清理。 所以这个时候还是使用在线重定义就有一些弊端了。 首先是时间的问题，在线重定义的过程中，其实内部还是在做数据的复制工作。相当于把数据从一个源流动到另外一个源。内部是这样的数据刷新，对外保持始终可以访问，所以对于上亿条记录来说这个时间就比较长了。 其次就是效率问题，因为重新分区后，可能大部分数据都不需要了，这个时候做在线重定义还会做数据复制，然后在一定的时间之后还是需要再次做数据清理。这个时候相当于做了重复工作。效率会大大折扣。 这个时候根据目前的问题情况，考虑通过以下的方式来实现。 首先是数据内部的复制，为了减少这种开销，可以考虑把分区表的默认分区和一个普通表做交换，这个过程是一个数据字典级的变更，所以速度还是很快的。 然后可以交换后的分区做split partition的操作。这个时候split操作时间会持续极短。影响相对来说很小。 然后就是数据的清理，这个时候清理工作就会变为一种选择性的数据导入，因为需要导入的数据量还是很小的。所以只需要把需要时间范围内的数据导入即可。 我们来简单试一试。 还是之前所用的分区表，现在只有一个默认分区。

CREATE  TABLE tab_part  
   (  
   col1 varchar2(30),  
   col2 DATE  
   )   
   partition BY range(col2)  
   (  
     partition tab_part_maxvalue  values less than (maxvalue)
   ); 

我们希望达到的效果是下面的分区形式。

CREATE TABLE  tab_part  
   (  
   col1 varchar2(30),  
   col2 DATE  
   )   
   partition BY range(col2)  
   (  
   partition tab_part_2014 VALUES  less than (to_date('2014-08-01','yyyy-mm-dd')),  
   partition tab_part_2015  VALUES less than (to_date('2015-08-01','yyyy-mm-dd')),  
   partition  tab_part_maxvalue values less than (maxvalue)
    );

然后给分区表创建分区索引，然后我们简单插入几条数据。 create index inx_tab_part on tab_part(col1) local; INSERT INTO tab_part VALUES(1,SYSDATE-400); INSERT INTO tab_part VALUES(11,SYSDATE-600); commit; 数据情况如下： COL1 COL2 ------------------------------ --------- 1 09-APR-15 11 05-MAR-14 这个时候我们创建一个表test_tab01来做为中间过渡的一个表。

CREATE TABLE TEST_TAB01( col1 varchar2(30), col2 DATE); 这个时候就开始使用exchange partition来把分区的数据做个交换。

ALTER TABLE tab_part EXCHANGE PARTITION tab_part_maxvalue WITH TABLE TEST_TAB01 ; 交换的速度很快，来看看操作之后的数据情况，可以看到数据都到了test_tab01里面。

SQL> select count(*)from tab_part;

COUNT(*) ---------- 0

SQL> select count(*)from test_tab01;

COUNT(*) ---------- 2 这个时候分区还是没变，但是数据给交换出来了。 SQL> select table_name,partition_name from user_tab_partitions where table_name='TAB_PART';

TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ TAB_PART TAB_PART_MAXVALUE 我们接下来要做split partition的工作。

SQL> alter table tab_part SPLIT PARTITION tab_part_maxvalue at (to_date('2014-08-01','yyyy-mm-dd')) INTO ( PARTITION tab_part_2014 , PARTITION tab_part_maxvalue);

Table altered.

SQL> alter table tab_part SPLIT PARTITION tab_part_maxvalue at (to_date('2015-08-01','yyyy-mm-dd')) INTO ( PARTITION tab_part_2015 , PARTITION tab_part_maxvalue);

Table altered. 这个时候再来看分区的情况，就达到了我们预期的要求。

SQL> select table_name,partition_name from user_tab_partitions where table_name='TAB_PART';

TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ TAB_PART TAB_PART_2014 TAB_PART TAB_PART_2015 TAB_PART TAB_PART_MAXVALUE 如果对exchange partition还是有一定的疑虑，想索引会不会受到影响，如果是本地索引是没有问题的，如果是global全局索引，就需要rebuild

SQL> select index_name,partition_name,status from user_ind_partitions where index_name='INX_TAB_PART';

INDEX_NAME PARTITION_NAME STATUS ------------------------------ ------------------------------ -------- INX_TAB_PART TAB_PART_2014 USABLE INX_TAB_PART TAB_PART_2015 USABLE INX_TAB_PART TAB_PART_MAXVALUE USABLE

这个时候重新分区完成了，要做的事情就是导入数据了，选择性的导入数据，根据时间戳即可。 当然了好几亿条记录，不建个索引根本说不过去。 create index inx_test_tab01 on test_tab01(col); 我们就可以使用insert的方式导入数据即可。

insert into tab_part select *from test_tab01 where col>sysdate-10;

最后为了把exchange partiton的一个精髓突出出来和在线重定义区分开了，可以做一个很简单的小例子。 我们还是创建原来的表tab_part

CREATE TABLE tab_part ( col1 varchar2(30), col2 DATE ) partition BY range(col2) ( partition tab_part_maxvalue values less than (maxvalue) );

create index inx_tab_part on tab_part(col1) local; 我们接着创建一个表test_tab01，这个时候唯一的不同之处就是字段名不同，但是数据类型相同。

CREATE TABLE TEST_TAB01( a varchar2(30), b DATE); 然后尝试去做exchange partition的时候是没有问题的。

ALTER TABLE tab_part EXCHANGE PARTITION tab_part_maxvalue WITH TABLE TEST_TAB01 ; 我们看看交换分区之后的表结构变化情况

SQL> desc tab_part Name Null? Type ----------------------------------------- -------- ---------------------------- COL1 VARCHAR2(30) COL2 DATE

SQL> desc test_tab01 Name Null? Type ----------------------------------------- -------- ---------------------------- A VARCHAR2(30) B DATE

可以看到字段名没有任何变化，还是原来的字段名，但是在内部做了数据字典信息的交换，把相应的段信息做了交换而已。这也就exchange partition的主要思想。 通过这个案例可以看到，exchange partition还是大有可为，而且在很多场景下可以达到很满意的效果，在线重定义也不错，不过在选择的时候还是需要综合评定，没有最好的方法，最有最适合的方法。