Oracle 12c 能否在2小时内完成一张14亿条记录的表结构字段类型变更
发表于：2024-11-23 作者：千家信息网编辑
千家信息网最后更新 2024年11月23日，摘要：Oracle 12c 能否在2小时内完成一张14亿条记录的表结构字段类型变更原文链接： https://www.modb.pro/db/22782?cyn概述前面分享过Oracle大表在线修改的
千家信息网最后更新 2024年11月23日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巴赫服务器
代表城市发展的数据库
杭州轻松购网络技术
服务器上市时间表
西安手机软件开发哪家公司好
安国租房软件开发
厦门限时团购清货软件开发
姑苏区网络技术咨询报价
谁负责网络安全保护和监督管理
哪个服务器可以玩生存挑战赛
discord游戏服务器
360网络安全是越老越吃香吗
梅州卫星软件开发回收价
贵州省信息网络技术考试试题
网络安全黑龙江合作院校
南开区应用软件开发服务供应
植物查询核苷酸序列数据库
