"파티션 테이블 로컬인덱스 생성"의 두 판 사이의 차이

DB CAFE

2018년 8월 27일 (월) 17:32 판

추천메뉴 바로가기

DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
튜닝 { 오라클 튜닝 목록 }
모델링 { 데이터 모델링 가이드 }

대용량의 파티션된 로컬 인덱스를 한꺼번에 생성할 때 부하도 크고 시간도 오래 걸린다.
다음과 같이 UNUSABLE 옵션으로 생성한 다음 각 로컬 인덱스들을 REBUILD하면
부하도 적고 시간도 절약된다.

1.(필요시) TEMP 테이블스페이스의 크기 늘려줌

  - 메모리에서 정렬이 모두 이루어지지 않는 경우 TEMP 테이블스페이스의 크기도 늘려줘야 함
  - 임시 테이블스페이스를 별도의 큰 tempfile(datafile이 아닌)로 구성(시간 단축)
  - 오라클 사용자의 TEMPORARY TABLESPACE IMSI로 변경

2.(필요시) PGA 영역의 크기를 늘려줌

  - init(spfile)의 Parameter 조정
  - HASH_AREA_SIZE의 1/2을 SORT_AREA_SIZE로 사용 가능

3.UNUSABLE된 인덱스를 ACCESS하지 않도록 설정(9i 이상)

SQL> ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE;

4.PARALLEL이 가능하도록, SORT영역을 크게, 한번에 읽는 블록의 갯수가 많도록 설정

SQL> ALTER SESSION ENABLE PARALLEL DDL;
   SQL> ALTER SESSION ENABLE PARALLEL DML;
   SQL> ALTER SESSION SET WORKAREA_SIZE_POLICY=MANUAL;
   SQL> ALTER SESSION SET SORT_AREA_SIZE=512000000;
   SQL> ALTER SESSION SET SORT_AREA_RETAINED_SIZE=512000000;
   SQL> ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=256;

5.INDEX 생성(UNUSABLE 옵션 적용)

SQL> CREATE INDEX IX_TBABC10_L1 ON TBABC10(YM, PROC_CD, PROC_STAT) 
               LOCAL (
                      PARTITION PT_TBABC10_200711 ... ,
                      PARTITION PT_TBABC10_200712 ... ,
                      PARTITION PT_TBABC10_200801 ... ,
                      PARTITION PT_TBABC10_200802 ... ,
                      PARTITION PT_TBABC10_200803 ... ,
                      PARTITION PT_TBABC10_999999 ...  
                    )
                UNUSABLE ;

6.각 파티션별로 REBUILD

  가장 많이 ACCESS(최근 사용)되는 파티션부터 생성

SQL> ALTER INDEX IX_TBABC10_L1 REBUILD PARTITION PT_TBABC10_999999
                      TABLESPACE TS_TBABC10_999999 PARALLEL(DEGREE 8) NOLOGGING;
   SQL> ALTER INDEX IX_TBABC10_L1 REBUILD PARTITION PT_TBABC10_200803 
                      TABLESPACE TS_TBABC10_200803 PARALLEL(DEGREE 8) NOLOGGING;
                :
   SQL> ALTER INDEX IX_TBABC10_L1 NOPARALLEL LOGGING;

특히, PK(PRIMARY KEY)를 생성하는 경우 ALTER TABLE ... ADD CONSTRAINTS ... 명령을 이용하면 위와같이 작업할 수 없다. 그러므로 다음과 같은 순서로 작업하면 된다.

1) CREATE UNIQUE INDEX ... UNUSABLE;
2) ALTER INDEX ... REBUILD ... ;
3) ALTER INDEX ... NOPARALLEL LOGGING;
4) ALTER TABLE ... ADD CONSTRAINTS ... PRIMARY KEY (...) ;

@@ 15번째 줄: / 15번째 줄: @@
 .UNUSABLE된 인덱스를 ACCESS하지 않도록 설정(9i 이상)
+<source lang=sql>
     SQL> ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE;
+</source>
 .PARALLEL이 가능하도록, SORT영역을 크게, 한번에 읽는 블록의 갯수가 많도록 설정
+<source lang=sql>
     SQL> ALTER SESSION ENABLE PARALLEL DDL;
     SQL> ALTER SESSION ENABLE PARALLEL DML;
@@ 24번째 줄: / 26번째 줄: @@
     SQL> ALTER SESSION SET SORT_AREA_RETAINED_SIZE=512000000;
     SQL> ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=256;
+</source>
 .INDEX 생성(UNUSABLE 옵션 적용)
+<source lang=sql>
     SQL> CREATE INDEX IX_TBABC10_L1 ON TBABC10(YM, PROC_CD, PROC_STAT)
                 LOCAL (
@@ 36번째 줄: / 39번째 줄: @@
                      )
                  UNUSABLE ;
+</source>
 .각 파티션별로 REBUILD
     가장 많이 ACCESS(최근 사용)되는 파티션부터 생성
+<source lang=sql>
     SQL> ALTER INDEX IX_TBABC10_L1 REBUILD PARTITION PT_TBABC10_999999
                        TABLESPACE TS_TBABC10_999999 PARALLEL(DEGREE 8) NOLOGGING;
@@ 45번째 줄: / 49번째 줄: @@
                  :
     SQL> ALTER INDEX IX_TBABC10_L1 NOPARALLEL LOGGING;
+</source>
 특히, PK(PRIMARY KEY)를 생성하는 경우 ALTER TABLE ... ADD CONSTRAINTS ... 명령을
 이용하면 위와같이 작업할 수 없다. 그러므로 다음과 같은 순서로 작업하면 된다.
+<source lang=sql>
 ) CREATE UNIQUE INDEX ... UNUSABLE;
 ) ALTER INDEX ... REBUILD ... ;
 ) ALTER INDEX ... NOPARALLEL LOGGING;
 ) ALTER TABLE ... ADD CONSTRAINTS ... PRIMARY KEY (...) ;
+</source>