转载

Oracle数据加载之外部表的介绍

环境:

服务端:RHEL6.4 + Oracle 11.2.0.4

一、创建外部表

1.1 创建外部表需要的目录
1.2 创建外部表
1.3 创建外部表源文件
1.4 查询外部表

二、加载外部表数据到普通表

2.1 创建普通表
2.2 直接插入
2.3 直接路径插入

三、References

一、创建外部表

1.1 创建外部表需要的目录

create or replace directory admin as '/u01/jingyu';

1.2 创建外部表

drop table dept_external purge; CREATE TABLE dept_external (    deptno     NUMBER(6),    dname      VARCHAR2(20),    loc        VARCHAR2(25)  ) ORGANIZATION EXTERNAL (TYPE oracle_loader  DEFAULT DIRECTORY admin  ACCESS PARAMETERS  (   RECORDS DELIMITED BY newline   BADFILE 'ulcase1.bad'   DISCARDFILE 'ulcase1.dis'   LOGFILE 'ulcase1.log'   SKIP 10   FIELDS TERMINATED BY ","  OPTIONALLY ENCLOSED BY '"'   (    deptno     INTEGER EXTERNAL(6),    dname      CHAR(20),    loc        CHAR(25)   )  )  LOCATION ('ulcase1.ctl') ) REJECT LIMIT UNLIMITED;

1.3 创建外部表源文件

创建源文件ulcase1.ctl

LOAD DATA INFILE * BADFILE 'sample.bad' DISCARDFILE 'sample.dsc' APPEND INTO TABLE emp WHEN (57) = '.' TRAILING NULLCOLS (deptno, dname, loc) BEGINDATA 10,ACCOUNTING,NEW YORK 20,RESEARCH,DALLAS 30,SALES,CHICAGO 40,OPERATIONS,BOSTON

这里是把这个ctl文件当作数据文件使用,上面skip=10对应了是跳过前面10行无效信息,从数据行开始读取。

1.4 查询外部表

SQL> select * from dept_external;     DEPTNO DNAME  LOC ---------- -------------------- -------------------------  10 ACCOUNTING    NEW YORK  20 RESEARCH      DALLAS  30 SALES  CHICAGO  40 OPERATIONS    BOSTON

二、加载外部表数据到普通表

2.1 创建普通表

CREATE TABLE dept (    deptno     NUMBER(6),    dname      VARCHAR2(20),    loc        VARCHAR2(25)  ) tablespace dbs_d_jingyu;

2.2 直接插入

insert into dept select * from dept_external; commit;

2.3 直接路径插入

insert /*+append*/ into dept select * from dept_external; commit;

一般情况,直接路径插入的效率要高。因为:

Data is appended to the end of the table, rather than attempting to use existing free space within the table.
Data is written directly to the data files, by-passing the buffer cache.
Referential integrity constraints are not considered. *
No trigger processing is performed. *

三、References

"APPEND Hint".[Online] Available:
https://oracle-base.com/articles/misc/append-hint (Sep 23,2015)
Oracle® Database SQL Language Reference 11g Release 2 (11.2)

正文到此结束