14亿条记录，12c 做不到2小时内变更表结构字段类型?

数据和云

发布于 2020-03-25 10:06:36

3880

发布于 2020-03-25 10:06:36

文章被收录于专栏：数据和云数据和云

导读：本文详细讲述Oracle 12c 在2小时内完成一张14亿条记录的表结构字段类型变更的过程，希望可以帮到工作中的大家，如有任何疑问，欢迎在本文的评论区交流讨论。

概述

Oracle大表在线修改的脚本（在线重定义），经过几轮的测试发现，都存在些缺陷，效率始终不是很满意。这次把索引和统计信息拆出来后发现效率相对算是最高的。主要测试常见的2种场景，如下:

场景1： 复制全部依赖 - COPY_TABLE_DEPENDENTS（索引 + 约束 + 统计信息），触发器和权限这种基本没有，就没有复制。

优点：操作方便，脚本直接把原表所有依赖全部复制过去，改后的表直接使用，不需要额外处理，适合百万或千万的表，且对效率没要求可用。
缺点：上亿的表测试发现效率非常低。

场景2： 有主键的表只复制约束 - COPY_TABLE_DEPENDENTS（会复制主键和唯一索引），其它索引和统计信息等重定义完成后再开并行重建和收集，这里要补充说明一下为什么要复制约束，因为创建主键不能并行操作，等重定义完成数据转换后，相当于在普通大表上创建主键，效率非常低。

优点：目前针对10亿以上的表测试发现效率是最高的，14亿的表全部弄完约2小时左右。
缺点：操作过程略微复杂。

复制规则，如下：

copy_indexes     => 0,
copy_triggers    => FALSE,
copy_constraints => TRUE,
copy_privileges  => FALSE,
ignore_errors    => FALSE,
num_errors       => num_errors,
copy_statistics  => FALSE);

由于场景1的效率比较差，我这里就只列举场景2的测试过程，后续实际业务变更也是在场景2中进行，以下是整个变更过程：

硬软配置一般，如下：

CPU：Intel® Xeon® CPU E7-4820 v3 @ 1.90GHz（物理4个，10核心，80个逻辑cpu）
内存：500 GB
存储：华为某型号
数据库软件：Oracle 12.2 Nocdb RAC，未打补丁。

我们先看一下原表数据行数,接近14亿条，人工造的，表实际大小和生产相差1倍以上。

SQL> select /*+ parallel(40) */ count(*) from OM_OFFERING_INST_TEST;

  COUNT(*)
----------
1399999996

Elapsed: 00:00:17.39

创建临时表，有35个分区，部份省略了，主键、索引等都不要建。

CREATE TABLE "CUSTINFO"."INT_OM_OFFERING_INST_TEST" (
    "BUSINESS_SEQ"               VARCHAR2(20),
    "PROD_ID"                    NUMBER(20, 0),
    "OFFERING_INST_ID"           NUMBER(20, 0),
    "OFFERING_ID"                NUMBER(20, 0),
    "OFFERING_NAME"              VARCHAR2(256),
    "OFFERING_CODE"              VARCHAR2(50),
    "CUST_TYPE"                  VARCHAR2(20),
    "CUST_ID"                    NUMBER(20, 0),
    "BRAND"                      VARCHAR2(50),
......
    "RECORD_STATUS"              NUMBER(3, 0) DEFAULT 1
)
    PARTITION BY LIST ( "BE_ID" ) ( PARTITION "P_000" VALUES ( '000' ),
        PARTITION "P_001" VALUES ( '001' ),
        PARTITION "P_002" VALUES ( '002' ) ,
        PARTITION "P_100" VALUES ( '100' ) ,
        PARTITION "P_200" VALUES ( '200' ) ,
..........

定义参数，设置并行和行迁移。

define USERNAME = 'CUSTINFO'; --用户名
define SOURCE_TAB = 'OM_OFFERING_INST_TEST';-- 原表名
define INT_TAB = 'INT_OM_OFFERING_INST_TEST';-- 临时表名，需要手工提前创建
define PARALLELS = 35; --并行数，这里设的分区数
alter session enable parallel dml ;
alter session force parallel dml parallel &PARALLELS;
alter session force parallel query parallel &PARALLELS;
alter table &INT_TAB enable row movement; --临时表开启行迁移

检查原表是否支持在线重定义，比较快，仅用了1秒不到。

SQL> begin
  2      dbms_redefinition.can_redef_table(uname        => '&USERNAME',
  3                                          tname        => '&SOURCE_TAB',
  4                                          options_flag => DBMS_REDEFINITION.CONS_USE_PK);
  5  end;
  6  /
PL/SQL procedure successfully completed
Executed in 0.027 seconds

映射字段类型，启动重定义进程，用了近10分钟，稍微有点慢。从这里开始到结束，如果中途有错误，想要重来，需要调abort_redef_table过程取消任务。

SQL> set timing on;
SQL> begin
  2    DBMS_REDEFINITION.START_REDEF_TABLE(uname        => '&USERNAME',
  3                                        orig_table   => '&SOURCE_TAB',
  4                                        int_table    => '&INT_TAB',
  5                                         col_mapping  => 'to_number(owner_party_role_id) owner_party_role_id,
  7                                                      to_number(offering_inst_id) offering_inst_id,
  8                                                      to_number(subs_id) subs_id,
  9                                                      to_number(group_id) group_id,
  10                                                      to_number(apply_obj_id) apply_obj_id', --这里只列举了需要变更的字段类型
  11                                        options_flag => DBMS_REDEFINITION.CONS_USE_PK);
  12  end;
/

PL/SQL procedure successfully completed

Executed in 576.565 seconds

复制依赖对象，这里只复制了主键约束，耗时54分钟，如果全部复制，我在测试跑了3个小时没有结果，直接Kill了。

SQL> DECLARE
  2      num_errors PLS_INTEGER;
  3  BEGIN
  4      DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname            => '&USERNAME',
  5                                              orig_table       => '&SOURCE_TAB',
  6                                              int_table        => '&INT_TAB',
  7                                              copy_indexes     => 0,
  8                                              copy_triggers    => FALSE,
  9                                              copy_constraints => TRUE,
 10                                              copy_privileges  => FALSE,
 11                                              ignore_errors    => FALSE,
 12                                              num_errors       => num_errors,
 13                                              copy_statistics  => FALSE);
 14  END;
 15  /
 
PL/SQL procedure successfully completed

Executed in 3230.441 seconds

异步同步数据，耗时28秒，比较快。

SQL> begin
  2      dbms_redefinition.sync_interim_table(uname        => '&USERNAME',
  3                                             orig_table => '&SOURCE_TAB',
  4                                             int_table  => '&INT_TAB');
  5  end;
  6  /

PL/SQL procedure successfully completed

Executed in 27.908 seconds

完成在线重定义，结束任务，耗时73秒，也是比较快。

SQL> begin
  2  dbms_redefinition.finish_redef_table(uname      => '&USERNAME',
  3                                       orig_table => '&SOURCE_TAB',
  4                                       int_table  => '&INT_TAB');
  5  end;
  6  /


PL/SQL procedure successfully completed

Executed in 72.302 seconds

创建索引，这个分区表上的索引不多，就3个普通索引，开53个并行，平均每个耗时4分钟左右，累计13分钟。

SQL> CREATE INDEX "CUSTINFO"."INX_OM_OFFERING_INST_TEST_CUSTID" ON "CUSTINFO"."OM_OFFERING_INST_TEST" ("CUST_ID") online parallel 35;

Index created
Executed in 257.138 seconds

SQL> CREATE INDEX "CUSTINFO"."INX_OM_OFFERING_INST_TEST_GROUPID" ON "CUSTINFO"."OM_OFFERING_INST_TEST" ("GROUP_ID") online parallel 35;

Index created
Executed in 244.853 seconds


SQL> CREATE INDEX "CUSTINFO"."INX_OM_OFFERING_INST_TEST_SUBSID" ON"CUSTINFO"."OM_OFFERING_INST_TEST" ("SUBS_ID") online parallel 35;

Index created
Executed in 261.665 seconds

收集统计信息,同样也是开35个并行，耗时4分钟左右。CASCADE => true表示收集表、列、索引等。

SQL> exec dbms_stats.gather_table_stats(ownname => 'CUSTINFO',tabname => 'OM_OFFERING_INST_TEST',CASCADE => true,degree => 35);

PL/SQL procedure successfully completed.
Elapsed: 00:04:18.35

取消表、索引上的并行度，检查字段是否修改成功，删除临时表，至此整个修改过程结束，这里耗时约10分钟左右。

--取消表上的并行
alter table &SOURCE_TAB noparallel;

--取消索引上的并行
alter index INX_OM_OFFERING_INST_TEST_CUSTID noparallel;
alter index INX_OM_OFFERING_INST_TEST_GROUPID noparallel;
alter index INX_OM_OFFERING_INST_TEST_SUBSID noparallel;

--删除临时表
drop table &INT_TAB;