关于分区表拆分几种方案的讨论

【背景】某生产数据库有一张以日期为分区键的范围分区表，每日一个分区，具体表结构经变形后如下：
create table FHIN_HIS
(ZONENO varchar2(5),
BRNO     varchar2(5),
......
WORKDATE varchar2(10),
 ......
)
partition by range (WORKDATE)
(
PARTITION  FHIN_HIS_20160101 values less than ('2016-01-02'),
PARTITION  FHIN_HIS_20160102 values less than ('2016-01-03'),
......
PARTITION  FHIN_HIS_20161231 values less than ('2016-12-32'),
PARTITION  FHIN_HIS_MAX         values less than(maxvalue)
)
按照要求，每年年底都需要对该表添加次年的新分区，以便将新业务数据存储到新的对应分区。由于维护人员疏忽，2016年底没有执行增加分区的相关语句，导致2017年业务数据都加载到了 FHIN_HIS_MAX分区内。据统计每日业务数据量达到10W条以至于FHIN_HIS_MAX分区内达到了1000W条记录，导致性能出现问题。为提高性能，负责人计划新增2017年分区，将其恢复为正常状态（每日一分区）。

以上为背景，具体应该怎么做呢。方案可以大体分为以下几种：
1.exp/expdp导出FHIN_HIS表所有数据，按照规范重新创建表，然后imp/impdp导入
2.CTAS创建新表，然后drop表FHIN_HIS，move表名为FHIN_HIS
3.按照规范创建新表，insert into select * FROM FHIN_HIS，然后drop表FHIN_HIS，move新表名为FHIN_HIS
4.split partition拆分表分区
5.在线重定义

以下为各种方案的利弊：
第1种导入导出方案通俗易懂，操作简单，但适用于数据量较小的分区表，对于大数据量的表效率较低。
第2种CTAS相对第一种方案，效率略高，但相对第一种方案需要表空间有额外的存储空间。
第3种方案和第2种方案一样需要额外的存储空间外，也会产生大量的UNDO和归档。
第4种方案相对前3种方案效率最高，不需要额外空间，也不会产生大量的UNDO和归档。
第5中方案：纵观前4种方案，最大的问题在于会影响在线交易。若对在线数据交易要求较高，建议采用第5种方案：在线重定义。

本着操作方便、同时也能分析内部原理的原则，在测试环境中对操作表进行了模型简化，具体过程如下：
环境准备：

点击(此处)折叠或打开

1. CREATE TABLE t_partition(
   
2. id number,
   
3. data varchar2(100)
   
4. )
5. partition by range(id)
   
6. (
   
7.    partition p1 values less than(10000),
   
8.    partition p2 values less than (maxvalue)
   
9. );
   
10. 
    
11. 
    
12. begin
    
13. for i in 1..30000000 loop
    
14.     insert into t_partition values(trunc(dbms_random.value(1,100000)),dbms_random.string('a',5));
    
15.     if i mod 10000 = 0 then
    
16.         commit;
    
17.     end if;
    
18. end loop;
    
19. commit;
20. end;
    
21. /
22. 
    
23. 
    
24. create index ind_data on t_partition(data) local;
25. 
    
26. exec dbms_stats.gather_table_stats(USER,'T_PARTITION',CASCADE=>TRUE);

上述操作完成后，t_partition表含有两个分区，共计30000000条记录，其中id<10000的记录存放在p1,其余放在了p2分区。

点击(此处)折叠或打开

1. SELECT t.partition_name,t.num_rows FROM user_tab_partitions t WHERE t.table_name='T_PARTITION';
2. PARTITION_NAME  NUM_ROWS
   
3. --------------  ---------
   
4. P1              2998784
   
5. P2              27001216

为模拟上述生产环境情况将分区定为以10000为间隔存放，调整后的具体情况为：
partition p1  values  less than ( 10000 ) ,
partition p2  values  less than (2 0000 ) ,
partition p3  values  less than (3 0000 ) ,
partition p4  values  less than (4 0000 ) ,
partition p5  values  less than (5 0000 ) ,
partition p6  values  less than (6 0000 ) ,
partition p7  values  less than (7 0000 ) ,
partition p8  values  less than (8 0000 ) ,
partition p9  values  less than (9 0000 ) ,
partition p10  values  less than ( 100000 ) ,
partition pmax  values  less than ( maxvalue )


1.exp/expdp导出， imp/impdp导入方案
expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t_partition_p2.dump logfile=t_partition_p2.log tables=t_partition:p2

点击(此处)折叠或打开

1. alter table t_partition drop partition p2;
2. alter table t_partition add partition p2 values less than(20000);
3. alter table t_partition add partition p3 values less than(30000);
   
4. alter table t_partition add partition p4 values less than(40000);
   
5. alter table t_partition add partition p5 values less than(50000);
   
6. alter table t_partition add partition p6 values less than(60000);
   
7. alter table t_partition add partition p7 values less than(70000);
   
8. alter table t_partition add partition p8 values less than(80000);
   
9. alter table t_partition add partition p9 values less than(90000);
   
10. alter table t_partition add partition p10 values less than(100000);
    
11. alter table t_partition add partition pmax values less than(maxvalue);

impdp scott/tiger dumpfile=t_partition_p2.dump logfile=impdp.log directory=DATA_PUMP_DIR TABLE_EXISTS_ACTION=append


导出->drop partition,add partition->导入全部过程用了6分钟左右，且中间产生了大量的归档(本例中产生了2.5G的归档)。另外值得注意的是，在drop partition，add partition过程中插入数据若分区不存在会报错

导出导入完成后，分区索引状态正常，无需再次重建。

点击(此处)折叠或打开

1. SELECT distinct(STATUS) FROM user_ind_partitions WHERE index_name='IND_DATA';
2. STATUS
   
3. --------
   
4. USABLE

2. CTAS方案
执行操作前查看redo size数值：


执行CTAS

点击(此处)折叠或打开

1. create table t_partition1
   
2. partition by range(id)
   
3. (
   
4. partition p1 values less than(10000),
   
5. partition p2 values less than(20000),
   
6. partition p3 values less than(30000),
   
7. partition p4 values less than(40000),
   
8. partition p5 values less than(50000),
   
9. partition p6 values less than(60000),
   
10. partition p7 values less than(70000),
    
11. partition p8 values less than(80000),
    
12. partition p9 values less than(90000),
    
13. partition p10 values less than(100000),
    
14. partition pmax values less than(maxvalue)
    
15. ) as select * from t_partition;
    
16. Elapsed: 00:00:54.98
17. 
    
18. drop table t_partition purge;
    
19. Elapsed: 00:00:00.97
20. 
    
21. alter table t_partition1 rename to t_partition;
22. Elapsed: 00:00:00.34
23. 
    
24. create index ind_data on t_partition(data) local;
25. Elapsed: 00:01:33.38
    

执行完成查看redo size数值：


经统计所有步骤执行用时共计2分30秒， 生成归档1G左右，无论从 效率还是归档生成量方面较方案1都提高了不少。

点击(此处)折叠或打开

1. SQL> select (10523081752-9390775732)/1024/1024 from dual;
   
2. (10523081752-9390775732)/1024/1024
   
3. ----------------------------------
   
4.            1079.851169586181640625

同方案1一样，此方案也可能会造成业务数据的丢失 或者影响业务的正常运行。

3. insert into select * FROM方案

点击(此处)折叠或打开

1. create table t_partition1
   
2. (id number,data varchar2(100))
   
3. partition by range(id)
   
4. (
   
5. partition p1 values less than(10000),
   
6. partition p2 values less than(20000),
   
7. partition p3 values less than(30000),
   
8. partition p4 values less than(40000),
   
9. partition p5 values less than(50000),
   
10. partition p6 values less than(60000),
    
11. partition p7 values less than(70000),
    
12. partition p8 values less than(80000),
    
13. partition p9 values less than(90000),
    
14. partition p10 values less than(100000),
    
15. partition pmax values less than(maxvalue)
    
16. );
17. Elapsed: 00:00:00.05
    
18. 
    
19. insert into t_partition1 select * from t_partition;
20. Elapsed: 00:08:15.81
21. commit;
23. drop table t_partition purge;
24. Elapsed: 00:00:00.16
26. alter table t_partition1 rename to t_partition;
27. Elapsed: 00:00:00.05
28. 
    
29. create index ind_data1 on t_partition(data) local;
30. Elapsed: 00:01:28.28
    

所有步骤执行用时共计10分钟， 生成归档6G左右，无论从 效率还是归档生成量方面 相对较差。
同方案1，2一样，该方案也可能会造成业务数据的丢失或者影响业务的正常运行。

4.split partition拆分表分区
      该方案是推荐的方案，执行效率较高，可以节省大量的变更时间。

点击(此处)折叠或打开

1. ALTER TABLE t_partition SPLIT PARTITION p2 AT(20000) INTO (PARTITION P2,PARTITION P3);
2. Elapsed: 00:00:51.21
3. ALTER TABLE t_partition SPLIT PARTITION p3 AT(30000) INTO (PARTITION P3,PARTITION P4);
4. Elapsed: 00:00:53.34
   
5. ALTER TABLE t_partition SPLIT PARTITION p4 AT(40000) INTO (PARTITION P4,PARTITION P5);
6. Elapsed: 00:00:35.66
   
7. ALTER TABLE t_partition SPLIT PARTITION p5 AT(50000) INTO (PARTITION P5,PARTITION P6);
8. Elapsed: 00:00:24.56
   
9. ALTER TABLE t_partition SPLIT PARTITION p6 AT(60000) INTO (PARTITION P6,PARTITION P7);
10. Elapsed: 00:00:18.44
    
11. ALTER TABLE t_partition SPLIT PARTITION p7 AT(70000) INTO (PARTITION P7,PARTITION P8);
12. Elapsed: 00:00:14.67
    
13. ALTER TABLE t_partition SPLIT PARTITION p8 AT(80000) INTO (PARTITION P8,PARTITION P9);
14. Elapsed: 00:00:14.24
    
15. ALTER TABLE t_partition SPLIT PARTITION p9 AT(90000) INTO (PARTITION P9,PARTITION P10);
16. Elapsed: 00:00:06.91
17. ALTER TABLE t_partition SPLIT PARTITION p10 AT(100000) INTO (PARTITION P10,PARTITION Pmax);
18. Elapsed: 00:00:00.48

完成后重建 索引。

点击(此处)折叠或打开

1. alter index ind_data rebuild partition p2;
   
2. Elapsed: 00:00:09.47
   
3. alter index ind_data rebuild partition p3;
   
4. Elapsed: 00:00:06.55
   
5. alter index ind_data rebuild partition p4;
   
6. Elapsed: 00:00:06.49
   
7. alter index ind_data rebuild partition p5;
   
8. Elapsed: 00:00:07.27
   
9. alter index ind_data rebuild partition p6;
   
10. Elapsed: 00:00:05.35
    
11. alter index ind_data rebuild partition p7;
    
12. Elapsed: 00:00:07.08
    
13. alter index ind_data rebuild partition p8;
    
14. Elapsed: 00:00:08.89
    
15. alter index ind_data rebuild partition p9;
    
16. Elapsed: 00:00:06.64
    
17. alter index ind_data rebuild partition p10;
    
18. Elapsed: 00:00:08.11

共计用时5分钟左右，生成归档2.5G左右。

5.在线重定义
      在一个高可用系统中，上述几种方案 显然并适用，因为它们都会不同程度的影响业务。幸运的是，Oracle从9i版本开始提供了在线重定义表功能，通过调用DBMS_REDEFINITION包，可以在线修改表的结 构且将 对DML操作的影响降到最小。这里要特别说明一下，在线重定义online table redefinition并不是一点不影响DML操作，只是将影响程度降到了最小。从官方也能找到一点线索：
 
结合官方文档，总结在线重定义的步骤如下：
     1.首先选择重定义方法
     重定义有两种方法：
     a）基于主键，此方式为在线重定义的默认方式
     b) 基于rowid，rowid的方式不能用于索引组织表(IOT),而且重定义完成后 存在隐藏列 M_ROW$$
    
     2.调用重定义过程DBMS_REDEFINITION.CAN_REDEF_TABLE判断表是否满足重定义条件，若该表不满足在线重定义的条件，该过程会抛出异常告诉你原因。

     3.按照要求创建中间表，例如本实验中需要按照要求创建好各个分区。该步骤无需手工在中间表上创建索引、约束以及触发器。
    

     4.(可选)如果重定义一个非常大的表，为了提高性能，请按照如下步骤开启并行
     alter session force parallel dml parallel degree-of-parallelism;
     alter session force parallel query parallel degree-of-parallelism;

     5.调用重定义过程DBMS_REDEFINITION.START_REDEF_TABLE进行重定义。这个步骤牵扯到拷贝数据，因此这个步骤相对时间较长。但其执行过程不影响重定义表的DML操作。
     
     6.拷贝触发器、索引和约束等至中间表并进行相应的授权。可以根据实际情况选择自动、手动两种方案中的一种。
     
    7.(可选)若在执行DBMS_REDEFINITION.START_REDEF_TABLE过程中，重定义表发生了大量的DML操作，可根据实际情况执行一次或多次DBMS_REDEFINITION.SYNC_INTERIM_TABLE过程，以便缩短DBMS_REDEFINITION.FINISH_REDEF_TABLE过程的执行时间。
     
    8.执行DBMS_REDIFINITION.FINISH_REDEF_TABLE，该过程会以独占模式锁定重定义表一段时间，具体锁定时间与表的大小以及重定义的复杂度无关。
    
     
    9.对于采用ROWID方式进行重定义的表，包含了隐藏列M_ROW$$,官方建议对该列进行删除。
    
     alter table table_name set unused(M_ROW$$);
     alter table table_name drop unused columns;
     
    10.为节省空间，建议删除中间表

若你第一次进行在线重定义，看着前面的10个步骤肯定以为这个超级复杂。其实多做几次也就熟练了，下面就结合以上面的例子在测试环境操作一次。
a).确认表是否满足重定义条件

点击(此处)折叠或打开

1. BEGIN
2.     DBMS_REDEFINITION.CAN_REDEF_TABLE(USER,'T_PARTITION',DBMS_REDEFINITION.CONS_USE_ROWID);
3. END;
   
4. /
5. PL/SQL procedure successfully completed.
6. Elapsed: 00:00:00.77
   

过程返回没有报错证明满足条件。
b). 创建中间表

点击(此处)折叠或打开

1. create table t_partition_temp
   
2. (id number,data varchar2(100))
   
3. partition by range(id)
   
4. (
   
5. partition p1 values less than(10000),
   
6. partition p2 values less than(20000),
   
7. partition p3 values less than(30000),
   
8. partition p4 values less than(40000),
   
9. partition p5 values less than(50000),
   
10. partition p6 values less than(60000),
    
11. partition p7 values less than(70000),
    
12. partition p8 values less than(80000),
    
13. partition p9 values less than(90000),
    
14. partition p10 values less than(100000),
    
15. partition pmax values less than(maxvalue)
    
16. );
17. Elapsed: 00:00:00.03
    

c). 执行 DBMS_REDEFINITION.START_REDEF_TABLE

• 为验证其不影响DML操作，在执行前记录t_partition数据量

点击(此处)折叠或打开

1. select count(*) FROM t_partition;
   
2.   COUNT(*)
   
3. ----------
   
4.   30000000

•  在向START_REDEF_TABLE执行过程中以及执行完成后，分别向t_partition插入1000条记录
  

  点击(此处)折叠或打开

  1. EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T_PARTITION', 'T_PARTITION_TEMP',OPTIONS_FLAG=>DBMS_REDEFINITION.CONS_USE_ROWID);
  2. Elapsed: 00:05:30.78
  
  

点击(此处)折叠或打开

1. begin
   
2. for i in 1..1000 loop
   
3.     insert into t values(dbms_random.value(1,100000),dbms_random.string('a',5));
4. end loop;
   
5. commit;
6. end;
   
7. /

该步骤最耗时，请耐心等待。

d).自动 拷贝触发器、索引和约束等至中间表并进行相应的授权

点击(此处)折叠或打开

1. DECLARE
   
2. num_errors PLS_INTEGER;
   
3. BEGIN
   
4. DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(USER, 'T_PARTITION','T_PARTITION_TEMP',DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, FALSE, num_errors);
   
5. END;
   
6. /
7. PL/SQL procedure successfully completed.
   

本地索引copy报错：

完成后，查看 DBA_REDEFINITION_ERRORS视图查看是否有错误

点击(此处)折叠或打开

1. SELECT count(*) FROM DBA_REDEFINITION_ERRORS;
   
2.   COUNT(*)
   
3. ----------
   
4.          1

f) 执行DBMS_REDIFINITION.FINISH_REDEF_TABLE

点击(此处)折叠或打开

1. SQL> BEGIN
   
2. DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'T_PARTITION', 'T_PARTITION_TEMP');
   
3. END;
   
4. / 
5. PL/SQL procedure successfully completed.
6. Elapsed: 00:00:03.27

重建索引：

点击(此处)折叠或打开

1. SQL> drop index ind_data;
2. Index dropped.
3. Elapsed: 00:00:00.53
   
4. 
   
5. SQL> create index ind_data on t_partition(data) local;
6. Index created.
7. Elapsed: 00:02:00.47
   
8. 
   
9. SQL> exec dbms_stats.gather_table_stats(USER,'T_PARTITION',CASCADE=>TRUE);
10. PL/SQL procedure successfully completed.
11. Elapsed: 00:00:33.50

查看表的信息

点击(此处)折叠或打开

1. SELECT count(*) FROM t_partition;
   
2. 
   
3.   COUNT(*)
   
4. ----------
   
5.   30000000

在线重定义整个过程共计用时8分钟左右，生成归档2G左右。
该方案最大程度上降低了对在线重定义表的影响，只有在FINISH阶段以排他方式锁表。由于本例中索引为本地索引，无法自动完成索引的拷贝，在重建索引阶段也会锁表。另外可以看到在线重定义方案也有可能造成数据丢失的情况。

【总结】
本博文提供了5种调整分区表的方案，各有利弊，请根据实际情况选择。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29827284/viewspace-2135516/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29827284/viewspace-2135516/