一次使用暫存資料表最佳化資料處理的過程

一次使用暫存資料表最佳化資料處理的過程

 

 

 

 

 

 

 

 

 

 

寫了一個資料處理程式，從遠端一資料庫中，將符合要求的資料過濾後插入到本機資料庫中。資料涉及兩張表A，B，其中A表的記錄六十萬條左右，B表的記錄二十萬條左右。

需要從A表中，查詢某種交易類型（每種交易類型有若干“套”（即幾條記錄的集合，類似會計分錄），每套中有若干條記錄），然後將得到的結果的每一條，再和該交易類型內的一套記錄中尋找該套內是否有對應的記錄，有的話才算一條滿足要求的記錄。對B表的處理類似。要求A表中處理四種交易類型，B表中處理一種交易類型。

1．遠端資料庫是生產庫，不允許私自串連，只能通過DBLINK。

2．兩表上只有主鍵，沒有我查詢需要使用的索引，無法建立索引。

3．A，B兩表不可以建立在本地。

寫了一個資料處理程式，使用遊標，將查詢得到的結果迴圈處理。查詢某種交易類型的資料的遊標開啟就要二分鐘。然後迴圈在套中校正資料的時候，都要使用全表掃描，五種交易類型處理下竟然要用四個多小時，處理的記錄數大概有兩百多萬條。得到大概一千五百條滿足條件的記錄。

 

 

 

 

 

 

 

 

 

 

 

需要改寫程式。不能建立索引，不能將資料存到本地，於是就想到了使用暫存資料表。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

建立會話級暫存資料表，在會話退出時Truncata 暫存資料表。並在建立的暫存資料表上建立幾個索引，資料處理的時候要用的索引。

 

 

 

 

 

 

 

 

 

 

drop table vhh;

 

 

 

 

 

 

 

 

 

 

drop table txn;

 

 

 

 

 

 

 

 

 

 

create global temporary table vhh on commit preserve rows as select * from A@dblink_Remote where 1=2;

 

 

 

 

 

 

 

 

 

 

create global temporary table txn on commit preserve rows as select * from B@dblink_Remote where 1=2;

 

 

 

 

 

 

 

 

 

 

drop index vhh_0;

 

 

 

 

 

 

 

 

 

 

drop index vhh_1;

 

 

 

 

 

 

 

 

 

 

drop index vhh_2;

 

 

 

 

 

 

 

 

 

 

drop index vhh_3;

 

 

 

 

 

 

 

 

 

 

create index vhh_0 on vhh(vchdat,apcode,curcde);

 

 

 

 

 

 

 

 

 

 

create index vhh_1 on vhh(apcode,vchdat);

 

 

 

 

 

 

 

 

 

 

create index vhh_2 on vhh(curcde,vchdat);

 

 

 

 

 

 

 

 

 

 

create index vhh_3 on vhh(orgidt,tlrnum,vchset,vchdat);

 

 

 

 

 

 

 

 

 

 

drop index txn_0;

 

 

 

 

 

 

 

 

 

 

create index txn_0 on txn(boknum,txndat);

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

建立預存程序，將遠程庫中的資料插入到本機資料庫中，及一百萬的記錄，差不多需要十分鐘多一點。然後接著使用本地的暫存資料表，執行資料的處理。速度比直接使用DBLINK到遠程庫進行處理提高了很多。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

資料的處理整個過程就由原來的4小時15分鐘，縮短到了1449.995 seconds，也就是25分鐘。

 

 

 

 

 

 

 

 

 

 

在處理遠端資料或因磁碟空間不夠又要處理大量資料的時候，就可以考慮使用暫存資料表。暫存資料表的使用和普通表一樣操作。