在线重定义方式将Heap表转换为分区表

简介

     近期由于性能优化、数据清洗的客户需求，要将部分客户的单表（heap table）转换成分区表。按时间分区后，不单可以提高查询的效率，而且还可以对分区表进行按时的数据清理，大大提高了效率减少了人工运维和维护的成本。考虑到在线实施时对生产业务的影响，所以打算采用基于物化视图的日志原理的在线重定义方式实现。下面是个人测试的步奏拿出来与大家分享。

1、搭建测试环境

--创建源表T
SQL> create table t(x date,y number,constraint PK_T primary key(y));
Table created.

--加载数据
insert into t values(to_date('20100701','yyyymmdd'),0);--2010,2011,2012,2013,2014
insert into t values(to_date('20100710','yyyymmdd'),1);
insert into t values(to_date('20100711','yyyymmdd'),2);
insert into t values(to_date('20100801','yyyymmdd'),3);
insert into t values(to_date('20100821','yyyymmdd'),4);
insert into t values(to_date('20100816','yyyymmdd'),5);
insert into t values(to_date('20100901','yyyymmdd'),6);
insert into t values(to_date('20101001','yyyymmdd'),7);
insert into t values(to_date('20110701','yyyymmdd'),8);
insert into t values(to_date('20110801','yyyymmdd'),9);
insert into t values(to_date('20100911','yyyymmdd'),10);
insert into t values(to_date('20110101','yyyymmdd'),11);
insert into t values(to_date('20110201','yyyymmdd'),12);
insert into t values(to_date('20110301','yyyymmdd'),13);
insert into t values(to_date('20110401','yyyymmdd'),14);
insert into t values(to_date('20120701','yyyymmdd'),15);
insert into t values(to_date('20130701','yyyymmdd'),16);
insert into t values(to_date('20140701','yyyymmdd'),17);
commit;

SQL> create unique index uk_t on t(x,y);  --测试建立 非主键索引
Index created.

--创建中间过渡表R_T
SQL>CREATE TABLE r_t
    (x date,y number)
   PARTITION BY RANGE(x)
  (
  PARTITION P2010 VALUES LESS THAN (TO_DATE('20110101','yyyymmdd')) ,
  PARTITION P2011 VALUES LESS THAN (TO_DATE('20120101','yyyymmdd')) ,
  PARTITION P2012 VALUES LESS THAN (TO_DATE('20130101','yyyymmdd')) ,
  PARTITION P2013 VALUES LESS THAN (TO_DATE('20140101','yyyymmdd')) ,
  PARTITION PMAX VALUES LESS THAN (MAXVALUE)
  --PARTITION P2014 VALUES LESS THAN (TO_DATE('20150101','yyyymmdd'))
  );

Table created.

SQL> alter table r_t add constraint pk_r_t primary key(y);                 --为过度表舔加主键
Table altered.

SQL> create unique index uk_r_t on r_t(x,y) local;                            --为过渡表添加本地唯一键
Index created.

SQL> set linesize 100
SQL> select index_name,index_type,tablespace_name from user_indexes where table_name in ('T','R_T');

INDEX_NAME                     INDEX_TYPE                  TABLESPACE_NAME
------------------------------ --------------------------- ------------------------------
PK_R_T                         NORMAL                      TEST001
UK_R_T                         NORMAL
PK_T                             NORMAL                      TEST001
UK_T                             NORMAL                      TEST001

SQL> col column_name for a15
SQL> select index_name,table_name,column_name from user_ind_columns where table_name in ('T','R_T');
INDEX_NAME                     TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ---------------
PK_R_T                         R_T                            Y
UK_R_T                         R_T                            X
UK_R_T                         R_T                            Y
PK_T                             T                              Y
UK_T                             T                              X
UK_T                             T                              Y

2、进行在线重定义

--验证T是否可以在线重定义
begin
  dbms_redefinition.can_redef_table('SCOTT', 'T');
end;
/
 
--开始在线重定义 
begin
  dbms_redefinition.start_redef_table('SCOTT', 'T', 'R_T');
end;
/

-- 手工注册创建的本地索引
begin
  DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT('SCOTT',
                                                'T',
                                                'R_T',
                                                DBMS_REDEFINITION.CONS_INDEX,
                                                'SCOTT',
                                                'UK_T',
                                                'UK_R_T');
end;
/

-- 手工注册创建的主键索引
begin
  DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT('SCOTT',
                                                'T',
                                                'R_T',
                                                DBMS_REDEFINITION.CONS_INDEX,
                                                'SCOTT',
                                                'PK_T',
                                                'PK_R_T');
end;
/

-- 自动注册其他的
DECLARE
  num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCOTT',
                                          'T',
                                          'R_T',
                                          dbms_redefinition.cons_orig_params,
                                          TRUE,
                                          TRUE,
                                          TRUE,
                                          TRUE,
                                          num_errors);
END;
/

--同步数据
begin
  dbms_redefinition.sync_interim_table('SCOTT', 'T', 'R_T');
end;
/

--完成重定义
begin
   dbms_redefinition.finish_redef_table('SCOTT','T','R_T');
end;
/

SQL> drop table r_t purge;       --删除过渡表;


3、验证结果

SQL> select count(*) from t c where c.y=1;

  COUNT(*)
----------
         1

SQL> select count(*) from t partition (p2010);
 
COUNT(*)
----------
         9
SQL> select count(*) from t partition (pmax);

  COUNT(*)
----------
         1

--检查索引
SQL> select index_name,index_type,tablespace_name from user_indexes where table_name in ('T','R_T');

INDEX_NAME                     INDEX_TYPE                  TABLESPACE_NAME
------------------------------ --------------------------- ------------------------------
PK_T                           NORMAL                      TEST001
UK_T                           NORMAL

 SQL> select index_name,table_name,column_name from user_ind_columns where table_name in ('T','R_T');

INDEX_NAME                     TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ---------------
PK_T                           T                              Y
UK_T                           T                              X
UK_T                           T                              Y

SQL> col owner for a15
SQL> select b.owner, b.constraint_name, b.constraint_type, b.table_name
  from user_constraints b
 where b.table_name in ('R_T', 'T');

OWNER           CONSTRAINT_NAME                C TABLE_NAME
--------------- ------------------------------ - ------------------------------
SCOTT           PK_R_T                         P T                                            --这里发现，有趣的是constraint_name是不会随在线重定义转换的


--为转换完的表rename约束名

SQL> alter table t rename constraint pk_r_t to pk_t;
Table altered.

SQL> select b.owner, b.constraint_name, b.constraint_type, b.table_name
  from user_constraints b
 where b.table_name in ('R_T', 'T');

OWNER           CONSTRAINT_NAME                C TABLE_NAME
--------------- ------------------------------ - ------------------------------
SCOTT           PK_T                           P T

   至此转换完成，希望对大家有所帮助！


--END

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

转载于:http://blog.itpub.net/26727294/viewspace-1782708/