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

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

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

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

主要测试常见的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(*)fromOM_OFFERING_INST_TEST;COUNT(*)----------1399999996Elapsed:00:00:17.39

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

CREATETABLE"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)DEFAULT1)PARTITIONBYLIST("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'),..........

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

defineUSERNAME='CUSTINFO';--用户名defineSOURCE_TAB='OM_OFFERING_INST_TEST';--原表名defineINT_TAB='INT_OM_OFFERING_INST_TEST';--临时表名，需要手工提前创建definePARALLELS=35;--并行数，这里设的分区数altersessionenableparalleldml;altersessionforceparalleldmlparallel&PARALLELS;altersessionforceparallelqueryparallel&PARALLELS;altertable&INT_TABenablerowmovement;--临时表开启行迁移

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

SQL>begin2dbms_redefinition.can_redef_table(uname=>'&USERNAME',3tname=>'&SOURCE_TAB',4options_flag=>DBMS_REDEFINITION.CONS_USE_PK);5end;6/PL/SQLproceduresuccessfullycompletedExecutedin0.027seconds

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

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

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

SQL>DECLARE2num_errorsPLS_INTEGER;3BEGIN4DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname=>'&USERNAME',5orig_table=>'&SOURCE_TAB',6int_table=>'&INT_TAB',7copy_indexes=>0,8copy_triggers=>FALSE,9copy_constraints=>TRUE,10copy_privileges=>FALSE,11ignore_errors=>FALSE,12num_errors=>num_errors,13copy_statistics=>FALSE);14END;15/PL/SQLproceduresuccessfullycompletedExecutedin3230.441seconds

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

SQL>begin2dbms_redefinition.sync_interim_table(uname=>'&USERNAME',3orig_table=>'&SOURCE_TAB',4int_table=>'&INT_TAB');5end;6/PL/SQLproceduresuccessfullycompletedExecutedin27.908seconds

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

SQL>begin2dbms_redefinition.finish_redef_table(uname=>'&USERNAME',3orig_table=>'&SOURCE_TAB',4int_table=>'&INT_TAB');5end;6/PL/SQLproceduresuccessfullycompletedExecutedin72.302seconds

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

SQL>CREATEINDEX"CUSTINFO"."INX_OM_OFFERING_INST_TEST_CUSTID"ON"CUSTINFO"."OM_OFFERING_INST_TEST"("CUST_ID")onlineparallel35;IndexcreatedExecutedin257.138secondsSQL>CREATEINDEX"CUSTINFO"."INX_OM_OFFERING_INST_TEST_GROUPID"ON"CUSTINFO"."OM_OFFERING_INST_TEST"("GROUP_ID")onlineparallel35;IndexcreatedExecutedin244.853secondsSQL>CREATEINDEX"CUSTINFO"."INX_OM_OFFERING_INST_TEST_SUBSID"ON"CUSTINFO"."OM_OFFERING_INST_TEST"("SUBS_ID")onlineparallel35;IndexcreatedExecutedin261.665seconds

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

SQL>execdbms_stats.gather_table_stats(ownname=>'CUSTINFO',tabname=>'OM_OFFERING_INST_TEST',CASCADE=>true,degree=>35);PL/SQLproceduresuccessfullycompleted.Elapsed:00:04:18.35

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

--取消表上的并行altertable&SOURCE_TABnoparallel;--取消索引上的并行alterindexINX_OM_OFFERING_INST_TEST_CUSTIDnoparallel;alterindexINX_OM_OFFERING_INST_TEST_GROUPIDnoparallel;alterindexINX_OM_OFFERING_INST_TEST_SUBSIDnoparallel;--删除临时表droptable&INT_TAB;