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;

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

1. Data is appended to the end of the table, rather than attempting to use existing free space within the table.
2. Data is written directly to the data files, by-passing the buffer cache.
3. Referential integrity constraints are not considered. *
4. 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)