External Table

External Table

작성자: 강명규
OS: Windows 2000 Advanced Server
Oracle: 9.0.1.0.1
참조문서: Oralce Magazine 2002 1/2월호 EXPERT ADVICE코너의 External Tables: More Than an 
ETL TOOL

오라클에서 flat file을 테이블처럼 처리하도록 할 수 있게 해주는 것이 external table이다.
flat file이란 그냥 raw data만 있다고 생각하면 되겠다. CSV(Comma Separated Values)파일도 이에
 해당한다.
External Table은 9i부터 지원되므로 9i이전에서는 organization external에서 에러가 발생한다.

특징:
virtual read-only table
인덱스 사용 못함(파일처리가 빠르기때문에 필요없음)
OS명령을 통해 insert, update, delete수행
external table간의 조인 가능, external-regular table간의 조인 가능

[e:etableemp.dat]
7369,SMITH,CLERK,20
7499,ALLEN,SALESMAN,30
7521,WARD,SALESMAN,30
7566,JONES,MANAGER,20
7654,MARTIN,SALESMAN,30
7698,BLAKE,MANAGER,30
7782,CLARK,MANAGER,10
7788,SCOTT,ANALYST,20
7839,KING,PRESIDENT,10
7844,TURNER,SALESMAN,30
7876,ADAMS,CLERK,20
7900,JAMES,CLERK,30
7902,FORD,ANALYST,20
7934,MILLER,CLERK,10

SQL> conn system/manager
연결되었습니다.
SQL> create directory test_dir as
  2  'e:etable';

디렉토리가 생성되었습니다.

SQL> create table emp_external
  2  (               
  3   empno char(4),
  4   ename char(10),
  5   job char(9),
  6   deptno char(2)
  7  )
  8  organization external
  9  (
 10   type oracle_loader
 11   default directory test_dir  
 12   access parameters
 13   (
 14    fields terminated by ','
 15    (empno, ename, job, deptno)
 16   )
 17   location ('emp.dat')
 18  );

테이블이 생성되었습니다.

SQL> desc emp_external
 이름                                      널?      유형
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              CHAR(4)
 ENAME                                              CHAR(10)
 JOB                                                CHAR(9)
 DEPTNO                                             CHAR(2)

SQL> select * from emp_external;

EMPN ENAME      JOB       DE
---- ---------- --------- --
7369 SMITH      CLERK     20
7499 ALLEN      SALESMAN  30
7521 WARD       SALESMAN  30
7566 JONES      MANAGER   20
7654 MARTIN     SALESMAN  30
7698 BLAKE      MANAGER   30
7782 CLARK      MANAGER   10
7788 SCOTT      ANALYST   20
7839 KING       PRESIDENT 10
7844 TURNER     SALESMAN  30
7876 ADAMS      CLERK     20
7900 JAMES      CLERK     30
7902 FORD       ANALYST   20
7934 MILLER     CLERK     10

14 개의 행이 선택되었습니다.

SQL> select count(*) from emp_external;

  COUNT(*)
----------
        14

SQL> select * from emp_external
  2  where empno=7521;

EMPN ENAME      JOB       DE
---- ---------- --------- --
7521 WARD       SALESMAN  30


조인시의 분석
테이블 A와 테이블 B는 각각 동일하게 20만개의 로우(row)를 가지고 있다.
테이블 A에 조건을 준 select시 20개의 레코드가 반환되고, B또한 동일하다. 
테이블 A와 테이블 B에 대한 조인은 4백만개의 로우수를 access하여 400개의 로우수를 반환한다.
이때 걸린 시간은 3초였다고 한다. - 20만개 레코드를 만들기 귀찮아서 그냥 넘어간다.

emp2_external테이블은 emp2.dat를 참조한다.

SQL> create table emp2_external
  2  (
  3   empno char(4),
  4   ename char(10),
  5   job char(9),
  6   deptno char(2)
  7  )
  8  organization external
  9  (
 10   type oracle_loader
 11   default directory test_dir
 12   access parameters
 13   (
 14    fields terminated by ','
 15    (empno, ename, job, deptno)
 16   )
 17   location ('emp2.dat')
 18  );     

테이블이 생성되었습니다.

SQL> select a.empno, b.job, a.job
  2  from emp_external a, emp_external b
  3  where a.empno = b.empno
  4  and a.empno = 7900
  5  and b.empno = 7900;

EMPN JOB       JOB
---- --------- ---------
7900 CLERK     CLERK


[external table - regular table간의 조인]

SQL> create table emp_regular  
  2  (                           
  3   empno char(4),             
  4   ename char(10),            
  5   job char(9),               
  6   deptno char(2)             
  7  );

테이블이 생성되었습니다.

SQL> insert into emp_regular values(7900, '명규', 'DBA', 50);

1 개의 행이 만들어졌습니다.

SQL> select a.empno, b.job, a.job       
  2  from emp_external a, emp_regular b
  3  where a.empno = b.empno            
  4  and a.empno = 7900                 
  5  and b.empno = 7900;                

EMPN JOB       JOB
---- --------- ---------
7900 DBA       CLERK