通过DBMS_REDEFINITION更新大表

更新大表的时候通常伴随着大量的redo和undo产生，消耗了不少的资源。同时如果这个字段较长的话，也会产生许多行迁移，因为肯定会有某些块无法容纳下更新后的行的大小。

通常考虑通过DDL的方式来替代对这些大表的更新DML操作，第一时间想到的可能就是CTAS，最后rename回去的方式。但是这会带来一些其他的问题，比如表的其他属性并不会复制到新表当中，其他的约束和索引等都需要重建，工作比较繁琐也容易漏掉，而且这种变更表操作是需要停机时间的。而通过在线重定义的方式则可以免去这些烦恼，程序会自动帮我们将这些依赖关系同步过去。通常我们使用在线重定义都是作为改变表的存储属性用途，但是别忘了，在线重定义同样支持字段匹配的功能，只要我们的字段匹配是确定的，那我们可以获得更多有价值的内容，包括更新操作。

首先建两张测试表，T1和T2，T1做为被更新的表，T2作为T1更新字段所需要关联的表

drop table t1 purge;
create table t1 as
select object_id ,object_name 
from dba_objects 
where object_id is not null;

drop table t2 purge;
create table t2 as
select object_id ,object_name ,object_id||object_name cname
from dba_objects 
where object_id is not null;

alter table t1 
   add constraint pk_t1
   primary key (object_id);

alter table t2 
   add constraint pk_t2
   primary key (object_id);

这里给T1加一个字段cname，然后通过与T2表的object_id字段关联，将T2表的cname更新到T1表cname字段中

alter table t1 add cname varchar2(200);

xb@PDB12C> select * from t1 where rownum>10;

 OBJECT_ID OBJECT_NAME                         CNAME
---------- ----------------------------------- --------------
         9 I_FILE#_BLOCK#
        38 I_OBJ3
        45 I_TS1
        51 I_CON1
        19 IND$
        31 CDEF$
         6 C_TS#
        58 I_CCOL2
        24 I_PROXY_DATA$

xb@PDB12C> select * from t2 where rownum>10;

 OBJECT_ID OBJECT_NAME          CNAME
---------- -------------------- ---------------------------
         9 I_FILE#_BLOCK#       9I_FILE#_BLOCK#
        38 I_OBJ3               38I_OBJ3
        45 I_TS1                45I_TS1
        51 I_CON1               51I_CON1
        19 IND$                 19IND$
        31 CDEF$                31CDEF$
         6 C_TS#                6C_TS#
        58 I_CCOL2              58I_CCOL2
        24 I_PROXY_DATA$        24I_PROXY_DATA$

在DBMS_REDEFINITION中无法使用子查询或者表关联，但是可以用确定性的表达式，在这个例子中可以使用函数的方式

create or replace
function get_cname(v_id int )  return varchar2 deterministic is
  f varchar2(200);
begin
  select  cname
  into f
  from t2
  where object_id = v_id;
  return f;
end;
/

建一张临时表用作重定义的过程中转换的数据

drop table t_tmp purge;
create table t_tmp (
  object_id number,
  object_name varchar2(200),
  cname varchar2(200));

那现在可以使用DBMS_REDEFINITION包了，这里需要注意的重点是COL_MAPPING参数，这里将配置之前建的get_cname函数来获取要更新T1表的数据

declare
    l_colmap varchar(512);
  begin
    l_colmap :=
          'OBJECT_ID
          ,OBJECT_NAME
          ,get_cname(OBJECT_ID) cname';

    dbms_redefinition.start_redef_table
    (  uname           => user,
       orig_table      => 'T1',
       int_table       => 'T_TMP',
       col_mapping   => l_colmap );
 end;
 /

在这里不需要考虑其他依赖对象，只需要COPY_TABLE_DEPENDENTS就可以解决这些问题

var nerrors number
begin
  dbms_redefinition.copy_table_dependents
    ( user, 'T1', 'T_TMP',
      copy_indexes => dbms_redefinition.cons_orig_params,
      num_errors => :nerrors );
end;
/

xb@PDB12C> print nerrors

   NERRORS
----------
         0

-- 完毕
begin
  dbms_redefinition.finish_redef_table
    ( user, 'T1', 'T_TMP' );
end;
/

最后检查T1表的数据

xb@PDB12C> select * from t1 where rownum>10;

 OBJECT_ID OBJECT_NAME          CNAME
---------- -------------------- ----------------------
         9 I_FILE#_BLOCK#       9I_FILE#_BLOCK#
        38 I_OBJ3               38I_OBJ3
        45 I_TS1                45I_TS1
        51 I_CON1               51I_CON1
        19 IND$                 19IND$
        31 CDEF$                31CDEF$
         6 C_TS#                6C_TS#
        58 I_CCOL2              58I_CCOL2
        24 I_PROXY_DATA$        24I_PROXY_DATA$

在线重定义操作避免了业务的中断，不需要考虑依赖对象的问题，特别是对大表的操作起到很好的效果。