Oracle 12c 能否在2小时内在线完成一张14亿条记录的表结构字段类型变更
发表于:2024-10-10 作者:千家信息网编辑
千家信息网最后更新 2024年10月10日,原文链接: https://www.modb.pro/db/22782摘要:Oracle 12c 能否在2小时内在线完成一张14亿条记录的表结构字段类型变更概述前面分享过Oracle大表在线修改的脚本
千家信息网最后更新 2024年10月10日Oracle 12c 能否在2小时内在线完成一张14亿条记录的表结构字段类型变更
原文链接: https://www.modb.pro/db/22782
摘要: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(*) 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安全错误
数据库的锁怎样保障安全
世界顶尖网络技术
南邮数据库考试
软件开发合伙销售协议范本
服务器硬件环境要求
我的世界服务器动物
如何查询数据库表里有几行数据
原神国际服什么服务器好
浦江软件开发流程
高青配货站开票软件开发公司
服务器bios管理口ip配置
高级数据库技术难吗
非法服务器地址
广东省网络安全技能
服务器两个网口怎么通讯
ncbi官网数据库
win7电脑管理服务器
米哈游四川服务器开发
linux数据库编程
mssql文件备份到新数据库
合理用药数据库教学
太极股份做软件开发的子公司
软件开发的公司做账
无限猫网络技术有限公司
凡乐网络技术有限公司怎么样
网络安全公司排行2019
新基建对网络安全有什么影响
软件开发工程师入行要多久
软件开发属于施工
网络安全问题讨论题
开展网络安全通知