ORACLE 快速批量导入文本数据到数据库(sqlldr工具)方法与分析

在实际生产环境中,常会碰到将一些如通过通讯接口传过来的数据(文本文件 txt)导入到数据库的某张表中。通常做法是使用ORACLE自带的包功能打开文件,将文件中的数据用LOOP循环一行行读入内存数组中,之后再使用LOOP循环将内存数组写入表中。

例如将一个文本文件56万+行数据导入数据库表:

create or replace procedure bob_tmp_700 is
    filehandle utl_file.file_type;
    filebuffer varchar2(4000);
    fileName   varchar2(50);
    --记时用
    v_duration interval day(1) to second(0);
    v_start timestamp(2);

    type t_table is table of tmp_bobtest%rowtype index by binary_integer;
    v_tmpdata t_table;
    --行分隔
    v_tab_split TAB_SPLIT;
    v_loopcount   number(10);

  begin
    v_loopcount:=0;
     --拼接文件名
    fileName := 'WM_CHK' || to_char(sysdate, 'yyyyMMdd') || '.txt';
    --打开文件
    filehandle := utl_file.fopen('FILETRANS', fileName, 'r');    

    v_start:=systimestamp;
    dbms_output.put_line('开始打开文件并将数据导入内存  time: '|| v_start );

    loop
      v_loopcount:=v_loopcount+1;
      begin
        utl_file.get_line(filehandle, filebuffer);
       --分隔行数据
        v_tab_split := TOOLS.fn_split(filebuffer, '|');
        --将一行数据给数组赋值
        v_tmpdata(v_loopcount).pcmiid       := v_tab_split(1);
        v_tmpdata(v_loopcount).pcqfje       := v_tab_split(2);
        v_tmpdata(v_loopcount).pcycje       := v_tab_split(3);
        v_tmpdata(v_loopcount).updatedate   := to_date(v_tab_split(4), 'yyyy-MM-dd');
        v_tmpdata(v_loopcount).sendate      :=  to_date(v_tab_split(5), 'yyyy-MM-dd');
        v_tmpdata(v_loopcount).insertdate   := sysdate;
         v_tmpdata(v_loopcount).pcmetertype := '1';

      exception
        when no_data_found then
          exit;
      end;
    end loop;

    v_duration:=systimestamp-v_start;
   dbms_output.put_line('数据导入内存完毕  time: '|| v_start ||'    用时: '||v_duration);
    --关闭文件
    utl_file.fclose(filehandle);

       v_start:=systimestamp;
        dbms_output.put_line('开始将内存数据插入表  time: '|| v_start );
    --先删除原数据
    execute immediate 'truncate table tmp_bobtest'; 
    commit;
   --插入数据表
    for i in 1..v_tmpdata.count loop
      insert into tmp_bobtest values  (v_tmpdata(i).pcmiid,v_tmpdata(i).pcqfje,v_tmpdata(i).pcycje,v_tmpdata(i).updatedate,v_tmpdata(i).sendate,v_tmpdata(i).insertdate,v_tmpdata(i).pcmetertype);
      end loop;
    commit;
v_duration:=systimestamp-v_start;
dbms_output.put_line('内存数据插入表完毕  time: '|| v_start ||'    用时: '||v_duration);

  end bob_tmp_700;

DBMS输出:

开始打开文件并将数据导入内存  time: 2017-03-29 19:33:35:050000
数据导入内存完毕  time: 2017-03-29 19:33:35:050000    用时: +0 00:08:03
开始将内存数据插入表  time: 2017-03-29 19:41:37:630000
内存数据插入表完毕  time: 2017-03-29 19:41:37:630000    用时: +0 00:00:19



如上面例子中我们可以看到:一共从文件中导入56万+条数据到表中,将文件中数据读取到内存数组中就花去了8分多钟,而将内存数组中的数据写到表中只用了二十多秒时间。因此使用 utl_file.get_line 方法循环读取文本文件内容非常地耗时(大概每秒1100+行),这种方法对付小批量数据问题不大,一但数据超过几十万,上百万,甚至上千万条时就非常地慢。


如果我们使用sqlldr工具则可以从文本文件里高速导入,一共只要21秒左右(大概每秒2.5万+行),翻了二十多倍。但这个工具无法在PLSQL中使用,必须登录LINUX以su - oracle 身份运行。

具体是:你要创建一个控制文件,控制文件用来指示导入文本文件数据的规则

控制文件:/home/bob/sql7002.ctl
内容:

----------------------------------------------
Load data
Infile '/home/filetrans/bobWM_CHK20170329.txt'
Append into table tmp_bobtest
fields terminated by '|'
Trailing nullcols
(
pcmiid,
pcqfje,
pcycje,
updatedate "TO_DATE(:updatedate,'yyyy-mm-dd HH24:MI:SS')",
sendate "TO_DATE(:sendate,'yyyy-mm-dd HH24:MI:SS')",
insertdate "sysdate",
pcmetertype "1"
)

----------------------------------------------

说明:updatedate,sendate ,insertdate 为date类型。函数必须用双引号括起来,字段前要加冒号。


再执行命令:

[oracle@newmete ~]$ sqlldr userid=SZJT/SZJT@orcl control=/home/bob/sql7002.ctl log=input.log;


注意:

当加载大量数据时(大约超过10GB),最好抑制日志的产生:
SQL>ALTER TABLE RESULTXT nologging;

无论哪种方式导入,都建议先关闭索引和相关约束与触发器。


Infile 指定文件名

Append into table tmp_bobtest 以追加新记录方法插入到表中
insert,为缺省方式,在数据装载开始时要求表为空
append,在表中追加新记录
replace,删除旧记录,替换成新装载的记录
truncate,直接删除旧记录

fields terminated by '|'  字段的结束符

Trailing nullcols 允许字段数据为空NULL


在 Dos 窗口下使用 SQl*Loader 命令实现数据的导入
C:/>sqlldr userid=system/manager@ serviceName control=input.ctl
默认日志文件名为:input.log
默认坏记录文件为:input.bad



如果要指定每个字段列  好像只能为char 不能是其它类型
(resultid char terminated by ',',
website char terminated by ',',
ipport char terminated by ',',
status char(500) terminated by whitespace)

发布了87 篇原创文章 · 获赞 51 · 访问量 130万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览