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
)
更多推荐
Sql loader使用教程
发布评论