Oracle12c能否在2小时内完成一张14亿条记录的表结构字段类型变更
原文链接：https://www.modb.pro/db/22782?xy（复制至浏览器即可查看）
摘要：Oracle 12c 能否在2小时内在线完成一张14亿条记录的表结构字段类型变更
概述
- 前面分享过Oracle大表在线修改的脚本（在线重定义），经过几轮的测试发& j 7 0 S z e u * COPY_TABLE_DEPENDENTS（索引 + 约束 + 统计信息），触发器和权限这种基本没有，就没有复制。
- 优点：操作方便，脚本直接把原表所有依赖全部复制过去，改后的表直接使用，不需要额外处理，适合百万或千万的表R } a c，且对效率没要求可用。
- 缺点：上亿的表测试发现效率非常低。
- 场景2：有主键的表只复制约束 - COPY_TABLE_DEPENDENTS（会复制主键和7 & l * & y唯一索引），其它索引和统计信息等C s N =重定义完成后再开并行重建和收集，这里要补充说明一下为什么要复制约^ F w @ 5 Q [束，因为创建主键不能并行操作h ~ X l 2 X | {，等重定义完成数据转换后，相当于在普通大表上创建主键，效率非常低。
- 优点：目前针对10亿以上的表测试发现效率是最高的，14亿的表全部弄完约2小时左右。
- 缺点：操作过程稍微麻烦一点，别的还好。
- 复制规则，如下：
- 硬软配置一般，如下：
- CPU：Intel Xeon CPU E7-4820 v3 @ 1.90GHc + z R Z Tz（物理4个，10核心，80个逻辑cpu） D z
- 内存：500 GB
- 存储：华为某型号
- 数据库软件：Og m e o R 8 { & Jracle 12.2 Nocdb RAC，未打补丁。
- 我们先看一下原表数据行数,接近14亿条，人工造的，表实际大小和生产相9 D E p R a差1倍以上。
- 创建临时表，有35个分区，部份省略了，主键、索引等都不V B * r 0要建。
- 复制依赖对象，这里只复制了主键约束，耗时54分钟，如果全部复制，我在测试跑了3个小时没有结果，只接Kill了。
- 异步同步数据，耗时28秒，比较快。
- 完成在线重定义，结束任务,耗时73秒，也是比较快。
- 创建索引，这个分区表上的索引* + M + F ) / ~ ~不多，就3个普通索引，开53个并行，平均每个耗时4分钟左右，累计13分钟。
- 收集统计信息,同样也是开35个并行，耗时4分钟左右。CASCADE => true表示收集表、X , V H 0列、索引等。
- 取消表、索引上的并行度，检查字段是否修改成功，删除临时表，至此整个修改过程结束，这里耗时约10分钟左右。
- 总计执行耗时：95W ? 8分钟，不到2小时，效率上暂时能接受，如果有更好的办法，求拍砖，谢谢。
- 检查表定义 1T C H秒
- 启动重定义进程 10分钟
- 复制依赖 54分钟
- 异步同步数据 28秒
- 执行结束任务 73秒
- 创建索引 13分钟
- 收集统计信息 4分钟
- 取消并行检查删除临时表 10分钟
copy_indexes => 0,
copy_triggers => FALSE,
copy* 6 G R _ L ; A_constraints => TRUE,
copy_privileges => FALSE,
ignore_errors => FALSE,
n8 + uum_errors => num_errors,
copy_statistics =&d z kgt; FALSE);
由于场景1的效率比较差，我这里就只列举场景2的测试过程，后续实际业务变更也是在场景2中进行，以下是整个变更过程：
SQL> select /*+ parallel(40) *) N c u a { (/ count(*) from OM_OFFERIJ Q x A 0 DNG_INST_TEST;
COUNT(*)
----------
1399999996
Elapsed: 00:00:17.39
CREATE{ R p c m Q TABLE \"CUSTINFO\".\"INT_OM_OFFERING_INST_TEST\" (
\"BUSINESS_SEQ\" VARCHAR2(20),
\"PROD( 5 5 0 up;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_numbert v ^ 1 { 8 c(subs_id) subs_id,
9 to_number(group_id) group_id,
10 to_number(apply_obj_id) apply_~ } 6 U $ o R w Aobj_id\', --这里只列举了需要变更的字段类型
11 options_flag => DBMS_REDEFINITION.CONS_USE_PK);
12 end;
13 /
PL/SQL procedure successfuP J # U F F G #lly complete@ 9 l W 8 . #d
Executed in 576.565 seconds
SQLa J N I a J k & J> DECLARE
2 num_errz ] a Q 4 s x gors PLS_INTEGER;
3 BEGIN
4 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => \'&USERNAME\',M A 7 i s T J a 3
5 orig_table => \'&SOUv ` c 1 ` 0 / 3RCE_TAB\',
6 int_tabw X s [ G a w } c s_TAB\',
7 copy_indexes => 0,
8 copy_triggerk X * r { F c 6s =t r G 4 2> FALSE,
9 copy_V / k b i K lconstraints => TRUE,
10 coB L ! C m 9py_privileges => FALSE,m | H 1 G 3 9
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
SQL> begin
2 dbms_redefinition.sync_interim_table(uname => \'&USERNAMEX : z\',
3 orig_Z ` Z g @ W e ~table => \'&SOURCE_TAB\',
4 int_table => \Y G q T X'&INT_TAB\');
5 end;
6 /
PL/SQL procedure successfully completed
Executed in 27.908 seconds
SQL> begin
2 dbms_redefinie X ation.finish_redef_ta h !able(uname => \'&USERNAME\',
3 orig_table => \'&SOURCE_TAB\',
4 int_table => \'a J 4 ?&INT_TAB\');
5 end;
6 /
PL/SQL procedure successfully completed
Executed in 72.302 seconds
SQL> CR{ | M 4 jEATEa } h H INDEX \"CUSTINFO\".\"INX_OM_OFFEl & /RING_INST_TEST_CUSTID\" OU I U #N \"M ? =CUSTINFO\".\"OM_OFFERIND / ` k kG_INST_TEST\" (\"CUSTF ; o l t q f 7_ID\") online parallel 35;
Index created
Executed in 257.138 seconds
SQL> CREATJ A g . J s _E INDEX \"CUSTINFO\".~ F 7 d / h\"INX_OM_y q M X _ XOFFERING_INST_TEST_GROUPID\" ON \"CUSTINFO _ 8 K H oO\".\"OM_OFFERING_INST_TEST\" (\"GROUP_ID\") online parallel 35;
Index created
Executed in 244.853 secondL n E J (s
SQL> CREATE INDEX \"CUSTINFO\".\"INX_OM_OFFERING_INST_TEST_SUBSID\" ON\"CUSTIG B 0 ~ O [ 8 vNFO\".\"OM_OFFERING_INST_TEST\" (\"SUBS_ID\") online parallel 35;
Index created
Executed in 261.66u E v ( Z ( ~ 45 seconds
SQL> exec dbms_stats.gather_tab8 ! Ale_stats(ownname => \'CUSTIo ^ t $ O - + wNFO\',tabname => \'OM_OFFERING_INST_TEST\',CASCADE => true,degrep v _ 9 Fe => 35);
PL/SQL procedure successfully completed.
Elapsed: 00:04q u 7 d P:18.35t A h e
--取消表上的并行
alter tah M Kble &SOURCE_TAB noparY q s dallel;
--取消索引上的并行
alter index INX_OM_` U ; 5 # V EOFFERING_INST_TEST_CUSTID noparallel;
alter index INX_OM_OFFERING_INST_TEST_GROUPID noparallel;
alter index INX_OM_OFFERING_INST_TEST_* W gSUBSID noparallel;
--删除临时表
drop table &INT_TAB;