exchange效率oracle,巧妙使用exchange partition的一个案例

前几天写过一篇文章讨论过分区表的在线重定义，其实就是另外一个分区表和现有的分区表做数据字典信息的交换

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还是大有可为，而且在很多场景下可以达到很满意的效果，在线重定义也不错，不过在选择的时候还是需要综合评定，没有最好的方法，最有最适合的方法。