sqlldr理论深入学习
来源：互联网 发布：手游推荐软件 编辑：程序博客网 时间：2024/05/21 14:47
传统路径（conventional path）:SQLLDR 会利用SQL插入为我们加载数据。
直接路径（direct path）:采用这种模式，SQLLDR不使用SQL；而是直接格式化数据库块，而绕过整个SQL引擎和UNDO生成，同时还可能避开REDO生成。要在一个没有任何数据的库中充分加载数据，最快的方法就是采用并行直接路径加载。
常规路径装载使用SQL INSERT语句和内存中的键数组缓存（bond array buffers）将数据装载到Oracle数据库的表中。这个过程与其他进程竞争SGA内部的内存资源。如果数据库已经有支持多个并发处理进程的开销，常规路径装载会降低装载的性能。
使用常规路径装载的另外一个开销是装载进程必须搜索数据库，以查找被装载表的部分填充块，并试图填充这些块。这对日常的事务处理是非常有效的，但是它是常规路径装载的一个额外开销。
最好或有时必须使用常规路径装载方法，而不能使用直接路径装载的情形：
1、如果被装载的表是被索引的并且被并发访问的，或者如果要对表进行插入或删除，必须使用常规路径装载。
2、当在控制文件中使用SQL函数时，必须使用常规路径装载。当使用直接路径装载时，SQL函数将不适用。
3、当装载的表是一个簇表时。
4、当装载少量记录到一个大型索引表，或当表具有引用完整性或检查约束时。
5、当装载工作是通过SQL * Net或Net8在不同的平台上进行时，为使用直接路径装载，两个节点必须属于同一个计算机家族并且使用同样的字符集。
不需要使用SQL INSERT语句和键数组缓存，直接路径装载格式化输入数据到Oracle数据块并将它们直接写入数据库中。注意直接路径装载总是在表的最高水位之上插入数据，这种方式消除了用于搜索部分填充块的时间。
SQLLDR是一个命令工具，并非一个API，不能从PL/SQL调用。
SQL*Loader具有很多功能，包括以下能力：
可以从不同文件类型的多个输入数据文件中加载数据；
输入记录可以是定长的或变长的记录；
可以在同一次运行中加载多个表，还可以逻辑地将选定的记录载入到每个表中；
在输入数据载入表之前，可以对其使用SQL函数；
多个物理记录可以被编译成一个逻辑记录，同样，SQL可以提取一条物理记录并把它作为多个逻辑记录加载；
支持嵌套、嵌套表、VARRAYS和LOBS（包括BLOGCLOBNLOBBFILE）。
SQL*Loader 组件：
0.控制文件
控制文件中包含描述输入数据的信息（如输入数据的布局、数据类型等），另外还包含有关目标表的信息，控制文件甚至还可以包含要加载的数据。
1. SQL*Loader输入数据：
SQL *Loader能够接收多种不同格式的数据文件。文件可以存储在磁盘或磁带上，或记录本身可以被嵌套到控制文件中。记录格式可以是定长的或变长的，定长记录是指这样的记录：每条记录具有相同的固定长度，并且每条记录中的数据域也具有相同的固定长度、数据类型和位置
2.SQL*Loader输出：
（1）LOAD DATA
（2）INFILE *
（3）INTO TABLE DEPT
（4）FIELDS TERMINATED BY ‘,’
（5）(DEPTNO,DNAME,LOC)
（6）BEGINDATA
（7）10,Sales,Virginia
（8）20,Accounting,Virginia
（9）30,Consulting,Virginia
（10）40,Finance,Virginia
LOAD DATA(1):这会告诉SQLLDR要做什么（在这个例子中，则指示要加载数据）。SQLLDR还可以执行CONTINUE_LOAD,也就是继续加载。只有在继续一个多表直接路径加载时才能使用后面这个选项。
INFILE * (2):这会告诉SQLLDR所要加载的数据实际上包含在控制文件中，如第6-10行所示。也可以指定包含数据的另一个文件的文件名。如果愿意可以使用一个命令行参数覆盖这个INFILE语句。［命令行选项会覆盖控制文件设置］。
INTO TABLE DEPT(3)：这告诉SQLLDR要把数据加载到哪个表中。
FILEDS TERMINATED BY ‘,’(4)：告诉SQLLDR数据的形式应该是用逗号分隔的值。
（DEPTNO,DNAME,LOC）(5)：告诉SQLLDR所要加载的列，这些列在输入数据中的顺序以及数据类型。这是指输入流中数据的数据类型，而不是数据库中的数据类型，在这个例子中，列的数据类型默认为CHAR(255)。
BEGINDATA(6)：告诉SQLLDR你已经完成对输入数据的描述，后面的行（第7-10行）是要加载到DEPT表的具体数据。
要使用以上的控制文件，建立一个空的DEPT表：
CREATE TABLE DEPT
(DEPTNO NUMBER(2) CONSTRAINT DEPT_PK PRIMARY KEY,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
);
并运行以下命令：
Sqlldr userid=/ control=demo1.ctl
表装载的方法：
INSERT 这是缺省方法。该方法假设在数据装载前表是空的，如果在表中有记录，SQLLDR退出，并报：SQLLDR-601: FOR INSERT OPTION,TABLE MUST BE EMPTY,ERROR ON TABLE DEPT
APPEND这种方法允许记录被添加到数据库表中，而且不影响已经存在的记录
REPLACE 这种方法首先删除表中已经存在的记录，然后开始装载新的记录。注意，当老记录被删除时，表上的任意删除触发器将被触发
TRUNCATE 这种方法在装载数据前，使用SQL命令TRUNCATE 删除老的记录，因为去除了触发器的触发并且没有创建回滚，所以这种方法要比REPLACE快得多。为了使用这种方法约束必须被禁止，并且要授予特定的权限
如何加载定界数据？
定界数据，（delimited data）即用某个特殊字符分隔的数据。
Example:
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”’
上面例子指定用逗号分隔数据字段，每个字段可以用双引号括起。
TERMINATED BY X’9’(使用16进制格式的制表符；采用ASCII时，制表符为9)
TERMINATED BY WHITESPACE
如何加载固定格式数据？
通常会有一个由某个外部系统生成的平面文件，而且这是一个定长文件，其中包含着固定位置的数据（POSITIONAL DATA）.要加载定宽的固定位置数据，将会在控制文件中使用POSITION关键字：
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
(DEPTNO position(1:2),
DNAME position (3:16),
LOC position (17:29)
)
BEGINDATA
10ACCOUNTIN Virginia ,USA
可以使用相对偏移量。
DEPTNO position(1:2),
DNAME position(*:16)
表示DNAME 从3-16个字符。
如何加载日期？
只需要控制文件中DATE数据类型，并指定要使用的日期掩码。这个日期掩码与数据库中的TO_CHAR和TO_DATE中使用的日期掩码是一样。
如何使用函数加载数据？
只需要将函数加到控制文件中
（DNAME “UPPER(:dname)”
）
TRAILING NULLCOLS 会导致绑定变量成为NULL,如果输入记录中不存在某一列的数据，SQLLDR会为该列绑定一个NULL值。
下面是增加SQL*Loader性能的一些补充技巧：
1）使用定位域而不要使用分隔域，分隔域要求装载器搜索数据以查找分隔符。定位域比较快，因为装载器只需要做简单的指针运算。
2）为终止域指定最大长度，使每个捆绑数组更为有效地插入。
3）预分配足够的存储空间。当数据被装载时，表中需要更多的空间， Oracle分配更多的区间以容纳数据，如果在数据装载期间频繁地做这项操作，处理的开销将非常大。在装载之前计算或估算存储空间需求能够让你预先创建必要的存储空间。
4）如果可能，在控制文件中尽量避免使用NULLIF和DEFAULTIF子句。这两个子句对于被装载的每条记录都会引起列运算。
5）分割数据文件，并行运行常规路径装载。
6）通过使用命令行参数ROWS，减少提交次数。
7）避免不必要的字符集转换，确保客户端的NLS_LANG环境与服务器端的相同。
8）只要可能，尽量使用直接路径装载方法。
9）当使用直接路径装载方法时，为表的最大索引预先排序并使用SORTED INDEXES子句。
10）当使用直接路径装载方法时，尽量使用并行直接路径选项。
11）在直接路径装载期间，尽可能少使用重做日志。有三种不同级别的控制实现这点：
禁止数据库归档；
在控制文件中使用关键字UNRECOVERABLE；
使用NOLOG属性修改表和/或索引。
直接路径并行加载的格式样例：
/opt/app/oracle/product/10.2.0/bin/sqlldr APS/APS control=/home/oracle/APS_LOAD/ctl/AP_CONTRACT.CTLdirect=true parallel=true LOG=/home/oracle/APS_LOAD/log/$yesterday/AP_CONTRACT_$yesterday.log bad=/home/oracle/APS_LOAD/bad/DUE_BILL_$yesterday.bad rows=10000 readsize=20000000 bindsize=20000000 DISCARD=/home/oracle/APS_LOAD/bad/discard_ts.dis
工具具体使用方法:
1、控制文件中注释用“--”
2、为防止导入出现中文乱码，在控制文件中加入字符集控制
LOAD DATA
CHARACTERSET ZHS16GBK
3、让某一列成为行号，用RECNUM关键字
- load data
- infile *
- into table t
- replace
- ( seqno RECNUM //载入每行的行号
- text Position(1:1024))
- BEGINDATA
- fsdfasj
4、过滤某一列，用FILLER关键字
- LOAD DATA
- TRUNCATE INTO TABLE T1
- FIELDS TERMINATED BY ','
- ( field1,
- field2 FILLER,
- field3
- )
5、过滤行
在INTO TABLE table_name后加WHEN过滤条件，但功能有限，如果以竖线分隔符的文件，不能实现字段级的过滤，定长的还好。
- LOAD DATA
- INFILE 'mydata.dat'
- BADFILE 'mydata.bad'
- DISCARDFILE 'mydata.dis'
- APPEND
- INTO TABLE my_selective_table
- WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '20031217'
- (
- region CONSTANT '31',
- service_key POSITION(01:11) INTEGER EXTERNAL,
- call_b_no POSITION(12:29) CHAR
- )
6、过滤首行，用OPTIONS (SKIP 1)选项
也可以写在命令行中，如：
- sqlldr sms/admin control=test.ctl skip=1
7、TRAILING NULLCOLS的使用，作用是表的字段没有对应的值时允许为空
如：
- LOAD DATA
- INFILE *
- INTO TABLE DEPT
- REPLACE
- FIELDS TERMINATED BY ','
- TRAILING NULLCOLS // 其实下面的ENTIRE_LINE在BEGINDATA后面的数据中是没有直接对应的列的值的如果第一行改为 10,Sales,Virginia,1/5/2000,, 就不用TRAILING NULLCOLS了
- (DEPTNO,
- DNAME "upper(:dname)", // 使用函数
- LOC "upper(:loc)",
- LAST_UPDATED date 'dd/mm/yyyy', // 日期的一种表达方式 还有'dd-mon-yyyy' 等
- ENTIRE_LINE ":deptno||:dname||:loc||:last_updated"
- )
- BEGINDATA
- 10,Sales,Virginia,1/5/2000
- 20,Accounting,Virginia,21/6/1999
- 30,Consulting,Virginia,5/1/2000
- 40,Finance,Virginia,15/3/2001
8、添加、修改数据
- （1）、
- LOAD DATA
- INFILE *
- INTO TABLE tmp_test
- ( rec_no "my_db_sequence.nextval",
- region CONSTANT '31',
- time_loaded "to_char(SYSDATE, 'HH24:MI')",
- data1 POSITION(1:5) ":data1/100",
- data2 POSITION(6:15) "upper(:data2)",
- data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')"
- )
- BEGINDATA
- 11111AAAAAAAAAA991201
- 22222BBBBBBBBBB990112
- （2）、
- LOAD DATA
- INFILE 'mail_orders.txt'
- BADFILE 'bad_orders.txt'
- APPEND
- INTO TABLE mailing_list
- FIELDS TERMINATED BY ","
- ( addr,
- city,
- state,
- zipcode,
- mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)",
- mailing_city "decode(:mailing_city, null, :city, :mailing_city)",
- mailing_state
- )
9、合并多行记录为一行记录
通过关键字concatenate 把几行的记录看成一行记录
- LOAD DATA
- INFILE *
- concatenate 3 // 通过关键字concatenate 把几行的记录看成一行记录
- INTO TABLE DEPT
- replace
- FIELDS TERMINATED BY ','
- (DEPTNO,
- DNAME "upper(:dname)",
- LOC "upper(:loc)",
- LAST_UPDATED date 'dd/mm/yyyy'
- )
- BEGINDATA
- 10,Sales, // 其实这3行看成一行 10,Sales,Virginia,1/5/2000
- Virginia,
- 1/5/2000
10、用”|+|”分隔符，避免数据混淆
fields terminated by "|+|"
11、如果数据文件包含在控制文件中，用INFILE *
如下：
- LOAD DATA
- INFILE *
- append
- INTO TABLE tmp_test
- FIELDS TERMINATED BY ","
- OPTIONALLY ENCLOSED BY '"'
- TRAILING NULLCOLS
- ( data1,
- data2
- )
- BEGINDATA
- 11111,AAAAAAAAAA
- 22222,"A,B,C,D,"
12、一次导入多个文件到同一个表
- LOAD DATA
- INFILE file1.dat
- INFILE file2.dat
- INFILE file3.dat
- APPEND
- INTO TABLE emp
- ( empno POSITION(1:4) INTEGER EXTERNAL,
- ename POSITION(6:15) CHAR,
- deptno POSITION(17:18) CHAR,
- mgr POSITION(20:23) INTEGER EXTERNAL
- )
13、将一个文件导入到不同的表
- （1）、
- LOAD DATA
- INFILE *
- INTO TABLE tab1 WHEN tab = 'tab1'
- ( tab FILLER CHAR(4),
- col1 INTEGER
- )
- INTO TABLE tab2 WHEN tab = 'tab2'
- ( tab FILLER POSITION(1:4),
- col1 INTEGER
- )
- BEGINDATA
- tab1|1
- tab1|2
- tab2|2
- tab3|3
- ==============
- （2）、
- LOAD DATA
- INFILE 'mydata.dat'
- REPLACE
- INTO TABLE emp
- WHEN empno != ' '
- ( empno POSITION(1:4) INTEGER EXTERNAL,
- ename POSITION(6:15) CHAR,
- deptno POSITION(17:18) CHAR,
- mgr POSITION(20:23) INTEGER EXTERNAL
- )
- INTO TABLE proj
- WHEN projno != ' '
- ( projno POSITION(25:27) INTEGER EXTERNAL,
- empno POSITION(1:4) INTEGER EXTERNAL
- )
14、过滤掉的数据文件路径指定
- /opt/app/oracle/product/10.2.0/bin/sqlldr APS/APS control=/home/oracle/APS_LOAD/ctl/AP_CONTRACT.CTL LOG=/home/oracle/APS_LOAD/log/$yesterday/AP_CONTRACT_$yesterday.log bad=/home/oracle/APS_LOAD/bad/DUE_BILL_$yesterday.bad rows=10000 readsize=20000000 bindsize=20000000 DISCARD=/home/oracle/APS_LOAD/bad/discard_ts.dis
15、附：测试用控制文件
- LOAD DATA
- INFILE '/home/oracle/APS_LOAD/dat/APS_AP_CONTRACT.dat'
- TRUNCATE
- INTO TABLE AP_CONTRACT
- WHEN (01)<>'1'
- FIELDS TERMINATED BY "|"
- TRAILING NULLCOLS
- (
- AGMT_NO "(TRIM(:AGMT_NO ))",
- CONTRACT_NO FILLER, -- "(TRIM(:CONTRACT_NO ))",
- LOAN_AMT "(TRIM(:LOAN_AMT ))",
- AGMT_HOLDER "(TRIM(:AGMT_HOLDER ))",
- LOAN_TYPE_CD "(TRIM(:LOAN_TYPE_CD ))",
- CURR_CD "(TRIM(:CURR_CD ))",
- BALANCE "(TRIM(:BALANCE ))",
- LOAN_DIRC_CD "(TRIM(:LOAN_DIRC_CD ))",
- AGMT_START_DATE "(TRIM(:AGMT_START_DATE ))",
- AGMT_END_DATE "(TRIM(:AGMT_END_DATE ))",
- AGMT_BELONG_ORG_NO "(TRIM(:AGMT_BELONG_ORG_NO ))",
- MANAGER_NO "(TRIM(:MANAGER_NO ))",
- PROCESS_RATE "(TRIM(:PROCESS_RATE ))",
- INSURE_METH_TYPE_CD "(TRIM(:INSURE_METH_TYPE_CD ))",
- AGMT_SIGN_DATE "(TRIM(:AGMT_SIGN_DATE ))",
- LOAN_PROP_CD "(TRIM(:LOAN_PROP_CD ))",
- LOAN_USE_TYPE "(TRIM(:LOAN_USE_TYPE ))",
- ENTRUST_LOAN_FLAG "(TRIM(:ENTRUST_LOAN_FLAG ))",
- ENTRUST_NAME "(TRIM(:ENTRUST_NAME ))",
- FARM_LOAN_FLAG "(TRIM(:FARM_LOAN_FLAG ))",
- FARM_LOAN_TYPE_CD "(TRIM(:FARM_LOAN_TYPE_CD ))",
- LOAN_BIZ_TYPE_CD "(TRIM(:LOAN_BIZ_TYPE_CD ))",
- ID_TEST RECNUM ,
- CHAR_TEST CONSTANT '31',
- SQ "sqlldr.nextval",
- TEST_4 "TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI:SS')",
- TEST_5 "(TRIM(:LOAN_BIZ_TYPE_CD)||'---'||TRIM(:AGMT_NO))"
- )
- sqlldr理论深入学习
- sqlldr理论深入学习。
- sqlldr理论深入学习
- sqlldr理论深入学习
- sqlldr理论深入学习
- sqlldr理论深入学习
- zz:sqlldr理论深入学习
- zz:sqlldr理论深入学习
- 【理论】深入学习spring boot
- sqlldr学习
- sqlldr
- sqlldr
- sqlldr
- sqlldr
- sqlldr
- sqlldr
- SQLLDR
- SQLLDR
- PAV BOOKS OPENCART 商城自适应主题模板 ABC-0018
- 多规则排序时根据权值排序
- 类和对象—类外访问私有数据
- 海量数据处理之基本方法
- select @@Identity
- sqlldr理论深入学习
- Unity3d游戏之游戏日志打印
- poj 1723 SOLDIERS 中位数
- 合并两个有序数组(48页)
- 规范的位操作方法
- IE6下margin双倍边距Bug的处理办法
- HDU 2896 病毒侵袭（AC自动机）
- OpenGL 资源汇编
- 转载科学网文章-circos教程四
