대용량 Sam 파일 오라클에 Load하기

1. external table
2. sql loader (direct path load)

External Table

프로젝트를 하다보면 Data Interface가 필요할때가 있다.
그때마다 다양한 방식으로 Interface를 하겠지만. 파일을 통한 방식이 의외로 많이 사용되곤 한다.
이때 사용할 수 있는 방법이 SQL Loader를 이용하는 방법, Procedure를 사용하여 upload하는 방법, External table을 이용하는 방법...등이 있는데

오늘은 External table을 이용하는 방법에 대해 적어보겠다.
먼저 예제부터 쓰면

create table ext_table_test (
   test1 char(10),
   test2 char(20)
)
organization external (
   type       oracle_loader
   default directory ext_dir
   access parameters (
     records delimited by newline
     fields (
       test1 position(1: 4) char,
       test1 position(5:30) char
    )
  )
  location ('file')
)
reject limit unlimited;

위의 방식은 file의 레이아웃이 총 30자리로 10자리 / 20자리씩 끊어서 ext_table_test 라는 external table를 만들고
그 테이블에 데이터를 인서트하는 방식이다.

이때 따로 만든 테이블에 인서트를 해야할 경우 이 external 테이블을 참조하여 따로 만든 테이블에 데이터를 인서트하면된다.
(뭐 직접 쿼리를 쓰든, 테이블에서 테이블로 데이터를 옮겨가는 Procedure를 만들어 사용하든 방법은 여러가지이다.)

다음엔 이 테이블을 좀더 고급화하여 프로지서로 생성하는 방법을 소개하도록 하겠다.
(왜???? 일일이 쿼리써서 언제 일을 하겠나? 프로시저를 만들어서 스캐줄작업에 넣어두면 그냥 아무것도 안해도 오라클이 다하는데...ㅎㅎㅎ)

10g 에서 SQL*LOADER 의 성능 향상 TIP   

SQL*LOADER 사용시에 여러가지 적절한 옵션 사용과 테이블에 대한 설정값들을 변경시켜줍으로써 많은 성능 향상을 가져올수 있다.

다음은 그 권장되는 설정법들을 설명한 것이다.

 

1. DIRECT PATH LOAD를 사용한다. Direct path load는 load되는 테이블이나 파티션에 exclusive access를 요구한다. 추가적으로 트리거는 자동으로 disable되고 제약조건은 로드가 완료될때까지 defer(연기) 된다.

 

 

2. direct path load로 데이타를 로드시에 모든 CHECK 와 REFERENCE 무결성 제약조건은 자동으로 DISABLE된다. 그러나 그밖의 다른 타입의 제약조건 NOT NULL, UNIQUE, PRIMARY KEY 조건들은 반드시 DISABLE 시켜준다. 작업 완료후에 수동으로 enable시켜준다.

 

 

3. 정렬된 순서로 데이타를 load한다. 미리 sorting 된 데이타는 정렬에 필요한 공간temporary 영역 사용을 최소화한다. SQL*LOADER 옵션에서 SORTED INDEXES 를 CONTROL파일에 기술한다.

 

 

4. 인덱스 MAINTANCE 를 연기(DEFERRING)하라. 인덱스는 데이타가 insert하거나 delete 또는 key 컬럼이 업데이트 될때마다 자동으로 maintance 를 실시한다.따라서 많은 양의 데이타를 load할때 작업이 종료된 후에 maintance를 한다면 좀더 빠를 것이다. SKIP_INDEX_MAINTENANCE = TRUE  를 설정하면 인덱스 세그먼트의 상태가 “ INDEX UNUSABLE”상태가 되어있더라도 LOADER시작시에 인덱스 MAINTANCE를 SKIP한다.

 

 

5. UNRECOVERABLE 을 사용해서 REDO 생성을 DISABLE시킨다. 모든 recover 시에는 redo log파일이 필요하다. 그러나 redo log 생성은 부하가 많기 때문에 disable시키면 그만큼 속도는 빠르게 된다. 그러나 LOADER 작업 중간에 FAILURE 가 발생한다면 처음부터 load작업을 다시 해야한다. 따라서 리두로그를 생성하지 않기때문에 loader 작업 후에는 반드시 백업하도록 한다.

 

 

6. 단일 파티션에 loading 하도록 한다. 파티션 테이블에 데이타 load시 기타 사용자는 다른 파티션 에 접근 할 수 있다. APRIL 파티션을 로드시에 jan 에서 april 까지 쿼리를 실행하는 유저를 차단하지 못한다. 따라서 부하가 증가한다.

 

 

7. Parallel 로 로드를 한다. 파티션으로 구성되어 있다면 parallel 로 다중 파티션을 로드할 수 있다.

 

 

8. STREAMSIZE 파라미터는 SQL*LOADER client 에서 oracle server로 direct path stream buffer 의 크기(bytes)를 지정한다. 기본값은 256000 bytes 이다.  또한

columnarrayrows 또한 direct path column array 에 전송하는 row의 갯수를 지정한다.

기본값은 5000이다.

 

 

9. 만일 load되는 데이타가 중복되는 날자값을 많이 가지고 있다면 DATE_CACHE 파라미터를 설정해서 DIRECT PATH LOAD시에 더 나은 성능을 보장할 수 있다.

CACHE에 CONVERION 되는 DATE 의 사이즈(엔트리)를 지정한다. (기본값은 1000)

Direct-Load Insert의 이해와 활용

PURPOSE

Direct Load Insert를 이해한다.

SCOPE & APPLICATION

• Data Warehouse에서 기존의 Table에 Daily Data를 Incremental Data Loading 시에 유용하게 사용.
• Create table as Select 를 사용하지 못하는 상황에서 대량의 Data를 Loading하기 위해서 사용할 수 있다.

KEY IDEA

Convential Insert에서와 같이 Data Buffer Cache를 사용하는 것이 아니라 Direct하게 Oracle Data File에 Write한다. 이와 같은 Mechanism은 Oracle Direct Path Load와 동일한 Mechanism이다.

( KEY WORD : DIRECT-LOAD INSERT , INSERT SELECT, PARALLEL, APPEND, DATA MIGRATION )

SUPPOSITION

이 Mechanism은 Insert …… Select 에서만 가능하다.

DESCRIPTION

• 방법
  • Serial Direct Load Insert
    Insert /*+ append */ into Atable
    Select * from Btable
     
  • Parallel Direct Load Insert
    Insert /*+ parallel(a 5) */ into Atable
    Select * from Btable
    
    ** 단, 해당 Session에 대해서 Parallel DML을 Enable시켜야 한다.
    ( ALTER SESSION ENABLE PARALLEL DML ; )

• 개념
  
  이것은 Partitioned , 또는 Nonpartitioned Table에 적용할 수 있다. 방법에는 Serial, 또는 Parallel하게 수행할 수 있다.
   
  • Serial Direct Load Insert
    현재의 High Water Mark이후부터 Data가 Insert 된다. 이때, Commit이 Invoke되면 High Water Mark가 Update되고 다른 Session에서도 볼 수 있게 되는 것이다.
     
  • Parallel Direct Load Insert
    Create table as Select와 동일한 Mechanism으로 Parallel Degree수 만큼의 Temporary segment를 Assign하고 Commit이 invoke되면 Parallel Query Coordinator가 해당 Temporary Segment를 Merge하고 High Water Mark를 Update하고 다른 Session에서도 볼 수 있게 된다.
     
• Logging / Nologging Direct-Load Insert
  Direct-Load Insert는 Logging 또는 Nologging Mode로 수행될 수 있다.
   
  • Logging Mode : 이것은 Full Redo Logging을 수행한다. Instance & Media Recovery를 성공적으로 수행할 수 있다.
     
  • Nologging Mode : Redo / Undo Logging을 수행하지 않는다. Insert Performance를 극대화할 수 있다. 하지만 Logging을 수행하지 않기 때문에 Recovery를 수행하지 못하는 상황이 발생할 소지가 있다. Insert를 수행하고서 반드시 Backup전략을 수립해서 Backup을 수행해야 한다.
     
  • Nologging Mode Direct-Load Insert 예
    ALTER TABLE emp NOLOGGING;
    ALTER SESSION ENABLE PARALLEL DML;
    INSERT /*+ PARALLEL(emp 12) */ INTO emp
    SELECT /*+ PARALLEL(t_emp 12) */
    *
    FROM t_emp;
     
• Direct-Load Insert의 제약사항
   
• Init.ora에서 ROW_LOCKING = INTENT 로 지정되어 있으면 수행할 수 없다.
   
• Referential Integrity를 지원하지 못한다.
   
• 해당 Table에 Trigger가 존재하더라도 Data가 Insert될 때 Invoke되지 않는다.
   
• Replication functionality를 지원하지 못한다.
   
• LOB Column을 가진 Table에는 수행할 수 없다.
   
• Index-Organized Table에도 수행할 수 없다.
   
• Distributed Transaction에서는 지원하지 않는다.
   
• Clustered Table에서도 수행할 수 없다.
  

파일을 읽어서 테이블에 인서트하는 프로시저

오늘을 파일을 읽어서 테이블에 인서트하는 프로시저를 소개하겠다.
단...오늘은
시간이 없어서...그냥 소스만 올려놓고..주석은 다음에 달도록 하겠다...

create or replace PROCEDURE INTER_MMMS0009 IS
-- 샘파일 로드 관련 변수
fHandler UTL_FILE.FILE_TYPE;  --샘파일 객체
V_LINE VARCHAR2(2000);   --샘파일 라인

--시스템조건변수
LOOP_CONDITION NUMBER(10) :=1;  --샘파일 읽는 조건 변수
EXIST_EMP NUMBER(3) :=0;  --사원마스터존재여부
UPDATE_CONDICTION NUMBER(3) :=0; --업데이트 조건변수
COMP_DCD KCOK_MMMS0009.DCD%TYPE; --패치 데이터

--인터패이스 현황 관련 변수
DATA_COUNT NUMBER(9) :=0;  --읽은 샘파일 갯수 파악(전송시도횟수)
DUP_COUNT_0009 NUMBER(9) :=0;  --0009중복 건수
INSERT_COUNT_0009 NUMBER(9) :=0; --0009테이블 인서트 성공 건수(입력성공건수)
UPDATE_COUNT_0009 NUMBER(7) :=0; --0009테이블 업데이트 성공 건수(변경건수)
ERROR_COUNT_0009 NUMBER(9) :=0;  --0009태이블 이관 에러 건수(입력실패건수)
ERROR_MSG_0009 VARCHAR2(1000);  --0009이관 오류내용
DUP_COUNT_0013 NUMBER(9) :=0;  --0013중복 건수
INSERT_COUNT_0013 NUMBER(9) :=0; --0013테이블 인서트 성공 건수(입력성공건수)
UPDATE_COUNT_0013 NUMBER(7) :=0; --0013테이블 업데이트 성공 건수(변경건수)
ERROR_COUNT_0013 NUMBER(9) :=0;  --0013태이블 이관 에러 건수(입력실패건수)
ERROR_MSG_0013 VARCHAR2(1000);  --0013이관 오류내용

--테이블 컬럼 변수
VAL KCOK_MMMS0009%ROWTYPE;  --KCOK_MMMS0009테이블 변수
NAME KCOK_MMMS0013.EE_NM%TYPE;  --KCOK_MMMS0013.EE_NM

--디버깅데이터
DEBUG_DATA VARCHAR2(50);

BEGIN
  fHandler := UTL_FILE.FOPEN('MMMS_DIR', 'A1A.txt', 'R');
 
  WHILE LOOP_CONDITION >0 LOOP
    UTL_FILE.GET_LINE(fHandler, V_LINE);
    --MOVE DATA
    VAL.EENO := TRIM(SUBSTR(V_LINE,1,7)); --사원번호
    NAME := TRIM(SUBSTR(V_LINE,8,14));  --사원명
    VAL.RGST_YMD := SUBSTR(V_LINE,22,8); --등록년월일(발령년월일)
    VAL.DCD := TRIM(SUBSTR(V_LINE,30,5)); --부서코드
    VAL.POA_CD := SUBSTR(V_LINE,35,1);  --직위코드(없는것도 있음)
    VAL.DRTY_SCN_CD :=SUBSTR(V_LINE,36,1); --직간접코드
    IF  VAL.DRTY_SCN_CD = ' ' THEN
 VAL.DRTY_SCN_CD := '2';
    END IF;
    LOOP_CONDITION := LENGTH(V_LINE);
    IF LOOP_CONDITION > 0 THEN
      DATA_COUNT := DATA_COUNT+1;
    END IF;

    --사원마스터데이터 이관
    SELECT COUNT(EENO)
      INTO EXIST_EMP
      FROM KCOK_MMMS0013
     WHERE EENO = VAL.EENO;
    IF EXIST_EMP = 0 THEN
 INSERT INTO KCOK_MMMS0013
      (EENO, EE_NM, RGST_YMD, RGN_EENO, RGST_DTM)
      VALUES (VAL.EENO, NAME, VAL.RGST_YMD, 'SYSTEM', SYSDATE);
 COMMIT;
 INSERT_COUNT_0013 := INSERT_COUNT_0013 + 1;
    ELSE IF EXIST_EMP = 1 THEN
     DUP_COUNT_0013 := DUP_COUNT_0013 + 1;
     ERROR_COUNT_0013 := ERROR_COUNT_0013 + 1;
     IF ERROR_COUNT_0013 <10 THEN
        ERROR_MSG_0013 := ERROR_MSG_0013 || 'DUP_EENO:' || VAL.EENO;
     END IF;
  ELSE
     ERROR_COUNT_0013 := ERROR_COUNT_0013 + 1;
     IF ERROR_COUNT_0013 <10 THEN
        ERROR_MSG_0013 := ERROR_MSG_0013 || 'DATA 구조이상';
     END IF;
  END IF;
    END IF;
    --업데이트 조건 찾기
    SELECT COUNT(EENO)
      INTO UPDATE_CONDICTION
      FROM KCOK_MMMS0009
     WHERE EENO = VAL.EENO
       AND ERS_YMD IS NULL;
    --데이터이관
    IF UPDATE_CONDICTION = 0 THEN
       --인서트(새로운 사원등록)
       INSERT INTO KCOK_MMMS0009
     (EENO, RGST_YMD, DCD, POA_CD, DRTY_SCN_CD, RGST_DTM)
            VALUES (VAL.EENO, VAL.RGST_YMD, VAL.DCD, VAL.POA_CD, VAL.DRTY_SCN_CD, SYSDATE);
       COMMIT;
       INSERT_COUNT_0009 := INSERT_COUNT_0009 + 1;
    ELSE IF UPDATE_CONDICTION = 1 THEN
    --업데이트&인서트(사원부서변경)
       SELECT NVL(DCD,'NULL')
      INTO COMP_DCD
      FROM KCOK_MMMS0009
     WHERE EENO = VAL.EENO
       AND ERS_YMD IS NULL;
     IF COMP_DCD = VAL.DCD THEN
  --에러
  ERROR_COUNT_0009 := ERROR_COUNT_0009 + 1;
  DUP_COUNT_0009 := DUP_COUNT_0009 + 1;
  IF ERROR_COUNT_0009 <10 THEN
     ERROR_MSG_0009 := ERROR_MSG_0009 || 'DUP_EENO:'||VAL.EENO||';';
  END IF;
     ELSE    
  UPDATE KCOK_MMMS0009
     SET ERS_YMD = VAL.RGST_YMD
   WHERE EENO = VAL.EENO
     AND ERS_YMD IS NULL;
  INSERT INTO KCOK_MMMS0009
      (EENO, RGST_YMD, DCD, POA_CD, DRTY_SCN_CD, RGST_DTM)
             VALUES (VAL.EENO, VAL.RGST_YMD, VAL.DCD, VAL.POA_CD, VAL.DRTY_SCN_CD, SYSDATE);
  COMMIT;
  UPDATE_COUNT_0009 := UPDATE_COUNT_0009 + 1;
     END IF;
  ELSE
     --에러
     ERROR_COUNT_0009 := ERROR_COUNT_0009 + 1;
     IF ERROR_COUNT_0009 <10 THEN
        ERROR_MSG_0009 := ERROR_MSG_0009 || 'EENO:'||VAL.EENO||';LINE:'||DATA_COUNT||';';
     END IF;
  END IF;
    END IF;
  END LOOP;
  --에러
  ERROR_COUNT_0009 := ERROR_COUNT_0009 + 1;
  IF ERROR_COUNT_0009 <10 THEN
     ERROR_MSG_0009 := ERROR_MSG_0009 || 'SAMFILE_ERROR;';
  END IF;
  --데이터입력현황테이블 채우기
  INSERT INTO KCOK_INSA0002
       (SRC_TABL_NM, FIN_TRSF_DT, ET_TRY_OFT, INP_SUCS_CT, INP_FAIL_CT, ALTR_CT, DUP_CT, TRSF_ERR_SBC)
       VALUES ('KCOK_MMMS0009', SYSDATE, DATA_COUNT, INSERT_COUNT_0009, ERROR_COUNT_0009, UPDATE_COUNT_0009, DUP_COUNT_0009, SUBSTR(ERROR_MSG_0009,1,100));
  INSERT INTO KCOK_INSA0002
       (SRC_TABL_NM, FIN_TRSF_DT, ET_TRY_OFT, INP_SUCS_CT, INP_FAIL_CT, ALTR_CT, DUP_CT, TRSF_ERR_SBC)
       VALUES ('KCOK_MMMS0139', SYSDATE, DATA_COUNT, INSERT_COUNT_0013, ERROR_COUNT_0013, UPDATE_COUNT_0013, DUP_COUNT_0013, SUBSTR(ERROR_MSG_0013,1,100));
  COMMIT;

  UTL_FILE.FCLOSE(fHandler);
EXCEPTION
WHEN NO_DATA_FOUND  THEN
--데이터입력현황테이블 채우기
  INSERT INTO KCOK_INSA0002
       (SRC_TABL_NM, FIN_TRSF_DT, ET_TRY_OFT, INP_SUCS_CT, INP_FAIL_CT, ALTR_CT, DUP_CT, TRSF_ERR_SBC)
       VALUES ('KCOK_MMMS0009', SYSDATE, DATA_COUNT, INSERT_COUNT_0009, ERROR_COUNT_0009, UPDATE_COUNT_0009, DUP_COUNT_0009, SUBSTR(ERROR_MSG_0009,1,100));

  INSERT INTO KCOK_INSA0002
       (SRC_TABL_NM, FIN_TRSF_DT, ET_TRY_OFT, INP_SUCS_CT, INP_FAIL_CT, ALTR_CT, DUP_CT, TRSF_ERR_SBC)
       VALUES ('KCOK_MMMS0013', SYSDATE, DATA_COUNT, INSERT_COUNT_0013, ERROR_COUNT_0013, UPDATE_COUNT_0013, DUP_COUNT_0013, SUBSTR(ERROR_MSG_0013,1,100));

  COMMIT;
  UTL_FILE.FCLOSE(fHandler);

WHEN utl_file.invalid_path THEN
     raise_application_error(-20000, 'ERROR: Invalid path. Create directory or set UTL_FILE_DIR.');
WHEN OTHERS THEN
  ERROR_COUNT_0009 := ERROR_COUNT_0009 + 1;
  IF ERROR_COUNT_0009 <10 THEN
     ERROR_MSG_0009 := 'OTHER_EXCEPTION;' || ERROR_MSG_0009;
  END IF;
  INSERT INTO KCOK_INSA0002
       (SRC_TABL_NM, FIN_TRSF_DT, ET_TRY_OFT, INP_SUCS_CT, INP_FAIL_CT, ALTR_CT, DUP_CT, TRSF_ERR_SBC)
       VALUES ('KCOK_MMMS0009', SYSDATE, DATA_COUNT, INSERT_COUNT_0009, ERROR_COUNT_0009, UPDATE_COUNT_0009, DUP_COUNT_0009, SUBSTR(ERROR_MSG_0009,1,100));
  COMMIT;
END INTER_MMMS0009;