最近一直在用SQL*LOADER加载数据，用外部表也可以实现，今天想测试下外部表加载数据和SQL*LOADER加载1029027条记录的性能。
首先创建一张外部表。

create table enmo
(name char(10),lname char(10),xname varchar2(50),ydoss varchar2(20),
doss varchar2(50),btime varchar2(50),etime varchar2(50),
num_file number,box_num number,b_enum varchar2(20))
organization external
(type oracle_loader
default directory imp
access parameters
(records delimited by newline
fields terminated by","
(name,lname,xname,ydoss,doss,btime,etime,num_file,box_num,b_enum))
location('doss.csv'))；

表已创建。

查看要加载的数据是否正确

SQL> select * from enmo;
select * from enmo
*
第 1 行出现错误:
ORA-29913: 执行 ODCIEXTTABLEFETCH 调出时出错
ORA-30653: 已达到拒绝限制值

这个错误一般都是外部表的数据和外部表的数据类型或字段长度不符，查看日志发现

处理列 NUM_FILE (行 1, 数据文件 d:impdoss.csv) 时出错
ORA-01722: 无效数字

这是因为doss.csv的第一行是表的字段名字，和NUMBER类型不符，处理方法可以在第9行下面添加skip=1或者直接讲doss.csv的第一行删除即可，这里直接删除doss.csv的第一行。

SQL> select name,lname,xname,ydoss,doss,btime from enmo where rownum<10;

NAME   LNAM XNAME                  YDOSS DOSS             BTIME
------ ---- ---------------------- ----- ---------------- ------------------
内务府 呈稿  掌关防管理内管领事务处      乾关1 05-08-009-000001    乾隆七年九月十八日
内务府 呈稿  掌关防管理内管领事务处      嘉关1 05-08-009-000002    嘉庆元年正月十二日
内务府 呈稿  掌关防管理内管领事务处      嘉关2 05-08-009-000003    嘉庆元年五月初七日
内务府 呈稿  掌关防管理内管领事务处      嘉关3 05-08-009-000004    嘉庆元年七月初二日
内务府 呈稿  掌关防管理内管领事务处      嘉关4 05-08-009-000005    嘉庆元年九月初五日
内务府 呈稿  掌关防管理内管领事务处      嘉关5 05-08-009-000006    嘉庆元年三月十八日
内务府 呈稿  掌关防管理内管领事务处      嘉关6 05-08-009-000007    嘉庆元年二月十九日
内务府 呈稿  掌关防管理内管领事务处      嘉关7 05-08-009-000008    嘉庆元年三月十四日
内务府 呈稿  掌关防管理内管领事务处      嘉关8 05-08-009-000009    嘉庆二年二月初三日

已选择9行。

先测试SQL*LOADER加载需要的时间，首先创建一张和表enmo同构的表。

CREATE TABLE ENMO_01 AS SELECT * FROM ENMO WHERE 1=2;

表已创建。

利用SQL*LOADER加载

C:Documents and SettingsAdministrator>sqlldr stream/stream control=
d:impdoss.ctl log=d:impdoss.log direct=true streamsize=10485760
date_cache=50000
SQL*Loader: Release 11.2.0.1.0 - Production on 星期日 10月 2 22:15:40 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.All rights reserved.
加载完成 - 逻辑记录计数 1029027。

参数介绍：
direct=true打开直接路径加载。
streamsize=10485760直接路径加载默认读取全部记录，之后存到流缓冲区，即streamsize，该参数默认256K，这里加大到10M。
date_cache=50000该参数指定一个转换后日期格式的缓冲区，以条为单位，这里指定5000条。

查看日志，加载用时28.82秒

跳过的逻辑记录总数:          0
读取的逻辑记录总数:       1029027
拒绝的逻辑记录总数:          0
废弃的逻辑记录总数:        0
由 SQL*Loader 主线程加载的流缓冲区总数:      227
由 SQL*Loader 加载线程加载的流缓冲区总数:        0
从 星期日 10月 02 22:15:40 2011 开始运行
在 星期日 10月 02 22:16:08 2011 处运行结束
经过时间为: 00: 00: 28.82
CPU 时间为: 00: 00: 04.75

测试外部表加载需要的时间。

SQL> select count(*) from enmo;
COUNT(*)
----------
   1029027
SQL> truncate table enmo_01;
表被截断。
SQL> set timing on
SQL> INSERT /*+APPEND*/ INTO ENMO_01 SELECT * FROM ENMO;
已创建1029027行。已用时间:  00: 00: 23.82
SQL> set timing on
SQL> INSERT /*+APPEND*/ INTO ENMO_01 SELECT * FROM ENMO;
已创建1029027行。
已用时间:  00: 00: 23.82

SQL*LOADER加载需要28.82秒，外部表只需要23.82秒。
/*+APPEND*/代表SQL级的直接路径加载，这种方式不写redo，可以减少磁盘I/O。同样都是直径路径，显然利用外部表加载的时间要小于SQL*LOADER加载的时间。
ORACLE公司的工程师黄凯耀曾在EXADATA上利用外部表加载1T的数据，只用可10分钟，当然，他还用到了压缩技术。
我认为用外部表加载更方便，起码在加载前可以非常只管的检查要加载的数据是否正确。

本文固定链接: http://www.dbdream.com.cn/2011/10/sqlloader%e4%ba%8e%e5%a4%96%e9%83%a8%e8%a1%a8%e5%8a%a0%e8%bd%bd/ | 信春哥，系统稳，闭眼上线不回滚！