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

摘要&＃xff1a;Oracle 12c 能否在2小时内在线完成一张14亿条记录的表结构字段类型变更

概述前面分享过Oracle大表在线修改的脚本(在线重定义)&＃xff0c;经过几轮的测试发现&＃xff0c;都存在些缺陷&＃xff0c;效率始终不是很满意。这次把索引和统计信息拆出来后发现效率相对算是最高的。

在线重定义的目标&＃xff0c;是对在线业务影响最小&＃xff0c;通过最短的锁表时间来实现表结构的变更&＃xff0c;锁表只发生在finish_redef_table过程中&＃xff0c;正式切换前先执行sync_interim_table过程异步同步数据&＃xff0c;以尽可能减少业务影响。

由于是要对客户的核心业务变更&＃xff0c;按管理要求没办法提前执行finish_redef_table前的过程&＃xff0c;且维护窗口时间有限&＃xff0c;业务又不能完全停掉&＃xff0c;才有了这次的测试。

主要测试常见的2种场景&＃xff0c;如下:场景1&＃xff1a;

复制全部依赖 - COPY_TABLE_DEPENDENTS(索引 &＃43; 约束 &＃43; 统计信息)&＃xff0c;触发器和权限这种基本没有&＃xff0c;就没有复制。

优点&＃xff1a;操作方便&＃xff0c;脚本直接把原表所有依赖全部复制过去&＃xff0c;改后的表直接使用&＃xff0c;不需要额外处理&＃xff0c;适合百万或千万的表&＃xff0c;且对效率没要求可用。

缺点&＃xff1a;上亿的表测试发现效率非常低。

场景2&＃xff1a;

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

优点&＃xff1a;目前针对10亿以上的表测试发现效率是最高的&＃xff0c;14亿的表全部弄完约2小时左右。

缺点&＃xff1a;操作过程稍微麻烦一点&＃xff0c;别的还好。

复制规则&＃xff0c;如下&＃xff1a;

copy_indexes &＃61;> 0,

copy_triggers &＃61;> FALSE,

copy_constraints &＃61;> TRUE,

copy_privileges &＃61;> FALSE,

ignore_errors &＃61;> FALSE,

num_errors &＃61;> num_errors,

copy_statistics &＃61;> FALSE);

由于场景1的效率比较差&＃xff0c;我这里就只列举场景2的测试过程&＃xff0c;后续实际业务变更也是在场景2中进行&＃xff0c;以下是整个变更过程&＃xff1a;硬软配置一般&＃xff0c;如下&＃xff1a;CPU&＃xff1a;Intel® Xeon® CPU E7-4820 v3 &＃64; 1.90GHz(物理4个&＃xff0c;10核心&＃xff0c;80个逻辑cpu)

内存&＃xff1a;500 GB

存储&＃xff1a;华为某型号

数据库软件&＃xff1a;Oracle 12.2 Nocdb RAC&＃xff0c;未打补丁。

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

SQL> select /*&＃43; parallel(40) */ count(*) from OM_OFFERING_INST_TEST;

COUNT(*)

----------

1399999996

Elapsed: 00:00:17.39

3. 创建临时表&＃xff0c;有35个分区&＃xff0c;部份省略了&＃xff0c;主键、索引等都不要建。

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 ( &＃39;000&＃39; ),

PARTITION "P_001" VALUES ( &＃39;001&＃39; ),

PARTITION "P_002" VALUES ( &＃39;002&＃39; ) ,

PARTITION "P_100" VALUES ( &＃39;100&＃39; ) ,

PARTITION "P_200" VALUES ( &＃39;200&＃39; ) ,

..........

4. 定义参数&＃xff0c;设置并行和行迁移

define USERNAME &＃61; &＃39;CUSTINFO&＃39;; --用户名

define SOURCE_TAB &＃61; &＃39;OM_OFFERING_INST_TEST&＃39;;-- 原表名

define INT_TAB &＃61; &＃39;INT_OM_OFFERING_INST_TEST&＃39;;-- 临时表名&＃xff0c;需要手工提前创建

define PARALLELS &＃61; 35; --并行数&＃xff0c;这里设的分区数

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. 检查原表是否支持在线重定义&＃xff0c;比较快&＃xff0c;仅用了1秒不到。

SQL> begin

2 dbms_redefinition.can_redef_table(uname &＃61;> &＃39;&USERNAME&＃39;,

3 tname &＃61;> &＃39;&SOURCE_TAB&＃39;,

4 options_flag &＃61;> DBMS_REDEFINITION.CONS_USE_PK);

5 end;

6 /

PL/SQL procedure successfully completed

Executed in 0.027 seconds

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

SQL> set timing on;

SQL> begin

2 DBMS_REDEFINITION.START_REDEF_TABLE(uname &＃61;> &＃39;&USERNAME&＃39;,

3 orig_table &＃61;> &＃39;&SOURCE_TAB&＃39;,

4 int_table &＃61;> &＃39;&INT_TAB&＃39;,

5 col_mapping &＃61;> &＃39;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&＃39;, --这里只列举了需要变更的字段类型

11 options_flag &＃61;> DBMS_REDEFINITION.CONS_USE_PK);

12 end;

13 /

PL/SQL procedure successfully completed

Executed in 576.565 seconds

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

SQL> DECLARE

2 num_errors PLS_INTEGER;

3 BEGIN

4 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname &＃61;> &＃39;&USERNAME&＃39;,

5 orig_table &＃61;> &＃39;&SOURCE_TAB&＃39;,

6 int_table &＃61;> &＃39;&INT_TAB&＃39;,

7 copy_indexes &＃61;> 0,

8 copy_triggers &＃61;> FALSE,

9 copy_constraints &＃61;> TRUE,

10 copy_privileges &＃61;> FALSE,

11 ignore_errors &＃61;> FALSE,

12 num_errors &＃61;> num_errors,

13 copy_statistics &＃61;> FALSE);

14 END;

15 /

PL/SQL procedure successfully completed

Executed in 3230.441 seconds

8. 异步同步数据&＃xff0c;耗时28秒&＃xff0c;比较快。

SQL> begin

2 dbms_redefinition.sync_interim_table(uname &＃61;> &＃39;&USERNAME&＃39;,

3 orig_table &＃61;> &＃39;&SOURCE_TAB&＃39;,

4 int_table &＃61;> &＃39;&INT_TAB&＃39;);

5 end;

6 /

PL/SQL procedure successfully completed

Executed in 27.908 seconds

9. 完成在线重定义&＃xff0c;结束任务,耗时73秒&＃xff0c;也是比较快。

SQL> begin

2 dbms_redefinition.finish_redef_table(uname &＃61;> &＃39;&USERNAME&＃39;,

3 orig_table &＃61;> &＃39;&SOURCE_TAB&＃39;,

4 int_table &＃61;> &＃39;&INT_TAB&＃39;);

5 end;

6 /

PL/SQL procedure successfully completed

Executed in 72.302 seconds

10. 创建索引&＃xff0c;这个分区表上的索引不多&＃xff0c;就3个普通索引&＃xff0c;开53个并行&＃xff0c;平均每个耗时4分钟左右&＃xff0c;累计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个并行&＃xff0c;耗时4分钟左右。CASCADE &＃61;> true表示收集表、列、索引等。

SQL> exec dbms_stats.gather_table_stats(ownname &＃61;> &＃39;CUSTINFO&＃39;,tabname &＃61;> &＃39;OM_OFFERING_INST_TEST&＃39;,CASCADE &＃61;> true,degree &＃61;> 35);

PL/SQL procedure successfully completed.

Elapsed: 00:04:18.35

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

总结总计执行耗时&＃xff1a;95分钟&＃xff0c;不到2小时&＃xff0c;效率上暂时能接受&＃xff0c;如果有更好的办法&＃xff0c;求拍砖&＃xff0c;谢谢。检查表定义 1秒

启动重定义进程 10分钟

复制依赖 54分钟

异步同步数据 28秒

执行结束任务 73秒

创建索引 13分钟

收集统计信息 4分钟

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