ZT 寫有效的歷史資料遷移sql
利用分頁技術,分批對歷史資料遷移,適用於沒分割槽的大表,以後有空實際測試下效能
[@more@]
對於高併發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;
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;
3.使用bulk collect
declaretype 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;
1.優於常規寫法,可以分批多次進行提交,加入異常處理可以避免全部資料回滾。
2.優於for遷移,藉助中間小表一次遷移多條記錄,大大降低了insert,delete的執行次數。
3.主要是提供給大家一種思路。
結論:
其實寫法好沒有好壞之分,關鍵在於怎麼用,就大批次遷移資料來說,我覺得3,4都是比較可行的方案。歡迎大家拍磚討論,也歡迎貢獻更好的資料遷移辦法。
注:上面指令碼是虛擬碼
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/82387/viewspace-1016694/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 寫有效的歷史資料遷移sqlSQL
- 使用SQL SERVER儲存過程實現歷史資料遷移SQLServer儲存過程
- 資料庫歷史資料有效管理資料庫
- 儲存所有歷史提交資料下遷移git倉庫Git
- 異構資料庫遷移 sql等價改寫資料庫SQL
- 改寫資料庫歷史的中國人資料庫
- ZT 3000萬資料成功遷移
- Oracle10g 資料遷移方案(zt)Oracle
- [zt]prebuilt 物化檢視遷移資料庫UI資料庫
- SQL Server資料庫遷移SQLServer資料庫
- 【SQL】【遷移】寫了一個簡單的sequence遷移指令碼SQL指令碼
- 動態SQL完成大表資料的遷移SQL
- 資料的遷移
- zt 跨平臺 跨版本 大規模資料遷移
- 走進資料的歷史
- ZT:TOAD 配置遷移
- Oracle 9i 11g歷史庫升級遷移資料至19c CDBOracle
- 遷移資料.
- 【遷移】使用rman遷移資料庫資料庫
- 歷史股票資料的爬取
- SQL 遷移資料庫至ORACLE簡易方法SQL資料庫Oracle
- ZT 遷移案例一: oracle 8i 檔案遷移Oracle
- 一次用rman做資料遷移的實戰經歷
- 【資料遷移】使用傳輸表空間遷移資料
- 伺服器資料遷移的方法-硬體不同如何遷移資料伺服器
- MySQL儲存過程-->通過遊標遍歷和異常處理遷移資料到歷史表MySql儲存過程
- Kafka資料遷移Kafka
- 資料庫遷移資料庫
- redis資料遷移Redis
- 轉資料遷移
- ORACLE 資料遷移Oracle
- DXWB 資料遷移
- Harbor資料遷移
- 資料檔案的遷移
- Jim Gray:資料管理歷史
- SQL Server 資料庫最小當機遷移方案GESQLServer資料庫
- SQL SEVER 2008 資料檔案遷移SQL
- 使用SQL Developer 遷移異構資料庫到OracleSQLDeveloper資料庫Oracle