写有效的历史数据迁移sql
来源:互联网 发布:mac如何在pdf上签名 编辑:程序博客网 时间:2024/05/22 17:41
转自:http://rdc.taobao.com/blog/dba/html/124_oracle_data_transfer.html
作者:丁原
作者:丁原
对于高并发oltp系统,生产库可能只需保留当前几个月的数据,之前的数据要全部迁移到历史库中。那么,如何处理这样的需求,如何写合适的历史迁移程序呢?
1.常规写法
begin
--迁移数据
insert into tb_users
select *
from tb_users_dbc a
where gmt_modified <= trunc(sysdate) - v_day and rownum<5000000;
--删除原表数据
delete from tb_users_dbc
where gmt_modified <= trunc(sysdate) - v_day and rownum<5000000;
commit;
end;
--迁移数据
insert into tb_users
select *
from tb_users_dbc a
where gmt_modified <= trunc(sysdate) - v_day and rownum<5000000;
--删除原表数据
delete from tb_users_dbc
where gmt_modified <= trunc(sysdate) - v_day and rownum<5000000;
commit;
end;
2.使用for循环
declare
v_counts number := 0;
begin
--中间表
insert into tmp_tb_users
select *
from tb_users_dbc
where gmt_modified <= trunc(sysdate) - v_day and rownum<5000000;
commit;
for i in (select id from tmp_tb_users) loop
--数据迁移
insert into tb_users
select * from tb_users_dbc where id = i.id;
delete from tb_users_dbc where id = i.id;
v_counts := v_counts + 1;
--分批提交
if mod(v_counts, 100) = 0 then
commit;
end if;
end loop;
end;
v_counts number := 0;
begin
--中间表
insert into tmp_tb_users
select *
from tb_users_dbc
where gmt_modified <= trunc(sysdate) - v_day and rownum<5000000;
commit;
for i in (select id from tmp_tb_users) loop
--数据迁移
insert into tb_users
select * from tb_users_dbc where id = i.id;
delete from tb_users_dbc where id = i.id;
v_counts := v_counts + 1;
--分批提交
if mod(v_counts, 100) = 0 then
commit;
end if;
end loop;
end;
3.使用bulk collect
declare
type rec_rids is table of varchar2(32);
v_rids rec_rids;
cursor cur_aids is
select id
from tb_users_dbc
where gmt_modified <= trunc(sysdate) - v_day and rownum<5000000;
begin
open cur_aids;
loop
fetch cur_aids BULK COLLECT INTO v_rids LIMIT 1000;
for i in 1 .. v_rids.count loop
--数据迁移
insert into tb_users
select * from tb_users_dbc where id = v_rids(i);
delete from tb_users_dbc where id = v_rids(i);
end loop;
commit;
EXIT WHEN v_rids.COUNT = 0;
end loop;
close cur_aids;
end;
type rec_rids is table of varchar2(32);
v_rids rec_rids;
cursor cur_aids is
select id
from tb_users_dbc
where gmt_modified <= trunc(sysdate) - v_day and rownum<5000000;
begin
open cur_aids;
loop
fetch cur_aids BULK COLLECT INTO v_rids LIMIT 1000;
for i in 1 .. v_rids.count loop
--数据迁移
insert into tb_users
select * from tb_users_dbc where id = v_rids(i);
delete from tb_users_dbc where id = v_rids(i);
end loop;
commit;
EXIT WHEN v_rids.COUNT = 0;
end loop;
close cur_aids;
end;
很少考虑常规写法,数据迁移一般会选择采用2,3种方法。
相比for迁移方式,采用bulk collect避免了上下文切换,在大批量数据迁移中还是有很大优势的,推荐采用bulk collect数据迁移方案。
Asktom上有很多关于历史迁移方案的帖子,Tom的答案一直都是常规写法,也就是上面的第一种写法,可是常规写法消耗大量的资源,出现异常时整个事务都得回滚。因此很多人都认为Tom的方案不可行,认为Tom并没有接触过大数据库等。
4.借助Tom的思想,把迁移的数据拆分成n个小表,对n个小表进行迁移
declare
pagecount number;
sumcount number;
loopcount number;
begin
--取要迁移的数据
execute immediate 'truncate table tmp_tb_users';
insert into tmp_tb_users
select id
from tb_users_dbc
where gmt_modified <= trunc(sysdate) - v_day and rownum < 5000000;
--计算迁移总数
select count(*) into sumcount from tmp_tb_users;
--设定每次迁移条数
pagecount = 2000;
--得到循环次数
loopcount := sumcount / pagecount + 1;
for i in 1 .. loopcount loop
begcount := (i - 1) * pagecount + 1;
endcount := i * pagecount;
--构建中间小表
execute immediate 'truncate table mid_tb_users';
insert into mid_tb_users
select id, b.rn
from tmp_tb_users b where b.rn >= begcount and b.rn <= endcount;
--小表和原表进行关联,迁移数据
insert into tb_users
select f.*
from mid_tb_users t, tb_users_dbc f
where t.biz_order_id = f.biz_order_id
and t.rn >= begcount and t.rn < = endcount;
--删除原表数据
delete from tb_users_dbc where id in (select id from mid_tb_users);
commit;
end loop;
end;
pagecount number;
sumcount number;
loopcount number;
begin
--取要迁移的数据
execute immediate 'truncate table tmp_tb_users';
insert into tmp_tb_users
select id
from tb_users_dbc
where gmt_modified <= trunc(sysdate) - v_day and rownum < 5000000;
--计算迁移总数
select count(*) into sumcount from tmp_tb_users;
--设定每次迁移条数
pagecount = 2000;
--得到循环次数
loopcount := sumcount / pagecount + 1;
for i in 1 .. loopcount loop
begcount := (i - 1) * pagecount + 1;
endcount := i * pagecount;
--构建中间小表
execute immediate 'truncate table mid_tb_users';
insert into mid_tb_users
select id, b.rn
from tmp_tb_users b where b.rn >= begcount and b.rn <= endcount;
--小表和原表进行关联,迁移数据
insert into tb_users
select f.*
from mid_tb_users t, tb_users_dbc f
where t.biz_order_id = f.biz_order_id
and t.rn >= begcount and t.rn < = endcount;
--删除原表数据
delete from tb_users_dbc where id in (select id from mid_tb_users);
commit;
end loop;
end;
1.优于常规写法,可以分批多次进行提交,加入异常处理可以避免全部数据回滚。
2.优于for迁移,借助中间小表一次迁移多条记录,大大降低了insert,delete的执行次数。
3.主要是提供给大家一种思路。
结论:
其实写法好没有好坏之分,关键在于怎么用,就大批量迁移数据来说,我觉得3,4都是比较可行的方案。欢迎大家拍砖讨论,也欢迎贡献更好的数据迁移办法。
注:上面脚本是伪代码
- 写有效的历史数据迁移sql
- 写有效的历史数据迁移sql
- 写有效的web分页sql语句
- 历史数据迁移总结
- SAP与ERPv1.0的历史数据迁移简析
- 通过日志恢复SQL Server的历史数据
- 数据库定时删除历史数据的SQL指令
- 历史数据迁移测试(一)
- 历史数据迁移测试(二)
- 【数据迁移】在线数据迁移 :双写数据库、搬历史数据、切换写入、灰度验证(99.9999%)、删除历史
- 有效的写单元测试
- 历史数据迁移 使用触发器 trigger 示例
- 有效的编写SQL
- SQL历史数据相关触发器示例
- 历史数据的保存
- 股票历史数据的获取
- 股票历史数据的获取
- 【详解】Python带历史数据写Excel表
- 介绍一个firefox的插件Gmarks,同步你的google书签
- vcVC中Radio控件的用法
- vcpicture控件的使用
- 厦门人民笑了 南京人民哭了
- vc 设置EDIT中文字颜色方法
- 写有效的历史数据迁移sql
- vc 告别图标失真的烦恼
- 客户端上传文件&服务器端保存数据
- 教你如何在jsp里连接sql数据库
- vc 将数据四舍五入的方法
- vc如何在Excel中插入Flash
- vc TCP/IP
- 谈JAVA中如何将两个byte数组合成一个数组
- vc Socket