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

)

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐