导读:本文详细讲述Oracle 12c 在2小时内完成一张14亿条记录的表结构字段类型变更的过程,希望可以帮到工作中的大家,如有任何疑问,欢迎在本文的评论区交流讨论。
Oracle大表在线修改的脚本(在线重定义),经过几轮的测试发现,都存在些缺陷,效率始终不是很满意。这次把索引和统计信息拆出来后发现效率相对算是最高的。主要测试常见的2种场景,如下:
场景1: 复制全部依赖 - COPY_TABLE_DEPENDENTS(索引 + 约束 + 统计信息),触发器和权限这种基本没有,就没有复制。
场景2: 有主键的表只复制约束 - COPY_TABLE_DEPENDENTS(会复制主键和唯一索引),其它索引和统计信息等重定义完成后再开并行重建和收集,这里要补充说明一下为什么要复制约束,因为创建主键不能并行操作,等重定义完成数据转换后,相当于在普通大表上创建主键,效率非常低。
复制规则,如下:
copy_indexes => 0,
copy_triggers => FALSE,
copy_constraints => TRUE,
copy_privileges => FALSE,
ignore_errors => FALSE,
num_errors => num_errors,
copy_statistics => FALSE);
SQL> select /*+ parallel(40) */ count(*) from OM_OFFERING_INST_TEST;
COUNT(*)
----------
1399999996
Elapsed: 00:00:17.39
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; --临时表开启行迁移
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 completed
Executed in 0.027 seconds
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;
/
PL/SQL procedure successfully completed
Executed in 576.565 seconds
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 completed
Executed in 3230.441 seconds
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 completed
Executed in 27.908 seconds
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 completed
Executed in 72.302 seconds
SQL> CREATE INDEX "CUSTINFO"."INX_OM_OFFERING_INST_TEST_CUSTID" ON "CUSTINFO"."OM_OFFERING_INST_TEST" ("CUST_ID") online parallel 35;
Index created
Executed in 257.138 seconds
SQL> CREATE INDEX "CUSTINFO"."INX_OM_OFFERING_INST_TEST_GROUPID" ON "CUSTINFO"."OM_OFFERING_INST_TEST" ("GROUP_ID") online parallel 35;
Index created
Executed in 244.853 seconds
SQL> CREATE INDEX "CUSTINFO"."INX_OM_OFFERING_INST_TEST_SUBSID" ON"CUSTINFO"."OM_OFFERING_INST_TEST" ("SUBS_ID") online parallel 35;
Index created
Executed in 261.665 seconds
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
--取消表上的并行
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小时,效率上暂时能接受,如果有更好的办法,求拍砖,谢谢。
墨天轮原文链接:https://www.modb.pro/db/22782