环境:
服务端:RHEL6.4 + Oracle 11.2.0.4
目录:
一、 创建外部表
二、 加载外部表数据到普通表
一、 创建外部表
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
<h1 id="2">二、 加载外部表数据到普通表</h1>
<h2 id="2.1">2.1 创建普通表</h2>
CREATE TABLE dept (
deptno NUMBER(6),
dname VARCHAR2(20),
loc VARCHAR2(25)
) tablespace dbs_d_jingyu;
<h2 id="2.2">2.2 直接插入</h2>
insert into dept select * from dept_external;
commit;
<h2 id="2.3">2.3 直接路径插入</h2>
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. *
<h1 id="3">三、References</h1>
- "APPEND Hint".[Online] Available:
[https://oracle-base.com/articles/misc/append-hint](https://oracle-base.com/articles/misc/append-hint) (Sep 23,2015)
- Oracle® Database SQL Language Reference 11g Release 2 (11.2)