외부 테이블(External Table)

• Oracle 9i R1 에서 새롭게 추가
• 구분된 파일, 폭이 고정된 위치 파일과 같은 Flat File로 부터 조회할 수 있는 능력을 제공
• 쿼리는 가능하나 수정은 불가능. 인덱스 생성 또한 불가능

외부 테이블 설정

1. 디렉토리 설정

CREATE OR REPLACE DIRECTORY  DATA_DIR AS 'M:\ORACLE'; 

2. 외부 테이블 생성

SQL> create table external_table 
   2  (EMPNO NUMBER(4) ,
   3  ENAME VARCHAR2(10),
   4  JOB VARCHAR2(9),
   5  MGR NUMBER(4),
   6  HIREDATE CHAR(10),
   7  SAL NUMBER(7, 2),
   8  COMM NUMBER(7, 2),
   9  DEPTNO NUMBER(2)
  10  )
 * 11  ORGANIZATION EXTERNAL*
 * 12  ( type oracle_loader*
 * 13  default directory data_dir*
 * 14  access parameters*
 * 15  ( fields terminated by ',' )*
 * 16  location ('emp.dat')*
 * 17  )*
  18  /
테이블이 생성되었습니다. 

• 위 설정은 SQL*Loader에서 제어 파일과 비슷. 위 구문을 제어파일 형식으로 바꾼다면 다음과 같이 가능

LOAD DATA
 INFILE 'emp.dat'
 INTO TABLE emp
 REPLACE
 FIELDS TERMINATED BY ','
 (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 

• SQL*Loader를 이용하여 외부 테이블 변환을 쉽게 할 수 있다. 위와 같은 컨트롤 파일이 있다고 가정하고 다음의 구문을 수행하면 가능하다.

oracle>SQLLDR system/oracle EMP.CTL EXTERNAL_TABLE=GENERATE_ONLY

SQL*Loader: Release 10.2.0.3.0 - Production on 토 11월 15 03:11:54 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved. 

• 이후에 생성된 파일을 살펴보면 외부 테이블 생성 스크립트를 얻을 수 있다.

CREATE TABLE "SYS_SQLLDR_X_EXT_EMP" 
 (
   "EMPNO" NUMBER(4),
   "ENAME" VARCHAR2(10),
   "JOB" VARCHAR2(9),
   "MGR" NUMBER(4),
   "HIREDATE" DATE,
   "SAL" NUMBER(7,2),
   "COMM" NUMBER(7,2),
   "DEPTNO" NUMBER(2)
 )
 ORGANIZATION external 
 (
   TYPE oracle_loader
   DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
   ACCESS PARAMETERS 
   (
     RECORDS DELIMITED BY NEWLINE CHARACTERSET KO16MSWIN949
     BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'emp.bad'
     LOGFILE 'EMP.log_xt'
     READSIZE 1048576
     FIELDS TERMINATED BY "," LDRTRIM 
     REJECT ROWS WITH ALL NULL FIELDS 
     (
 \-- 중략
     )
   )
   location 
   (
     'emp.dat'
   )
 )REJECT LIMIT UNLIMITED 

• 3. SELECT 명령 수행

SELECT * FROM EXTERNAL_TABLE where rownum < 10;
      EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
 \---------\- \---------\- \--------\- \---------\- \---------\- \---------\- \---------\- \---------\-
       7369 SMITH      CLERK           7902 1980/12/17        800                    20
       7499 ALLEN      SALESMAN        7698 1981/02/20       1600        300         30
       7521 WARD       SALESMAN        7698 1981/02/22       1250        500         30
....
 .... 

• 여기서 읽혀지는 데이터는 버퍼 캐시를 거치지 않고 세션의 공간에 저장된다.
• TKPROF 나 autotrace에서 결과는 나오나 일반적인 오라클 테이블이 아니기 때문에 부정확할 수 있다.

• autotrace의 결과

SQL> set autotrace traceonly
SQL> l
   1\* SELECT * FROM EXTERNAL_TABLE where rownum < 1000
SQL> /
999 개의 행이 선택되었습니다.

 Execution Plan
 \---------------------------------------------------------\-
 Plan hash value: 2646445939
\---------------------------------------------------------------------------------------------\-
 \| Id  \| Operation                   \| Name           \| Rows  \| Bytes \| Cost (%CPU)\| Time     \|
 \---------------------------------------------------------------------------------------------\-
 \|   0 \| SELECT STATEMENT            \|                \|   999 \| 89910 \|     5   (0)\| 00:00:01 \|
 \|*  1 \|  COUNT STOPKEY              \|                \|       \|       \|            \|          \|
 \|   2 \|   EXTERNAL TABLE ACCESS FULL\| EXTERNAL_TABLE \|   999 \| 89910 \|     5   (0)\| 00:00:01 \|
 \---------------------------------------------------------------------------------------------\-
Predicate Information (identified by operation id):
 \--------------------------------------------------\-
   1 - filter(ROWNUM<1000)

 Statistics
 \---------------------------------------------------------\-
          17  recursive calls
           0  db block gets
          44  consistent gets
           0  physical reads
           0  redo size
       50323  bytes sent via SQL*Net to client
        1126  bytes received via SQL*Net from client
          68  SQL*Net roundtrips to/from client
           0  sorts (memory)
           0  sorts (disk)
         999  rows processed 

외부 테이블 수정하기

• 외부 테이블은 DML 연산이 일어날 수 없으므로, UTL_FILE과 같은 패키지를 이용하여 수정 가능
• 그러나 수정이 되더라도 오라클에서는 바로 인식이 가능하다.

직접 경로 적재에 외부 테이블 사용하기

Direct Path Load 의 방법

• 1. 명령 행에서 SQLLDR을 이용하여 스크립트를 작성한다.
• 2. INSERT 문을 사용한다.
• 3. CREATE TABLE AS SELECT 를 사용한다.

방법에 따른 적재 성능 비교

• 외부 테이블의 적재가 SQLLDR 보다 적은 오버헤드가 발생함

SQLLDR 에 비한 외부 테이블의 장점

• 1. 부하 자체로부터 직접 조회하기 위해 적재 과정에서 외부 테이블을 다른 테이블과 조인할 수 있다.
• 2. 상상할 수 있는 거의 모든 SLQ 술어를 사용하여 데이터를 걸러낼 수 있다. SQLLDR에서는 데이터를 걸러낼 수 있는 기능은 상대적으로 원시적이다.
• 3. 텔넷을 이용하여 서버 자체에 접속하지 않더라도 원격 클라이언트로부터 직접 경로 적재를 수행할 수 있다.
• 4. 간단하게 INSERT를 사용함으로써 직접 프로시저 내부로부터 SQLLDR 기능을 호출할 수 있다.

병렬 직접 경로 적재에 외부 테이블 사용하기

• SQLLDR을 이용할 시, 병렬 처리 하려면 SQLLDR을 여러개 수행해야 하나 외부 테이블을 이용하면 테이블 레벨에서 병렬 처리 가능

SQL> alter table external_table parallel 4;
테이블이 변경되었습니다.

SQL> create table emp4 as select * from external_table;
테이블이 생성되었습니다. 

• 위 명령이 수행되면서 테이블이 생성될 때 다음과 같이 병렬로 수행되는 것을 볼수 있다.

SQL> r
   1\* select sid,username,EVENT,ownerid from v$session where ownerid\!='2147483644'
       SID USERNAME                       EVENT                             OWNERID
 \---------\- \-----------------------------\- \-----------------------------\- \---------\-
        144 SYSTEM                         PX Deq Credit: send blkd            65688
        145 SYSTEM                         PX Deq: Execution Msg               65688
        150 SYSTEM                         PX Deq: Execution Msg               65688
        159 SYSTEM                         PX Deq: Execution Msg               65688 

병합에 외부 테이블 사용하기

• Oracle 9i 이후에 소개된 MERGE 명령과 외부 테이블을 직접 컨트롤 할 수 있는 장점 두 기능을 모두 사용하여 병합이 편해짐
• 다음은 병합을 사용하는 경우 SQL 문장 하나로 처리하는 경우이다.

SQL> merge into EMP e1
 2 using *EXTERNAL_TABLE e2*
 3 on ( e2.empno = e1.empno )
 4 when matched then
 5 update set e1.sal = e2.sal
 6 when not matched then
 7 insert (empno, ename, job, mgr, hiredate, sal, comm, deptno)
 8 values ( e2.empno, e2.ename, e2.job,
 e2.mgr, e2.hiredate, e2.sal, e2.comm, e2.deptno )
 9 /
 14 rows merged 

• 외부 테이블이 일반적인 Db 테이블과 같이 조인되어 사용이 되고 있다.

외부 테이블의 오류 처리하기

• 기본적으로 bad 파일은 파일과 같은 디렉토리에 생성이 되나 위치 및 파일 이름 변경 또한 가능하다.
• access parameter에서 bad file 절을 명시할 시 이것이 가능해 진다.

ORA> create table external_table
 2 (EMPNO NUMBER(4) ,
 3 ENAME VARCHAR2(10),
 4 JOB VARCHAR2(9),
 5 MGR NUMBER(4),
 6 HIREDATE DATE,
 7 SAL NUMBER(7, 2),
 8 COMM NUMBER(7, 2),
 9 DEPTNO NUMBER(2)
 10 )
 11 ORGANIZATION EXTERNAL
 12 ( type oracle_loader
 13 default directory data_dir
 14 access parameters
 15 (
 16 records delimited by newline
 17 *badfile data_dir:emp_external_table*
 18 fields terminated by ','
 19 )
 20 location ('emp.dat')
 21 )
 22 reject limit unlimited
 23 / 

• 수행 시 bad 파일의 이름은 emp_external_table.bad가 된다.
• 해당 bad 파일의 경우에도 외부 테이블로 조회가 가능하다.

SQL> create table emp_external_table_bad
 2 ( text1 varchar2(4000) ,
 3 text2 varchar2(4000) ,
 4 text3 varchar2(4000)
 5 )
 6 organization external
 7 (type oracle_loader
 8 default directory data_dir
 9 access parameters
 10 (
 11 records delimited by newline
 12 fields
 13 missing field values are null
 14 ( text1 position(1:4000),
 15 text2 position(4001:8000),
 16 text3 position(8001:12000)
 17 )
 18 )
 19 location ('emp_external_table.bad')
 20 )
 21 / 
 

• 한 행의 길이가 4000 까지는 text1에 저장되며, text2는 8000, text3는 12000까지 기록된다.
• 조회 시 다음과 같이 출력된다.

SQL> select count(*) from emp_external_table_bad;
  COUNT(*)
 \---------\-
      25722 
      
SQL> select * from emp_external_table_bad where rownum=1;
TEXT1
 \---------------------------------------------------------------------------------------------------\-
 TEXT2
 \---------------------------------------------------------------------------------------------------\-
 TEXT3
 \---------------------------------------------------------------------------------------------------\-
 7369,SMITH,CLERK,7902,1980/12/17,800,,20