Oracle 12c 能否在2小时内完成一张14亿条记录的表结构字段类型变更
发表于：2025-01-20 作者：千家信息网编辑
千家信息网最后更新 2025年01月20日，摘要：Oracle 12c 能否在2小时内完成一张14亿条记录的表结构字段类型变更原文链接： https://www.modb.pro/db/22782?cyn概述前面分享过Oracle大表在线修改的
千家信息网最后更新 2025年01月20日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安全错误
数据库的锁怎样保障安全
网络安全知识2021
国防科技大学互联网大会
网络安全认证凯旋家具
御坂网络服务器下载
网络安全二本
window服务器如何租用
人力培训数据库
我的世界启动器枪械服务器是什么
黄山网络安全考试学习
安徽中品网络技术地址
中外数据库
天元网络安全手抄报
网络技术服务标准费用
荒野乱斗为什么总是连接服务器
电商数据库课设
一念逍遥如何切换服务器
达梦数据库 代理商
局域网服务器安装位置
口碑最好的excel服务器
文件管理是不是服务器功能
网络安全裴智勇
加强公共网络安全
虚拟者网络安全团队官方博客
软件开发需要c语言吗
传奇私服服务器租
数据库系统包括哪三部分
新罗区凯航网络技术工作室
奉贤区系统软件开发中心
网络技术有限公司可以注册吗
还原苹果手机连接服务器出现问题
