当前位置: 首页 >> 技术文章 >> 14亿条记录,12c 做不到2小时内变更表结构字段类型?
14亿条记录,12c 做不到2小时内变更表结构字段类型?
发布时间:2020-03-19 发布人:袁长刚 95

墨天轮原文链接:https://www.modb.pro/db/22782 


导读:本文详细讲述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中进行,以下是整个变更过程:

1. 硬软配置一般,如下:

  • CPU:Intel® Xeon® CPU E7-4820 v3 @ 1.90GHz(物理4个,10核心,80个逻辑cpu)

  • 内存:500 GB

  • 存储:华为某型号

  • 数据库软件:Oracle 12.2 Nocdb RAC,未打补丁。


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

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


  COUNT(*)

----------

1399999996


Elapsed: 00:00:17.39


3. 创建临时表,有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' ) ,

..........



4. 定义参数,设置并行和行迁移。

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; --临时表开启行迁移



5. 检查原表是否支持在线重定义,比较快,仅用了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


6. 映射字段类型,启动重定义进程,用了近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


7. 复制依赖对象,这里只复制了主键约束,耗时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


8. 异步同步数据,耗时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


9. 完成在线重定义,结束任务,耗时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


10. 创建索引,这个分区表上的索引不多,就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


11. 收集统计信息,同样也是开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


12. 取消表、索引上的并行度,检查字段是否修改成功,删除临时表,至此整个修改过程结束,这里耗时约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;


13. 总结


总计执行耗时:95分钟,不到2小时,效率上暂时能接受,如果有更好的办法,求拍砖,谢谢。


  • 检查表定义 1秒

  • 启动重定义进程 10分钟

  • 复制依赖 54分钟

  • 异步同步数据 28秒

  • 执行结束任务 73秒

  • 创建索引 13分钟

  • 收集统计信息 4分钟

  • 取消并行检查删除临时表 10分钟