Oracle 12c 能否在2小时内完成一张14亿条记录的表结构字段类型变更
发表于：2024-11-24 作者：千家信息网编辑
千家信息网最后更新 2024年11月24日，摘要：Oracle 12c 能否在2小时内完成一张14亿条记录的表结构字段类型变更原文链接： https://www.modb.pro/db/22782?cyn概述前面分享过Oracle大表在线修改的
千家信息网最后更新 2024年11月24日Oracle 12c 能否在2小时内完成一张14亿条记录的表结构字段类型变更摘要：Oracle 12c 能否在2小时内完成一张14亿条记录的表结构字段类型变更
原文链接： https://www.modb.pro/db/22782?cyn
概述
- 前面分享过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(*)----------1399999996Elapsed: 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 completedExecuted 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; 13 / PL/SQL procedure successfully completedExecuted 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 completedExecuted 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 completedExecuted 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 completedExecuted 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 createdExecuted in 257.138 secondsSQL> CREATE INDEX "CUSTINFO"."INX_OM_OFFERING_INST_TEST_GROUPID" ON "CUSTINFO"."OM_OFFERING_INST_TEST" ("GROUP_ID") online parallel 35;Index createdExecuted in 244.853 secondsSQL> CREATE INDEX "CUSTINFO"."INX_OM_OFFERING_INST_TEST_SUBSID" ON"CUSTINFO"."OM_OFFERING_INST_TEST" ("SUBS_ID") online parallel 35;Index createdExecuted 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;
总结
- 总计执行耗时：95分钟，不到2小时，效率上暂时能接受，如果有更好的办法，求拍砖，谢谢。
- 检查表定义 1秒
- 启动重定义进程 10分钟
- 复制依赖 54分钟
- 异步同步数据 28秒
- 执行结束任务 73秒
- 创建索引 13分钟
- 收集统计信息 4分钟
- 取消并行检查删除临时表 10分钟
索引
效率
场景
测试
信息
数据
过程
统计
字段
小时
在线
检查
类型
任务
普通
最高
优点
实际
缺点
脚本
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
绿色网络安全
我要自学网网络技术
国家网络安全保护义务是什么
上海智能化网络技术开发优化价格
看门狗2如何连接服务器
网络安全专家推荐信
pc我的世界怎么加入服务器
网络安全产品哪家信誉好
服务器连接线材质
重庆三支一扶服务器满后考核难吗
网络技术学院风发型
软件开发职位都有哪些
php服务器如何配置
学网络技术感受
数据库定义属性
有计算机网络技术哪个学校呗
广西网络安全警察支队
广东新华科技互联网学校是中专
安卓pda软件开发
网络安全事件论文
广州市软件开发学校报名
手机显示电子数据库异常
吉林卫星同步数显钟服务器
数据机房服务器
数据库投影运算解析
商海导航软件开发 微信
维修服务器
成都dell服务器销售商
网络安全法认为网络运营者
阿哥美剧软件开发
