달력

3

« 2024/3 »

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
2011. 1. 14. 15:31

External Table Work/ORACLE2011. 1. 14. 15:31

Overview
Oracle 9i 이상
Table이 Database 내부의 Tablespace가 아닌 Database 외부의 OS 파일로 존재하게 하는 기능.
흔히 DW(Data Warehousing) 환경에서 기본적인 ETL(Extraction, Transformation, Loading) 작업에 사용.
주로 SELECT 만 사용하게 됨.
CREATE TABLE ... ORGANIZATION EXTERNAL 구문을 사용하여 생성하기는 하지만, 실제로 Table이 생성되는 것은 아니다.
External Table은 어떤 extents 와도 관련이 되어 있지 않다.
External Data에 접근하려면 Data Dictionary 에 metadata를 생성해야 한다.

문법
SQL> CREATE TABLE table_name ORGANIZATION EXTERNAL ...
External Table에 데이터를 처음 Unload 하게 되면, 자동으로 Select 문장의 데이터타입을 기준으로 metadata가 생성된다.

External Table 생성 방법
ORACLE_LOADER
: external 파일의 데이터를 읽어오는 기능. SQL*Loader 의 문법을 따른다.
ORACLE_DATAPUMP
: external 파일에 데이터를 쓰는 기능을 하고, 이를 다시 Database에 다시 reload 한다. 
Database로부터 데이터를 읽고 External Table에 insert 한다.
(... 사실 이렇게 External Table을 사용해 본 경험이 없다...)

External Table의 장점
병렬 SQL 수행가능
Table로 Loading 불필요
Storage 절약
Virtual Read-only Table*
External regular table 간 조인가능

External Table의 단점
index 생성 불가
Join 이나 Filter 수행이 어려움

[예제]
-- Oracle Directory 로 만들 경로 결정 혹은 생성
-- Oracle Directory 생성
        CREATE OR REPLACE DIRECTORY <oracle directory name> AS '<path>';
        GRANT READ, WRITE ON DIRECTORY <oracle directory name> TO <account>;
-- External Table 생성
CREATE TABLE et_ora_ext_t (
        col1 varchar(10)
        .
        .
        .
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER -- 생성 방식 지정 (ORACLE_LOADER / ORACLE_DATAPUMP)
     DEFAULT DIRECTORY <directory>
     ACCESS PARAMETERS 
       ( records delimited by newline
        badfile D_ORA_EXT_TXT:'et_ora_ext_t.bad'
        logfile D_ORA_EXT_TXT:'et_ora_ext_t.log'
        fields terminated by ',' -- 컬럼 구분자로 사용할 문자
        missing field values are null -- 데이터가 없으면 null 로 입력
                 )
     LOCATION (D_ORA_EXT_TXT:'et_ora_ext_t.txt') -- <oracle directory name> 내의 데이터 파일이름.
  )
REJECT LIMIT UNLIMITED
PARALLEL -- 병렬처리 여부 (PARALLEL | NOPARALLEL)
;

SELECT * FROM et_ora_ext_t;

--ALTER EXTERNAL TABLE
ALTER TABLE et_ora_ext_t REJECT LIMIT 100;
ALTER TABLE et_ora_ext_t PROJECT COLUMN REFERNCED;
ALTER TABLE et_ora_ext_t PROJECT COLUMN ALL;
ALTER TABLE et_ora_ext_t DEFAULT DIRECTORY admin_dat2_dir;
ALTER TABLE et_ora_ext_t ACCESS PARAMETERS (FIELDS TERMINATED BY ';');
ALTER TABLE et_ora_ext_t LOCATION('et_ora_ext_t2.txt', 'et_ora_ext_t3.txt');
-- PARALLEL, ADD COLUMN, MODIFY COLUMN, DROP COLUMN, RENAME TO 는 일반 테이블변경방식과 동일함.

-- 일반 테이블을 삭제하는 것과 같지만, DB 외부의 실제 데이터는 삭제되지 않고, DB 내의 metadata 만 삭제된다.
-- DROP EXTERNAL TABLE
DROP TABLE admin_ext_employees;
:
Posted by Elick