某用户业务日志有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 |
|