웹 개발과 관련된 모든 것들

External Table

- Oracle 9i 부터 제공하는 기능으로, Table이 Database 내부의 Tablespace가 아닌 Database 외부의 OS 파일로 존재하게 하는 기능이다.

- 흔히 DW(Data Warehousing) 환경에서 기본적인 ETL(Extraction, Transformation, Loading) 작업에 사용된다.

- External table 의 데이터엔 읽기전용으로만 접근이 가능하고, 저장되는 형식은 일정한 포맷이 정해진 것이 아니라, 규칙적인 포맷으로만 저장이 되어 있으면 읽어올때 그 포맷을 지정하여 불러올 수 있다. 이렇게 일정한 규칙으로 생성되어 있기 때문에 다른 Database 에서도 이 데이터를 사용할 수가 있는 장점이 있다. → 플랫폼에 독립적이다.

- External Table의 데이터에 대해 select, join, sort 를 할 수 있고, view, synonym 도 생성할 수 있다. 그러나 데이터는 읽기전용이기 때문에 Update, Insert, Delete는 불가능하다. index 생성도 불가능하다.

※ Possible - select, join, sort, view, synonym

※ Impossible - update, insert, delete, index

- External Table 에 대한 metadata 정의는 아래 형식으로 한다.

SQL> CREATE TABLE table_name ORGANIZATION EXTERNAL ...

- External Table에 데이터를 처음 Unload 하게 되면, 자동으로 Select 문장의 데이터타입을 기준으로 metadata가 생성된다.

- External Table로 접근하는 방법

(1) ORACLE_LOADER : external 파일의 데이터를 읽어오는 기능. SQL*Loader 의 문법을 따른다.

(2) ORACLE_DATAPUMP : external 파일에 데이터를 쓰는 기능을 하고, 이를 다시 Database에 다시 reload 한다.

                                      Database로부터 데이터를 읽고 External Table에 insert 한다. 한번에 여러개 파일 가능.

- External Table의 장점

(1) 병렬 SQL 수행가능

(2) Table로 Loading 불필요

(3) Storage 절약

(4) Virtual Read-only Table

(5) External regular table 간 조인가능

- External Table의 단점

(1) index 생성 불가 (파일처리가 더 빠름)

(2) Join 이나 Filter 수행이 어려움

====================================================================================================================

CREATE EXTERNAL TABLE

- CREATE TABLE ... ORGANIZATION EXTERNAL 구문을 사용하여 생성하기는 하지만, 실제로 Table이 생성되는 것은 아니다. external table은 어떤 extents 와도 관련이 되어 있지 않다.

- External Data에 접근하려면 Data Dictionary 에 metadata를 생성해야 한다.

[예제] Creating External Tables

- 여기서 사용하는 employees table은 hr.employees table 구조를 그대로 사용하였다.

- 필요한 권한: CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, SELECT ANY TABLE

- Directory 오브젝트의 권한 : READ, WRITE

1. 아래처럼 External Table을 사용할 디렉토리를 생성하고 oracle 관리자가 관련파일에 접근할 수 있도록 디렉토리에 대한 권한을 변경한다.

% cd /export/home/kangyw/oracle_test

% mkdir data log bad

% chmod -R 777 data log bad

2. data 디렉토리에 External Table의 데이터 파일 2개를 생성한다. (empxt1.dat, empxt2.dat)

% cd data

% vi empxt1.dat

360,Jane,Janus,ST_CLERK,121,17-MAY-2001,3000,0,50,jjanus
361,Mark,Jasper,SA_REP,145,17-MAY-2001,8000,.1,80,mjasper
362,Brenda,Starr,AD_ASST,200,17-MAY-2001,5500,0,10,bstarr
363,Alex,Alda,AC_MGR,145,17-MAY-2001,9000,.15,80,aalda

% vi empxt2.dat

401,Jesse,Cromwell,HR_REP,203,17-MAY-2001,7000,0,40,jcromwel
402,Abby,Applegate,IT_PROG,103,17-MAY-2001,9000,.2,60,aapplega
403,Carol,Cousins,AD_VP,100,17-MAY-2001,27000,.3,90,ccousins
404,John,Richardson,AC_ACCOUNT,205,17-MAY-2001,5000,0,110,jrichard

3. External Table의 디렉토리를 지정하고, kangyw user에게 해당 디렉토리에 대한 권한을 준다.

 

Data Dictionary 에 해당 디렉토리 정보가 등록되고, 이 디렉토리 접근은 kangyw 만 가능하다.

4. External Table에서 DB내부 Table로 Unload를 하기 위해 필요한 employees table은

hr user의 employees table 구조를 사용해야하므로, kangyw user 에게 hr.employees table과 동일한 구조로 employees table을  생성해준다. (subquery as 구문 사용)

SQL> CREATE TABLE kangyw.employees AS SELECT * FROM hr.employees WHERE EMAIL = ' ';

5. External Table admin_ext_employees을 생성한다.

dat 파일이 생성시 지정되기 때문에 지금 정의하는 External Table 을 Select 하게 되면, OS상의 dat 파일을 읽어들이게 된다.

CREATE TABLE admin_ext_employees (
       employee_id     NUMBER(4),
       first_name      VARCHAR2(20),
       last_name       VARCHAR2(25),
       job_id          VARCHAR2(10),
       manager_id      NUMBER(4),
       hire_date       DATE,
       salary          NUMBER(8,2),
       commission_pct  NUMBER(2,2),
       department_id   NUMBER(4),
       email           VARCHAR2(25))

       ORGANIZATION EXTERNAL (
              TYPE ORACLE_LOADER      ☞ 접근방식 지정 (ORACLE_LOADER / ORACLE_DATAPUMP)

                                                           default : ORACLE_LOADER

              DEFAULT DIRECTORY admin_dat_dir  
              ACCESS PARAMETERS)  ☞ ㅂ

       records delimited by newline
       badfile admin_bad_dir:'empxt%a_%p.bad'
       logfile admin_log_dir:'empxt%a_%p.log'
       fields terminated by ','                                       ☞ 필드구분자는 쉼표(,)로 함.

                missing field values are null (
                        employee_id, first_name, last_name, job_id, manager_id,
                        hire_date char date_format date mask "dd-mon-yyyy",
                        salary, commission_pct, department_id, email)
                )

                Location('empxt1.dat', 'empxt2.dat')         ☞ 데이터파일 위치 지정
        )

        PARALLEL       ☞ data 소스에서 parallel 쿼리가 가능하게 함. 많은 양의 데이터를 다룰때 사용.
        REJECT LIMIT UNLIMITED;     ☞ External Table 조회 시 발생하는 에러 수에 제한이 없음.

생성 후 바로 조회하면 데이터 확인이 가능하다.

6. External Table admin_ext_employees 의 내용(OS상 dat 파일)을 DB 내의 employees table에 Insert 한다.

employees table을 조회하면 데이터가 들어가있는 것을 확인할 수 있다.

7. 데이터가 정상적으로 Loading 되면 로그를 확인한다.

=====================================================================================================================

ALTER EXTERNAL TABLE

SQL> ALTER TABLE admin_ext_employees REJECT LIMIT 100;

SQL> ALTER TABLE admin_ext_employees PROJECT COLUMN REFERNCED;

SQL> ALTER TABLE admin_ext_employees PROJECT COLUMN ALL;

SQL> ALTER TABLE admin_ext_employees DEFAULT DIRECTORY admin_dat2_dir;

SQL> ALTER TABLE admin_ext_employees ACCESS PARAMETERS (FIELDS TERMINATED BY ';');

SQL> ALTER TABLE admin_ext_employees Location('empxt3.txt', 'empxt4.txt');

- PARALLEL, ADD COLUMN, MODIFY COLUMN, DROP COLUMN, RENAME TO 는 일반 테이블변경방식과 동일함.