【案例4】总账明细表的分区方案

目录

前言

实施步骤

备份数据

验证表数据分布情况

创建重定义需要的过渡表

确认gl_detail 可以进行重定义：

创建过渡表

开始重定义

开始重定义分区表

在过渡表上创建本地索引local index

主键的创建

同步数据

完成重定义

删除过渡表

修改索引，约束名称和原表一致

对所有索引进行重命名

验证数据分布正确性

ABORT_REDEF_TABLE使用

---

前言

本文是使用oracle 11g的提供的在线重定义方式，对普通表实现分区。

本文方案的过程均在测试环境中执行，正式环境需要依据具体情况进行替换（比如创建localindex等）。

所有操作需要在测试环境下进行业务验证后，方可实施在正式环境的数据库中

注明：

1. 每年需要DBA手动维护新增一个年度分区
2. 分区后查询效率需要DBA监控，根据用户的查询习惯调整索引，关注执行计划，尽量避免跨分区查询，跨分区的查询效果比较差。

---

实施步骤

备份数据

对gl_detail表做数据备份。通常这个表比较大，建议使用数据泵。

参考命令如下：

[oracle@oraDB dpdump]$ expdp nc63/nc63 TABLES=gl_detail dumpfile=gl_detail.dmp DIRECTORY=DATA_PUMP_DIR;

也可以增加并行加快导出时间，依据具体情况而定。

验证表数据分布情况

这里使用“年度”字段作为分区键，因此可以查看下各个年度数据分区情况，供分区完毕后进行初步数据验证。

参考命令如下：

nc63@ZFRORA> select count(*) from gl_detail;

  COUNT(*)

----------

     95610

nc63@ZFRORA> select count(*) from gl_detail where yearv='2013';


  COUNT(*)

----------

     13545

nc63@ZFRORA> select count(*) from gl_detail where yearv='2012';

  COUNT(*)

----------

     82065

创建重定义需要的过渡表

确认gl_detail 可以进行重定义：

nc63@ZFRORA> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('nc63','gl_detail');

PL/SQL procedure successfully completed.

创建过渡表

create table GL_DETAIL_temp

(

  ACCOUNTCODE        VARCHAR2(40) default '~',

  ADJUSTPERIOD       VARCHAR2(3),

  ……………..  ＃＃＃＃复制原表创建语句即可，此处省略

)

partition by list(yearv) 

(

  partition P2012 values('2012'),

  partition P2013 values('2013'),

  partition P2014 values('2014') 

);

注意：这里因为yearv是字符串列，所以后期到了新的年份无法自动字段创建新的分区，需要dba手工添加！！重要！！！

开始重定义

开始重定义分区表

nc63@ZFRORA> exec DBMS_REDEFINITION.START_REDEF_TABLE('nc63','gl_detail','gl_detail_temp');

PL/SQL procedure successfully completed.

在过渡表上创建本地索引local index

在开始重定义之后在过渡表上创建local索引，重定义完成后，主键对应的索引也是分区索引;

主键的创建

nc63@ZFRORA> alter table gl_detail_temp add constraint PK_GL_DETAIL_tmp primary key (year,PK_DETAIL)using index local;

Table altered.

其他索引创建

原gl_detail中非UNIQUE索引全部改成LOCAL索引。然后再根据运行情况进行调整。

同步数据

nc63@ZFRORA> exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE (uname => 'nc63',orig_table=> 'gl_detail',int_table=> 'gl_detail_temp');

PL/SQL procedure successfully completed.

完成重定义

nc63@ZFRORA> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname => 'nc63',orig_table => 'gl_detail',int_table => 'gl_detail_temp');

PL/SQL procedure successfully completed.

删除过渡表

nc63@ZFRORA> drop table gl_detail_temp;

Table dropped.

修改索引，约束名称和原表一致

对所有索引进行重命名

alter index PK_GL_DETAIL_tmp rename to PK_GL_DETAIL;

注：需要对所有索引进行rename

验证数据分布正确性

根据上面步骤查出的结果，验证分区后数据的正确性。

ABORT_REDEF_TABLE使用

在FINISH_REDEF_TABLE之前，可以使用abort_redef_table停止重定义

exec DBMS_REDEFINITION.ABORT_REDEF_TABLE ('nc63','gl_detail','gl_detail_temp');

PL/SQL procedure successfully completed.