如何用在线重定义的方式将普通表变为分区表

 

      很多时候我们建立了一个普通堆表，随着时间增长，dml会越来越慢，这个时候就会考虑到将该表变为分区表了，或者一开始忘记建分区表，把海量数据已经导进来之后，才想起来，这个时候就可以用在线重定义的方式将普通表变为分区表；

 

         普通表转换为分区表的做法：其中一种便是使用oracle自带的包进行重定义，需要注意几点：①需要有相应的系统权限；②重定义的方式，默认按照主键，可选择的为rowid;

包中的定义，可以用pl/sql工具看下包dbms_redefinition的用法

-- Constants for the options_flag parameter of start_redef_table
cons_use_pk CONSTANT PLS_INTEGER := 1;---主键重定义
cons_use_rowid CONSTANT PLS_INTEGER := 2;---rowid重定义

源表temp，查看下建表语句

 create table TEMP
( statis_date number,
  user_id          VARCHAR2(16),
  user_serv_number VARCHAR2(64),
  acct_name        VARCHAR2(512),
  group_name       VARCHAR2(100),
  group_id         VARCHAR2(20),
  product_type     VARCHAR2(20),
  manager_name     VARCHAR2(64),
  qf_area_name     VARCHAR2(60),
  balance          NUMBER,
  cycle_id_start   NUMBER,
  bad_balance      NUMBER
);

--建立索引

CREATE INDEX lzhm.IDX_WEBID_TICKET_CLINFO ON lzhm.temp(acct_name)

步骤一、测试能否表进行在线重定义，

     begin
             dbms_redefinition.can_redef_table('lzhm','temp');
     end;

出现错误

    ORA-12089: cannot online redefine table "LZHM"."TEMP" with no primary key
    ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
    ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
    ORA-06512: at line 2

原因是默认为主键在线重定义的方式，需要设定参数为2，按照rowid重定义；

     begin
        dbms_redefinition.can_redef_table('LZHM','TEMP',2);
     end;

或者

     begin
        dbms_redefinition.can_redef_table('LZHM','TEMP',dbms_redefinition.cons_use_rowid );
     end;

     PL/SQL procedure successfully completed

步骤二、创建一个和temp表结构一样的中间表（是分区表）

create table lzhm.temp_mid(
   statis_date              NUMBER,
  user_id                    VARCHAR2(16),
  user_serv_number  VARCHAR2(64),
  acct_name               VARCHAR2(512),
  group_name            VARCHAR2(100),
  group_id                  VARCHAR2(20),
  product_type           VARCHAR2(20),
  manager_name       VARCHAR2(64),
  qf_area_name         VARCHAR2(60),
  balance                   NUMBER,
  cycle_id_start          NUMBER,
  bad_balance           NUMBER
 )
partition by list (statis_date)
  (
    partition P20140731 values ('20140731')
      tablespace TBS_user_tj
      pctfree 10
      initrans 1
      maxtrans 255
      storage
      (
        initial 1M
        next 1M
        minextents 1
        maxextents unlimited
        pctincrease 0
      )
  );

步骤三、开始进行重定义

    execute dbms_redefinition.start_redef_table('lzhm','temp','temp_mid',null,2);

---------需要注意这里的输入参数选择
    PROCEDURE start_redef_table(uname IN VARCHAR2,----用户名
    orig_tableIN VARCHAR2,----源表名
    int_tableIN VARCHAR2,----中间表名
    col_mappingIN VARCHAR2 := NULL,---源表和中间表列之间的映射，map；
    options_flag IN BINARY_INTEGER := 1,---重定义方式
    orderby_cols IN VARCHAR2 := NULL,---对于分区表重定义的时候，分区列名
    part_nameIN VARCHAR2 := NULL);---对于分区表重定义的时候，需要重定义的分区。其中最后2个参数没用到，因为这里是由普通表转换为分区表；

  start_redef_table执行时间为12.5s 数据量为80万。

步骤三和步骤四中间步骤：

    select count(*)
    from temp；

    select count(*)
    from temp_mid;

----数据会有差别 而且与实际不符

----并且可以对表进行DML操作

    delete from temp
    where user_id=xxx;

步骤四、执行同步，保证数据的一致性：

    execute dbms_redefinition.sync_interim_table ('lzhm','temp','temp_mid‘);

步骤五、开始重定义之后，完成重定义之前，需要在中间表上创建与源表对应的索引、外键、触发器等，而中间表与源表对应的主键，如果需要按照主键重定义，需要再开始redefine之前创建，如果按照rowid进行重定义，对应主键呢？

    CREATE INDEX lzhm.IDX_WEBID_TICKET_CLIFO
    ON lzhm.temp_mid(acct_name)

步骤六、完成重定义

----完成重定义
    execute dbms_redefinition.finish_redef_table('lzhm','temp','temp_mid');

    select table_name,index_name,index_type,status
    from user_indexes
    where table_name in ('temp', 'temp_mid');

------看一下

    此时的temp的索引为lzhm.IDX_WEBID_TICKET_CLIFO,而非原来lzhm.IDX_WEBID_TICKET_CLINFO.

步骤七：删除没有用的中间表

DROP TABLE lzhm.temp_mid

 

结论：

重定义的方式有2个按照rowid、主键；

重定义的过程中可以对表进行DML操作，适合7*24的系统

重定义的速度还是挺快的；

重定义需要用户有一定系统权限、需要一个中间表（简单起见中间表的结构最好和源表一样，不一样的情况没有测试）、重定义开始到重定义结束需要在中间表上建一系列约束；重定义完成之后的效果是：

源表结构、约束信息与中间表的互相交换，二者数据量一致。

可以普通表转为分区表；

分区表也可以重定义。