SQLLDR使用-100万条记录的数据加载

1. setp-1 生成数据文件

getobject.sql

SELECT a.owner || ',"' || a.object_name || '",' || a.object_id || ',' ||
to_char(a.created, 'yyyy-mm-dd hh24:mi:ss') || ',' || a.status
FROM dba_objects a,
(SELECT rownum rn FROM dual connect BY rownum <= 23) b;

call.sql

SET echo off
SET term off
SET line 100 pages 0
SET feedback off
SET heading off
spool d:\oracle\script\ldr_object.csv
@d:\oracle\script\getobject.sql
spool off
SET heading on
SET feedback on
SET term on
SET echo on

执行SQL
SQL> @d:\oracle\script\call.sql

2. step 2 初始化环境：

createobject.sql

CREATE TABLE objects (
owner varchar(30),
object_name varchar(50),
object_id NUMBER,
created date,
status VARCHAR2(10)
);
CREATE INDEX idx_obj_owner_name on objects(owner,object_name);

step 3：第一次执行导入

ldr_object.ctl

load data
infile ldr_object.csv
truncate into table objects
fields terminated by "," optionally enclosed by '"'
(
owner,
object_name,
object_id,
created date 'yyyy-mm-dd hh24:mi:ss',
status "substr(:status,1,5)"
)

执行导入
D:\oracle\script>sqlldr scott/tigger control=ldr_object.ctl errors=10

查看日志:
ldr_object.log
经过时间为: 00: 01: 47.00
CPU 时间为: 00: 00: 07.43

step 2: 第二次执行导入

D:\oracle\script>sqlldr scott/tigger control=ldr_object.ctl errors=10 rows=640

查看日志:
ldr_object.log
经过时间为: 00: 01: 25.66
CPU 时间为: 00: 00: 05.75

setp 3: 第三次执行导入

使用direct参数
D:\oracle\script>sqlldr scott/tigger control=ldr_object.ctl errors=10 direct=true

查看日志:
ldr_object.log
经过时间为: 00: 00: 31.95
CPU 时间为: 00: 00: 03.92

setp 4: 执行第四次导入

加大流存储区，加大日期格式缓冲区
D:\oracle\script>sqlldr scott/tigger control=ldr_object.ctl errors=10 direct=true streamsize=10485760 date_cache=5000

查看日志:
ldr_object.log
经过时间为: 00: 00: 15.49
CPU 时间为: 00: 00: 03.26

其他参数比较
1. 调整bindsize参数值默认为256K，修改为10M, 同时将一次加载的行数提高到5000

D:\oracle\script>sqlldr scott/tigger control=ldr_object.ctl errors=10 bindsize=10485760 rows=5000

查看日志:
ldr_object.log
经过时间为: 00: 00: 55.58
CPU 时间为: 00: 00: 05.19

2. 去掉索引
-- Drop indexes
drop index IDX_OBJ_OWNER_NAME;

加大流存储区，加大日期格式缓冲区
D:\oracle\script>sqlldr scott/tigger control=ldr_object.ctl errors=10 direct=true streamsize=10485760 date_cache=5000

查看日志:
ldr_object.log
经过时间为: 00: 00: 25.45
CPU 时间为: 00: 00: 03.34