ChinaRay的收藏和随笔

记录我所看到的有用或有趣的文章,分享我在开发中的心得体会

导航

使用SQL Loader导入大量数据,避免使用SQL频繁写库

Posted on 2008-04-18 21:33  ChinaRay  阅读(17208)  评论(5编辑  收藏  举报
      目前所做的项目需要从一个按行记录的数据文件取出部分字段,再将这些字段的按行存储到Oracle数据库。这些数据大约有2 700 000条,原始数据文件有450M左右。至少有两种方法可以实现:

     一.将原始数据文件读进内存流中,每读一行解析一条数据,接着执行一条SQL语句,将解析到的数据插入数据库表;

     二..将原始数据文件读进内存流中,每读一行解析一条数据,接着将解析到的数据按行写入一个临时文件data.tmp,待所有数据均被解析完毕且写入临时文件后,调用SQL Loader,将所有数据一次性从临时文件导入数据库表。

     一开始是按照第一种方法编程的。在本机(Oracle服务器装在本机)测试顺利通过,但是花了大约150分钟才导入解析和完成;当部署到实际环境中运行时,却出现了问题。跟踪发现是因为网络不稳定(程序所在服务器与数据库服务器处于不同的局域网,由防火墙隔开,做了几个特定端口的路由),有时候连接不到数据库,往往只导入了很少的几千条甚至几百条数据时,数据库就连接不上了。

     后来分析,对于大量数据的导入,采用第一种方法会导致频繁写库,每次都要打开数据库(禁用了连接池,后续文章会提到禁用的原因),对数据库造成较大的压力,加上网络环境本来就不够稳定,所以很容易出现问题。于是改用第二种方法。

     使用第二种方法后,解析花的时间大约为90分钟,导入则只花了5分钟左右。看来SQL Loader的效率真的很高。

     下面讲讲SQL Loader的基本用法。
    
     1、在Oracle中按照导入数据的格式建立一个空表

     2、编写一个控制文件control.ctl,内容如下

LOAD DATA 
INFILE 
'E:\test\data.tmp' 
BADFILE 
'E:\test\data.bad'
DISCARDFILE 
'E:\test\data.dsc'
DISCARDMAX 
1000

APPEND
INTO TABLE "TB_TEST"
FIELDS TERMINATED 
BY '|'
TRAILING NULLCOLS
  (
   
USER_ID
   
USER_NAME
   REG_TIME  DATE(20) "YYYY-MM-DD HH24:MI:SS"
   )

 操作分类:

        a、insert,为缺省方式,在数据装载开始时要求表为空
        b、append,在表中追加新记录
        c、replace,删除旧记录,替换成新装载的记录
        d、truncate,同上

3、在命令行中执行以下语句:

        sqlldr userid/password@database e:\test\control.ctl

       关于如何在程序中使用SQL Loader,在后续文章中将做相关介绍。