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

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

 ``` 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; ``` 

 创建源文件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行无效信息,从数据行开始读取。  

 ``` SQL> select * from dept_external; 

DEPTNO DNAME                LOC

    10 ACCOUNTING           NEW YORK
    20 RESEARCH             DALLAS
    30 SALES                CHICAGO
    40 OPERATIONS           BOSTON

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

insert into dept select * from dept_external;
 commit;

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. *