JJON's StorY

============================
SQL*LOADER 성능 향상 기법
============================

 

    PURPOSE
    ---------
    이 문서는 SQL*LOADER 사용시 성능 향상 기법에 대해서 설명한다.

    Explanation
    -----------

    1. 테이블에 인덱스가 걸려있을 경우, 먼저 인덱스를 drop 시키고, SQL*Loader로
      데이터를 올린 후 index를 생성한다.

      1) 데이터를 로딩하는 동안 rollback 과 redo log의 양을 줄일 수 있다.
      2) B*Tree가 잘 balance 된다.

      중복된 데이터가 있는지 여부를 확인하기 위해서 index를 drop 시키지
      못하는 경우도 있다.

    2. 데이터를 로딩하기 전의 데이터를 삭제한 후 데이터를 로딩하기 위해서는 REPLACE
      옵션 대신 TRUNCATE 옵션을 사용한다.
      REPLACE 옵션을 사용할 경우 'DELETE FROM' 이라는 SQL 문장이 실행되게
      되는데, 이 경우 많은 양의 redo log 및 rollback 데이터가 발생하게 된다.
      반면 TRUNCATE 옵션을 사용할 경우 단순히 테이블을 truncate시키게 되므로
      redo log 및 rollback 정보가 발생하지 않는다.

    3. commit을 자주 하지 않을 경우 SQL*Loader가 보다 더 빨리 실행된다. command
      line 상에서 commit을 할 row 수의 최적화된 값을 지정할 수 있다. 이것은
      시스템의 메모리에 따라 달리 지정하게 되는데 ROWS = n (n은 사용자가 지정하는
      값)으로 지정하면 된다. 기본 값은 64이다. array 처리를 하여 속도가 개선되는
      것처럼 많은 양의 row를 처리한 후 commit을 하면 그 만큼 데이터베이스에 대한
      request가 줄어들게 된다.

    4. 데이터의 양이 많을 경우 redo log 파일의 크기가 충분하게 한다. 만약 redo log
      파일의 크기가 작을 경우 redo log를 switch할 때 마다 LGWR에서 timeout이
      발생하여 SQL*Loader에서 hang이 발생할 수 있다. 이와 같은 상황이 발생할 경우
      LGWR trace 파일이나 alert! log에 "file busy" 또는
      "can not advance to log sequence"라는 메시지가 남는다.

    5. bind array 크기를 가능한 크게 (단일 row 크기의 100배까지) 잡을 경우에도
      속도 개선이 가능하다. bind array 크기는 (#rows)*(maximum row size in bytes)
      값과 같다.
      이것은 파라미터 BINDSIZE 값을 지정하여 설정할 수 있다. ROWS 값도 적절하게
      큰 값을 사용하여야 한다. 그렇지 않을 경우 SQL*Loader에서는 기본값을 사용하게
      된다.

    6. SQL*Loader를 single-task로 운영되는 'sqlloaderst'로 link시킬 경우
      two-task에서 발생하는 overhead를 줄일 수 있다. 보통 benchmark test등에
      적절하다.

    7. 큰 데이터 파일을 loading할 때에는 tablespace에 rollback segment가 있고
      크기가 충분히 큰지 여부를 확인하여야 한다. 만약 tablespace에 freespace가
      없거나 적은 갯수의 rollback segment밖에 없을 경우에는 SQL*Loader가 hang이
      걸리거나 에러가 발생하게 된다.

    8. DIRECT PATH 옵션을 사용한다. 이 옵션을 사용하면 데이터가 SQL engine을 거치지
      않고 데이터파일에 바로 write된다. SQL engine을 거치지 않으므로 rollback
      segment도 사용되지 않는다.
      만약 DIRECT PATCH 옵션을 사용중이라면 다음과 같은 옵션을 사용하여 추가적인
      속도 개선을 얻을 수 있다.

    9. UNRECOVERABLE 절을 사용하면 loading 되는 데이터가 redo log에 기록되지
      않는다. 테이블에 index가 걸려 있을 경우에는 index에 대한 데이터는 redo log에
      기록된다.

    10. 테이블에 index가 걸려있고, 데이터가 index에 저장되는 순서되로 미리 정렬되어
      있다면 SORTED INDEX절을 사용할 수 있다.

      1) 데이터를 load 하기 전에 table은 empty 상태이어야만 한다.
      2) SORTED INDEX 절을 사용하는데 데이터가 올바로 정렬되어 있지 않다면
        인덱스의 status가 DIRECT LOAD 상태로 남게 된다.

    11. PARALLEL 옵션을 사용한다. 다중 SQL*Loader 세션을 사용할 경우 한개의 테이블
      도  parallel하게 loading 될 수 있다.

 

 

 아래의 예제와 같이 가변 길이의 filed들이 ',', '|' 와 같은 구분자로
 구분이 되고 있는 경우 oracle 8i부터 제공되는 'FILLER'라고 하는 필드
 구분자를 사용하여 상태인식자로 표시하여 insert시 skip할 수 있다.

 

    <Example>
    TABLE : skiptab
    ===========================
     col1 varchar2(20)
     col2 varchar2(20)
     col3 varchar2(20)

    CONTROLFIEL : skip.ctl
    LOAD DATA
    INFILE skip.dat
    INTO TABLE skiptab
    FIELDS TERMINATED BY ","
    (col1 char,
    col2 filler char,
    col3 char)

    DATAFILE : skip.dat
    SMITH,      DALLAS,        RESEARCH
    ALLEN,      CHICAGO,       SALES
    WARD,       CHICAGO,       SALES

    data loading :
    $sqlldr scott/tiger control=skip.ctl

    결과  :

    COL1          COL3
    -------------------------------------------------------
    SMITH         RESEARCH
    ALLEN         SALES
    WARD          SALES

 

 

 

======================================================
 또다른 예 readme.ctl
 SQL*LOADER를 이용하여 TEXT FILE을 INSERT하는 방법
======================================================

 

 SQL*LOADER를 이용하여 TEXT FILE을 그대로 DATABASE에 넣는 방법은 다음과 같다.
 여기서는 README.DAT 화일을 DATABASE의 README 테이블에 넣는 경우를 예로 들기로
 한다.

   <테이블 구조 : README>
     NAME   NULL  TYPE
     ----------------------
     NAME         VARCHAR2(10)
     TEXT         LONG

 

    <README.CTL>
     LOAD DATA
     INFILE readme.dat "fix 65535"
     APPEND
     CONCATENATE 10
     PRESERVE blanks
     INTO TABLE readme
     (name position(1:10) char,
      text char(65535))

     <README.DAT>
     원래의  README.DAT 앞에 10자리로 NAME 컬럼 내용을 추가하였음

     README     화일 내용

     <실행 명령>

   sqlldr username/password control=readme.ctl data=readme.dat bindsize=300000

  이렇게 하면 README.DAT의 내용이 하나의 RECORD로 되어 DATABASE에 입력된다.
  단, 최대로 입력 가능한 TEXT FILE의 크기는 200KB 이다.

 

 

================================================================
 SQL*LOADER에서 SEQUENCE 함수와 DECODE 함수 사용하는 방법
================================================================

   PURPOSE
   ---------

   Unique한 DATA을 load하고자 할때 쓰이는 SEQUENCE () 함수와 DECODE 함수의
   사용에 대해 알아보고자 한다.

   Explanation
   -----------
   table의 data에 unique한 값을 넣기 위해 sequence을 만들어 사용한다. 이것은
   블루틴 #10863 을 참고하면 알수 있다. 이때 loader에서 database의 sequence기능이
   아닌 sequence() 함수를 사용하는 것을 테스트해 보고자 한다.
   또한 decode 함수를 사용하는 내용을 여기서 다루고자 한다.

   함수를 사용하는 경우는 conventional path load인 경우에만 가능하며 direct path load
   인 경우는 적용되지 않음을 주의하자.
   함수를  사용할때 참조하고자 하는 컬럼앞에 콜론(:)을 붙이면 된다.

   SEQUENCE 를 사용하면 각각의 레코드를 로드할때 특정 컬럼을 증가할수 있다.
   단 무시되거나 잘못되어 들어가지 않은 레코드에 대해서는 증가을 하지 않기
   때문에 주의하여야 한다.

   SEQUENCE 함수의 옵션에 대해 알아보자.

    SEQUENCE(n,increment) - 지정한 n 값부터 시작하여 increment 값만큼 증가한다.

    SEQUENCE(COUNT,increment) - table에 이미 존재하는 로우들을 count한 수에서
                                시작하여 increment 값만큼 증가한다.

    SEQUENCE(MAX, increment - 해당 컬럼의 maximum 값에서 시작하여 increment
                              값만큼 증가한다.

    Examples
    ----------

    < Sequence 함수 사용 예 >

    1. sample table 생성

    create table t1
    (field1 number, field2 number, field3 varchar2(10));

 

    2. controlfile생성

    <sequence1.ctl>
    load data
    infile 'data1.dat'
    into table t1
    fields terminated by "," optionally enclosed by '"'
    (field1 SEQUENCE(MAX,1),
     field2,
     field3 )

 

    3. load할 datafile생성

    <data1.dat>

    1234, "ABC"
    3456, "CDF"

 

    4. 실행해 본다.

    $ sqlldr scott/tiger sequence1.ctl

    SQL> select * from t1;

        FIELD1     FIELD2 FIELD3
    ---------- ---------- --------------------
             1       1234 ABC
             2       3456 CDF

 

    < Decode 함수 사용 예 >

    test1 column이 'hello'이면 'goodbye'을 아니면 test1 column값을 로드하고자 한다.

    1. sample table 생성

    create table testldr
    (test1 varchar2(10), test2 varchar2(10));

 

    2. controlfile생성

    <decode1.ctl >

    Load data
    infile 'data2.dat'
    into table testldr
    fields terminated by ',' optionally enclosed by '"'
    (test1,
     test2 "decode(:test1, 'hello', 'goodbye', :test1)")

 

    3. load할 datafile생성

    <data2.dat>
    hello,""
    goodbye,""
    hey,""
    hello,""

 

    4. 실행해 본다.

    $ sqlldr scott/tiger decode1.ctl

    SQL> select * from testldr;

    SQL> select * from testldr;

    TEST1                TEST2
    -------------------- --------------------
    hello                goodbye
    goodbye              goodbye
    hey                  hey
    hello                goodbye

 

 

 

==================================================================
 SQL*LOADER에서 | (PIPE LINE)을 RECORD SEPARATOR로 사용하기
==================================================================

 

    PURPOSE
    -------

     Oracle8i부터는 , SQL*Loader을 사용할때 record terminator을 지정할 수 있게 되었다.

 

    Explanation
    -----------

     Oracle8i 이전에는 record seperator로 default로 linefeed(carriage return,
    newline 등)였다.  이전에는 VAR 또는 FIX 등의 적당한 file을 다루기 위한 옵션을
    주어야 하기 때문에 복잡한 감이 있었고 flexible하지 못했다.

     Oracle8i부터는 , SQL*Loader을 사용할때 record terminator을 지정할 수 있게
    되었다. newline 또는 carriage return 문자를 포함하는 data 또는 special 문자를
    포함하는 data를 load하고자 할때 record terminator를 hexadecimal로 지정하여 활용할 수 있다.

 

    Example
    --------

     다음의 예제는  '|' (pipe line)을 record separator로 사용한다.
     record separator를 사용하기 위해서 SQL*Loader의 control file에  'infile'절에 적당한 값을

     지정 하여야 한다.
     아래의 예는 '|' (pipe line)을 사용하기 위해서
     "str X'7c0a'"을 'infile'절에 지정하였다.

    --controlfile : test.ctl

    load data
    infile 'test.dat' "str X'7c0a'"
    into table test
    fields terminated by ',' optionally enclosed by '"'
    (col1, col2)

    --datafile:  test.dat

    1,this is the first line of the first record
    this is the second|
    2,this is the first line of the second record
    this is the second|

    SQL> desc test
     Name                                      Null?    Type
     ----------------------------------------- -------- -----------
     COL1                                               VARCHAR2(4)
     COL2                                               VARCHAR2(100)

    $  sqlldr scott/tiger control=test.ctl log=test.log

    load된 data을 보면 아래와 같이 carriage return이 들어가 있는 data가 한 column에
    제대로 들어간 것을 볼 수 있다.

    SQL> select * from test;

    COL1
    ----
    COL2
    --------------------------------------------------------------------------------
    1
    this is the first line of the first record
    this is the second

    2
    this is the first line of the second record
    this is the second

 

 

 

===========================================================================
특별한 값(SEQUENCE, 십진수, USERNAME)을 갖는 COLUMN을 LOADER로 넣기
===========================================================================

    PURPOSE
    ---------
    특별한 값(SEQUENCE, 십진수, USERNAME)을 갖는 COLUMN을 SQL*LOADER를
    이용하여 INSERT하는 방법에 대해 알아보도록 한다.

    Explanation
    -----------

    1.연산이 포함된 십진수의 load.

    은행의 경우 data가 transaction type, 계좌번호 거래액으로 될 경우,
    마지막 거래액은 함축적 의미를 가진 숫자값이다.
    아래의 예에서 실제 값은 100.73, 75.25, 820.00이다.

     DEPOSIT  10015 10073
     DEPOSIT  10012 7525
     WITHDRAWAL 10015 82000

    이것을 아래의 table로 load하려 한다.

    CREATE TABLE register
     (tx_type CHAR(15),
      acct    NUMBER,
      amt     NUMBER);

    cotrol file
     LOAD DATA
     INFILE 'month.dat'
     INTO TABLE register
     (tx_type POSITION(1:10),
     acct POSITION(13:17),
     amt POSITION(20:24) ":amt/100")

    이것을 load하면 sql*loader는 아래와 같은 insert 문을 만든다.

    INSERT INTO register (tx_type,acct,amt)
       VALUES (:data1, :data2, ":amt/100");

    그러므로 매 amt값마다 100으로 나눈 값을 얻을 수 있다.
    그러나 이 방법은 direct path 방식에서는 사용할 수 없다.

    2. database sequence를 load하기

    [예제1]

    먼저 좀 더 쉬운 방법인 position에 의해 구분되는 data file의 경우를 보자.

     - table
    CREATE TABLE load_db_seq_positional
     (seq_number NUMBER,
      data1  NUMBER,
      data2  CHAR(15);

     - sequence
    CREATE SEQUENCE db_seq
     START WITH 1
     INCREMENT BY ;
     - control file
     LOAD DATA
     INFILE *
     INTO TABLE load_db_seq_positional
     (seq_number "db_seq.nextval",
      data1  POSITION(1:5),
      data2   POSITION(6:15)
     )
     BEGINDATA
     11111AAAAAAAAAA
     22222BBBBBBBBBB

     - 결과
    select * from load_db_seq_positional;

    SEQ_NUMBER DATA1 DATA2
    ----------      -------  -----------
             1 11111  AAAAAAAAAA
             2 22222  BBBBBBBBBB

    [예제2]

    이번은 comma로 구분되는 data file이다. 여기에서 key는 file이 구분되어
    있다는 것이다.

    sql*loader는 data file에서 seq_number field값을 찾으려 할 것이다. 이
    경우 마지막 field인 seq_number값을 넣어 주어야 하는데 그 값이 없을 경우
    TRAILING NULLCOLS를 이용하여 loader에게 지정해 주어야 한다.
    trailing columns는 null or non_existent이다.

    위의 예제와 이름만 다른 (load_db_seq_delimited) table과 같은 sequence를
    사용하여 다음을 실행한다.
     - control file
     LOAD DATA
     INFILE *
     INTO TABLE load_db_seq_delimited
     FIELDS TERMINATED BY ","
     TRAILING NULLCOLS
     (data1,
      data2,
      seq_number "db_seq.nextval"
     )
     BEGINDATA
     11111,AAAAAAAAAA
     22222,BBBBBBBBBB
     - 결과
    select * from load_db_seq_delimited;

    SEQ_NUMBER DATA1 DATA2
    ----------    -------  ------
             3 11111  AAAAAAAAAA
             4 22222  BBBBBBBBBB

    sql*loader에서 위의 경우 아래와 같은 insert문을 만든다.

    INSERT INTO load_db_seq_delimiter (data1,data2,seq_number)
      VALUES (:data1,data2,"db_seq.nextval");

    위의 두방법 역시 direct path에서는 사용하지 못한다.

 

    3. sql*loader 실행 시 username을 load 하기

 

    [예제1]

    data file에서 field가 position으로 구분되어 지는 경우로 조금 쉽다.
    다음 방법은 delimiter로 구분된 것으로 좀 더 신경을 써야 한다.
    두 경우 모두 "USER" pseudo-variable을 사용하는데 만약 user ID를
    사용하려면 "UID"를 대신 사용한다.

     - table
    CREATE TABLE load_user_positional
     (username CHAR(30),
      data1  NUMBER,
      data2  CHAR(15);
     - control file
     LOAD DATA
     INFILE *
     INTO TABLE load_user_positional
     (username "USER",
      data1  POSITION(1:5),
      data2   POSITION(6:15)
     )
     BEGINDATA
     11111AAAAAAAAAA
     22222BBBBBBBBBB
     - 실행
     sqlldr scott/tiger load_user_d.ctl
     - 결과
    select * from load_user_positional;

      USERNAME  DATA1     DATA2
    ----------      -------   ----------
         SCOTT      11111       AAAAAAAAAA
         SCOTT      22222       BBBBBBBBBB

 

    [예제2]
    이 경우는 comma delimiter로 구분된 경우이다. 이경우 sql*loader는
    USERNAME field를 찾을 것이다.
    그러나 그 값이 없으므로 USERNAME FIELD는 CONTROL FILE 제일 뒤에 둔다.
    그리고 "TRAILING NULLCOLS"절로 뒤의 null또는 존재하지 않는 값을
    trailing columns라는 것을 나타내 준다.

     - table
    CREATE TABLE load_user_delimited
     (username CHAR(30),
      data1  NUMBER,
      data2  CHAR(15);
     - control file
     LOAD DATA
     INFILE *
     INTO TABLE load_user_delimited
     FIELD TERMINATED BY ","
     TRAILING NULLCOLS
     (data1,
      data2,
      username "USER"
     )
     BEGINDATA
     11111,AAAAAAAAAA
     22222,BBBBBBBBBB
     - 실행
     sqlldr jack/jack load_user_d.ctl

     - 결과
    select * from load_user_delimited;

      USERNAME   DATA1  DATA2
    ----------      -------  ----------
      JACK            11111    AAAAAAAAAA
      JACK            22222    BBBBBBBBBB

    이 loader의 작업을 insert문으로 만들면

    INSERT INTO load_user_delimiter(data1,data2,username)
       VALUES(:data1,:data2,USER);

    이 방법 역시 direct mode에서는 사용할 수 없다.