자주 사용하는 오라클 DML 정리

/***********************************/

/******* 파티션 테이블 생성 ******/

/**********************************/

CREATE TABLE HEO.MY_CONTENTS (

    STRD_DT VARCHAR2(8) NOT NULL

    , CNTNTS_ID VARCHAR2(10) NOT NULL

    , SUBJECT VARCHAR2(50)

    , CONTENTS VARCHAR2(4000)

    , LOAD_DTTM VARCHAR2(14)

)

TABLESPACE TS_HEO_DAT

PARTITION BY RAGNE ( 'STRD_DT' ) (

    PARTITION PT_MIN VALUE LESS THAN ( '20181231' )

    , PARTITION PT_20190101 VALUE LESS THAN ( '20190102' )

    , PARTITION PT_20190102 VALUE LESS THAN ( '20190103' )

);

  1. 특징

      - 하나의 테이블을 파티션으로 구분하여 가용성을 높여주고 데이터의 관리 및 접근을 용이하게 해줌.

      - 대용량의 데이터를 처리하는 테이블에서 사용하는 것을 권장.

      - 인덱스가 파티션 레벨로 적용되어 일반 테이블보다 빠른 탐색이 가능하지만, 조회 조건에 파티션 기준 컬럼이 없다면 속도는 보장할 수 없음.

      - 파티션을 생성할 수 있는 개수가 이론적으로는 65,535개로 제한되어 있으며 일부에서는 실질적으로 10,000개까지만 지원된다고 함.

         ( 본인은 약 2만개까지 무리없이 생성해 보았음, oracle 11g enterprise 기준 )

      - 병렬(PARALLEL)로 처리하면 더 좋은 퍼포먼스 보여줌.

 

  2. 파티션 생성

      - ALTER TABLE TABLE_NAME [ADD|REMOVE] PARTITION [PARTITION_NAME] [LESS THAN] ( NEXT_VALUE );

      - LESS THAN: 기준 컬럼의 데이터가 [NEXT_VALUE] 이하의 값인 경우 [PARTITION_NAME] 파티션에 데이터가 적재됨.

 

  3. 파티션 나누기

      - ALTER TABLE TABLE_NAME SPLIT PARTITION PREV_PART_NM AT ('NEW_VALUE') INTO (PARTITION NEW_PART_NM , PARTITION PREV_PART_NM )

      - 이미 생성된 파티션 사이에는 ADD 할 수 없으므로 SPLIT으로 이미 생성된 파티션을 나누어야 함.

      - 이전 파티션(PREV_PART_NM)을 새로운 값(NEW_VALUE)을 가진 파티션(NEW_PART_NM)으로 나누기.

 

  4. 조회 방법

      - SELECT * FROM HEO.MY_CONTENTS PARTITION ( PT_20190101 );

      - SELECT * FROM HEO.MY_CONTENTS WHERE STRD_DT = '20190101';

   

 

/***********************************/

/********** 통계자료 생성 *********/

/***********************************/

ANALYZE TABLE [SCHEMA].[TABLE_NAME] [COMPUTE|ESTIAMTE] STATISTICS;

ANALYZE TABLE [SCHEMA].[TABLE_NAME] PARTITION ( PARTITION_NAME ) [COMPUTE|ESTIAMTE] STATISTICS;

ANALYZE INDEX [SCHEMA].[INDEX_NAME] [COMPUTE|ESTIAMTE] STATISTICS;

 

  1. 특징

      - 삽입(INSERT)와 삭제(DELETE)가 빈번하게 발생하는 경우에 주로 사용.

  2. OPTION

      - COMPUTE: 통계자료를 생성하는 속도는 빠르지만, 분석률이 떨어짐. 주로 1만건 이상의 통계자료를 생성할 때 사용.

      - ESTIMATE: 통계자료를 생성하는 속도는 느리지만, 정확한 통계자료를 생성.

 

 

/***********************************/

/**************** 힌트 *************/

/***********************************/

/*+ USE_HASH( T1 T2 ) */

  - 옵티마이저가 강제로 Hash Join을 하도록 설정.

  - 대용량 테이블을 조인할 때는 Nasted Loops 보다 Hash Join이 좋은 성능을 발휘 한다.

  - 파티션 테이블을 Hash Join하는 경우 Partition Index가 Full Scan타는 경우가 있으므로 조건을 잘 확인하여 되도록 Single Range를 타도록 유도해야한다.

 

/*+ INDEX( T1 INDEX_NAME ) */

  - 강제로 입력한 INDEX를 타게 설정함.

 

/*+ PARALLEL( 4 ) */

  - 4개의 쓰레드로 병럴처리하도록 설정.

 

/*+ PARALLEL( 4 ) PARALLEL_INDEX( T1 INDEX_NAME ) */

  - 4개의 쓰레드로 병렬처리하며 인덱스에 접근하도록 설정.

 

/*+ PARALLEL( 4 ) PARALLEL_INDEX( T1 INDEX_NAME ) INDEX_DESC( T1 INDEX_NAME ) */

  - 4개의 쓰레드로 병렬처리하며 인덱스를 내림차순으로 접근하도록 설정.

 

 

 

/** 끄적끄적 **/

- 조건문에 IN , EXISTS를 택할 때. DRIVING 테이블의 데이터가 조건 테이블보다 데이터 양이 많다면 IN이 더 효율이 좋음.