一次使用临时表优化数据处理的过程

一次使用临时表优化数据处理的过程

 

写了一个数据处理程序，从远程的一数据库中，将符合要求的数据过滤后插入到本地数据库中。数据涉及两张表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分钟。

在处理远程数据或因磁盘空间不够又要处理大量数据的时候，就可以考虑使用临时表。临时表的使用和普通表一样操作。

Author:丁亚军

Date: 2005-10-30