sql load 简单案例

某用户业务日志有400多万条,需要迁移到我们系统中,由于时间要求紧张,决定先用程序生成符合格式的数据文件,再用oralce 的sqlload 装入数据到数据库中.试验结果300M(400万条) 文本文件导入到oracle 中,经生成数据文件耗时2分钟,sqlload 装入耗时4分钟.效果非常好.

 

表结构

RZB

 

DZTM                                               VARCHAR2(30)
DCTM                                               VARCHAR2(30)
CZY                                                VARCHAR2(20)
CZRQ                                               DATE
CZLXDM                                             VARCHAR2(8)
LYB                                                VARCHAR2(12)

 

控制文件
c.trl

 

LOAD   DATA  
  INFILE   'ss.txt'  
  APPEND INTO   TABLE   RZB  
  FIELDS   TERMINATED   BY   ','
  (
  DZTM   CHAR,  
  DCTM   CHAR,  
  CZRQ   DATE   "YYYY-MM-DD   HH24:MI:SS",
  CZLXDM   CHAR,  
  LYB   CHAR,  
  CZY   CHAR
  )

 

测试数据文件
ss.txt

 

01412401,A0019889,2000-07-10 00:00:00,WJ,gkdcb,yl1
01412401,A0019889,2000-07-12 00:00:00,HH,gkdcb,yl1
01412401,A0019889,2000-07-10 00:00:00,WJ,gkdcb,yl1
01412401,A0019889,2000-07-12 00:00:00,HH,gkdcb,yl1
T0000065,A0108899,2000-07-12 00:00:00,WJ,gkdcb,yl1
T0000065,A0108899,2000-08-12 00:00:00,HH,gkdcb,yl1
01412439,A0107636,2000-07-12 00:00:00,WJ,gkdcb,yl1
01412439,A0107636,2000-07-12 00:00:00,HH,gkdcb,yl1
98115338,A0108899,2000-07-13 00:00:00,WJ,gkdcb,yl1
98115338,A0108899,2000-07-13 00:00:00,HH,gkdcb,yl1
t0000063,A0044145,2000-09-07 00:00:00,WJ,gkdcb,yl1
t0000063,A0044145,2000-10-08 00:00:00,HH,gkdcb,yl1
s0006431,A0044145,2000-09-08 00:00:00,WJ,gkdcb,yl1
s0006431,A0044145,2000-09-10 00:00:00,HH,gkdcb,yl1

 

批处理文件

b.bat

sqlldr userid=test/test@glis control=t.ctl direct=true