生成百万级的数据文件
连接到本地数据库(自己安装的):
找一个合适大小的表:
1 2 3 4 5 6 7 8 | <code> SELECT COUNT(0) FROM DBA_OBJECTS; COUNT(0) ---------- 65389 |
然后找一个有20行记录的表,对其进行笛卡尔运算,即可生成130万条记录了.
为了更好地体现通用性,我们在输出时对created日期做一下格式转换:
1 2 3 4 | <code> 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 content by rownum<=20) b ; |
示例代码保存为SQL文件:getobject.sql
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | <code> set echo off set term off set line 1000 pages 0 set feedback off set heading off spool [当前路径]/ldr_object.csv @[当前路径]/getobject.sql spool off set heading on set feedback on set term on set echo on |
示例代码保存为:SQL文件:call.sql
然后在SQL*Plus命令行环境中,执行下列命令即可：
SQL> @[当前路径]call.sql
然后就耐心等待把,132万的记录量,输出也是需要一定时间的.
查看:
1 2 3 4 5 | <code> exit du -m ldr_object.csv //输出的数据以M为单位. |
初始化环境
创建表:
1 2 3 4 5 6 7 8 | <code> CREATE TABLE OBJECTS( OWNER VARCHAR2(30), OBJECT_NAME VARCHAR2(50), OBJECT_ID NUMBER, STATUS VARCHAR2(2000), CREATED DATE); |
创建Index.
1 2 | <code> create index idx_obj_owner_name on objects(owner,object_name); |
执行导入
创建控制文件如下:
1 2 3 4 5 6 7 8 9 10 11 12 | <code> LOAD DATA INFILE ldr_object.csv INSERT INTO TABLE OBJECTS FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' (owner, object_name , object_id, created date 'yyyy-mm-dd hh24:mi:ss', status char(2000) ) BEGINDATA |
控制文件保存为:ldr_object.ctl
这里注意,对于CREATED列，我们指定了日期格式，并进行了转换,这个格式一定要与数据文件中日期格式相符,不然日期格式转换时会报错并导致数据加载失败.
按照默认参数执行SQLLDR，看看需要多长时间，同时指定ERRORS参数值为10,明确指定出现10次错误即中止加载:
1 2 | <code> sqlldr SCOTT/TIGER CONTROL=ldr_object.ctl ERRORS=10 |
5.4.4 能不能再快一点呢
SQLLDR常规路径导入时默认一次加载64行,现在要加载的总行数已经达到百万级,十位数显然太小,我们首先尝试修改该值,先直接在后面加个0好了,看看能对效率起到多大的提升:
执行如下命令:sqlldr SCOTT/TIGER CONTROL=ldr_object.ctl ERRORS=10 ROWS=640
日志节选信息:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | <code> value used for ROWS parameter changed from 640 to 84 Table OBJECTS: 1307820 Rows successfully loaded. 0 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null. Space allocated for bind array: 254856 bytes(84 rows) Read buffer bytes: 1048576 Total logical records skipped: 0 Total logical records read: 1307820 Total logical records rejected:0 Total logical records discarded: 0 Run began on Wed Jun 05 14:18:43 2013 Run ended on Wed Jun 05 14:28:56 2013 Elapsed time was: 00:10:12.39 CPU time was:00:00:16.40 |
注意节选信息的第一行，该信息是提示由于640行所占用的空间已经超过了参数BINDSIZE的默认值，因此自动修改到最大可承受的84行，这说明BINDSIZE参数默认值偏小.速度只比刚才小了2秒(变化几乎可以忽略)
再进一步调整BINDSIZE参数值,默认为256k,我们将其修改为10M(1024kb * 1024 * 10 = 10485760),同时将一次加载的行数提高到5000.
1 2 | <code> sqlldr SCOTT/TIGER CONTROL=ldr_object.ctl ERRORS=10 ROWS=5000 BINDSIZE=10485760 |
时间变得更长,正在研究:
大约时间是:13:48
能不能再快一点呢
所有参数默认，只打开直接路径加载:
1 2 | <code> sqlldr SCOTT/TIGER CONTROL=ldr_object.ctl DIRECT=TRUE |
靠,这个速度才是王道啊!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | <code> Table OBJECTS: 1307820 Rows successfully loaded. 0 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null. Date conversion cache disabled due to overflow (default size: 1000) Bind array size not used in direct path. Column array rows : 5000 Stream buffer bytes: 256000 Read buffer bytes: 1048576 Total logical records skipped: 0 Total logical records read: 1307820 Total logical records rejected:0 Total logical records discarded: 0 Total stream buffers loaded by SQL*Loader main thread: 1250 Total stream buffers loaded by SQL*Loader load thread: 4996 Run began on Wed Jun 05 14:55:12 2013 Run ended on Wed Jun 05 14:56:28 2013 Elapsed time was: 00:01:15.74 CPU time was:00:00:11.80 |
有没有可能更快呢
这究竟是希望还是欲望，已经说不清楚了，反正没个尽头。
直接导入路径导入可用的参数也有不少，不过我们这里总数据量不大，因此实际能够起到效率提升的不多，我准备主要从以下两个参数入手：
- STREAMSIZE ： 直接路径加载默认读取全部记录，因此不需要设置ROWS参数,读取到的数据处理后存入流缓存区,即STREAMSIZE参数，该参数默认值为256kb,这里加大到10MB.
- DATE_CACHE : 该参数指定一个转换后日期格式的缓存区，以条为单位，默认值1000条，即保存1000条转换后的日期格式，由于我们要导入的数据中有日期列，因此加载该参数值到5000，以降低日期转换带来的开销.
修改参数后执行命令最终形式如下所示:
1 2 | <code> sqlldr SCOTT/TIGER CONTROL=ldr_object.ctl DIRECT=TRUE STREAMSIZE=10485760 DATE_CACHE=5000 |
可能已经达到性能瓶颈:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | <code> Date cache: Max Size: 5000 Entries : 1207 Hits : 1306613 Misses :0 Bind array size not used in direct path. Column array rows : 5000 Stream buffer bytes:16777216 Read buffer bytes: 1048576 Total logical records skipped: 0 Total logical records read: 1307820 Total logical records rejected:0 Total logical records discarded: 0 Total stream buffers loaded by SQL*Loader main thread: 1250 Total stream buffers loaded by SQL*Loader load thread: 0 Run began on Wed Jun 05 15:15:39 2013 Run ended on Wed Jun 05 15:17:07 2013 Elapsed time was: 00:01:28.14 CPU time was:00:00:09.05 |
SQL*Loader加载综述
事实上想在Oracle存储过程中调用SQLLDR非常麻烦，因为SQLLDR是一个执行程序而不是一个接口,在9i之前版本要在存储过程中实现类似功能也很复杂,虽然可以通过UTL_FILE之类的包间接实现，但需要编写大量脚本,考虑字符截取,过滤,判断等诸多事宜。
一个不慎就可能造成执行报错,或者更不慎,执行到一般的时候报错(可能比没执行还要麻烦),幸运的是,9i及之后的版本,Oracle提供了一个新的功能—外部表（External Tables）,顾名思义就是数据存储在数据库之外的表,这是一个号称”SQL*Loader替代者”的新特性.
2013/06/05 15:25:57
–EOF–