Sql loader的使用分享

sql loader的使用分享

1.   sqlldr概述

sqlldr 为一种大量数据加载工作，在cmd命令行模式下实现，调用语句如下：

sqlldr userid/password @servicename control=ctlfilename。

满足前提是存储数据的oracle表是已经存在的。

2.    ctl文件内容

options ( {[skip=integer] [ load = integer ] [errors = integer] [rows=integer][bindsize=integer] [silent=(all|feedback|error|discard) ] } )

load[data]

[

  { infile | inddn } {file | * }

  [stream | record | fixed length [blocksizesize]|variable [length] ]

  [ { badfile | baddn } file ]

  {discards | discardmax} integr

]

[ {inddn |infile} . . . ]

[ append |replace | insert ]

[reclentinteger]

[ {concatenate integer | continueif { [this | next] (start[: end])last } operator{ 'string' | x 'hex' } } ]

into table[user.]table

[append |replace | insert]

[whencondition [and condition]...]

[fields[delimiter] ]

(

column {

  recnum | constant value | sequence

  ( { integer | max |count} [, increment] ) |[position ( { start [end] | * [ + integer] }) ]

    datatype

    [terminated [ by ] {whitespace| [x]'character' } ]

    [ [optionally] enclose[by] [x]'charcter']

  [nullif condition ]

  [defaultif condotion]

  }

  [ ,...]

  )

[intotable...]

[begindata]

line1：

option({[skip==integer] [ load = integer] [ errors = integer] [ rows = integer][ bindsize = integer][silent = (all|feedback|error|discard)]})

其中skip，skip = 1 表示用来跳过数据中的第一行；load = 200000表示不导入所有的数据，只导入跳过skip参数后的200000条数据；errors = 100 表示出错100次后，停止加载；rows=1000表示一次加载的行数，默认值为64；bindsize=33554421，表示每次提交记录缓冲区的大小，默认为256k。

line2:

load data

line3:

infile [*]‘datapath’

  [ { badfile | baddn } file ]

  {discards | discardmax} integer ]

其中*是在ctl文件中没有包含数据文件时使用，若有数据文件的时候最好使用绝对路径并且需要带上单引号,badfile内为抛出的坏文件名，integer为文件的记录大小，discards为被抛弃的文件名。

line4：

insert/append/truncate/replace

insert为默认插入数据的方式，只有在表为空表的时候才可进行数据导入

append 为在表中数据的末端将数据导入

replace：（用 delete from table 语句），替换成新装载的记录。是数据操作语句（dml），这个操作会放到 rollbacksegement 中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。

truncate：删除旧记录(用 truncate table 语句)，替换成新装载的记录是定义语言(ddl)，操作立即生效，原数据不放到 rollbacksegment 中，不能回滚，操作不触发 trigger。。

line5：

into table table_name

表示将数据插入某一个表中

line6：

field terminatedby”,”optionally enclosed by “”

表示数据以，（逗号）划分，以“”（空格）换行

line7：

when condition

比如when id = id _ demo为提取id 为id_demo的过滤条件

line8：

(

（1）virtual column filter   表示过率没有名字的第一行

           filler：控制文件中指定 filler，表示该列值不导入表中

（2）position(m:n)：指从第 m 个字符开始截止到第 n 个字符作为列值

　       position(*+2:15)：直接指定数值的方式叫做绝对偏移量，如果使用*号，则为相对偏移量，表示上一个字段哪里结束，这次就哪里开始，相对便宜量也可以再做运算。

　       position(*) char(9)：这种相对偏移量+类型和长度的优势在于，你只需要为第一列指定开始位置，其他列只需要指定列长度就可以。

（3）若导入的列比表中的列要少，需要在末尾加上comm”0”

（4）column + date ‘yyyy-mm-dd’表示将数据转换为日期类型

（5）

)

line9:

若要导入不同表，即在表的末尾加上into table anothertablename，再加上限制条件以及column相关的内容

line10：

begindate

+输入的数据

3.    sql*loader 的性能与并发操作

（1）、rows 的默认值为 64，你可以根据实际指定更合适的 rows 参数来指定每次提交记录数。

（2）、常规导入可以通过使用 insert语句来导入数据。direct导入可以跳过数据库的相关逻辑(direct=true)，而直接将数据导入到数据文件中，可以提高导入数据的性能。当然，在很多情况下，不能使用此参数(如果主键重复的话会使索引的状态变成unusable!)。

（3）、通过指定 unrecoverable选项，可以关闭数据库的日志(是否要 alter table table_namenologging?)。这个选项只能和 direct 一起使用。

（4）、对于超大数据文件的导入就要用并发操作了，即同时运行多个导入任务.

sqlldr   userid=/  control=result1.ctl  direct=true   parallel=true

sqlldr   userid=/  control=result2.ctl  direct=true   parallel=true

sqlldr   userid=/  control=result2.ctl  direct=true   parallel=true

当加载大量数据时（大约超过10gb），最好抑制日志的产生：  

alter tabletable_name nologging;

这样不产生redo log，可以提高效率。然后在 control文件中load data上面加一行unrecoverable，此选项必须要与direct共同应用。  

在并发操作时，oracle声称可以达到每小时处理100gb数据的能力！其实，估计能到 1－10g 就算不错了，开始可用结构相同的文件，但只有少量数据，成功后开始加载大量数据，这样可以避免时间的浪费。

4.    实例

load data

infile 'c:\users\shen_potato\desktop\emps.csv'

into table emp10

fields terminated by ","

(

virtual_column filler,

empno "seq_eseq.nextval",

ename,

job,

mgr,

hiredate date 'yyyy-mm-dd',

sal,

comm,

deptno

)