某用户业务日志有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)
DCTM?????????????????????????????????????????????? VARCHAR2(30)
CZY??????????????????????????????????????????????? VARCHAR2(20)
CZRQ?????????????????????????????????????????????? DATE
CZLXDM???????????????????????????????????????????? VARCHAR2(8)
LYB??????????????????????????????????????????????? VARCHAR2(12)
?
控制文件
c.trl
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
? )
? 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
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
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
?