행위

파티션 테이블 로컬인덱스 생성

DB CAFE

Dbcafe (토론 | 기여)님의 2021년 1월 7일 (목) 10:43 판
thumb_up 추천메뉴 바로가기

로컬 인덱스 생성[편집]

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

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

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

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

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

ALTER SESSION SET WORKAREA_SIZE_POLICY    = MANUAL;
ALTER SESSION SET SORT_AREA_SIZE          = 2147483647;
ALTER SESSION SET SORT_AREA_RETAINED_SIZE = 2147483647;
ALTER SESSION SET HASH_AREA_SIZE          = 2147483647;

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

ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE;

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

ALTER SESSION ENABLE PARALLEL DDL;
ALTER SESSION ENABLE PARALLEL DML;
ALTER SESSION SET WORKAREA_SIZE_POLICY=MANUAL;
ALTER SESSION SET SORT_AREA_SIZE=512000000;
ALTER SESSION SET SORT_AREA_RETAINED_SIZE=512000000;
ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=256;
ALTER SESSION SET "_sort_multiblock_read_count"     = 128;
ALTER SESSION SET "_db_file_optimizer_read_count"   = 128;
ALTER SESSION SET "_db_file_exec_read_count"        = 128;
ALTER SESSION SET "_serial_direct_read"             = TRUE;

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

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(최근 사용)되는 파티션부터 생성

alter_index.gif 

ALTER INDEX IX_TBABC10_L1 REBUILD PARTITION PT_TBABC10_999999
 TABLESPACE TS_TBABC10_999999 PARALLEL(DEGREE 8) NOLOGGING;

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

특히, PK(PRIMARY KEY)를 생성하는 경우 ALTER TABLE ... ADD CONSTRAINTS ... 명령을

이용하면 위와같이 작업할 수 없다.

다음과 같은 순서로 작업하면 된다. 

1) CREATE UNIQUE INDEX <PK_INDEX명> ON <TABLE명> (..컬럼..) 
      TABLESPACE <테이블스페이스명> LOCAL ( PARTITION <파티션명> TABLESPACE <테이블스페이스명> ,
                                            PARTITION <파티션명> TABLESPACE <테이블스페이스명> ,
                                            ....................
                                          ) 
       UNUSABLE; -- INDEX 미사용으로 생성해야 속도 빠름
2) ALTER INDEX <PK_INDEX명> REBUILD PARTITION <PARTITION명> [ TABLESPACE <테이블스페이스명> ] ;  -- 인덱스 REBUILD 
3) ALTER INDEX <PK_INDEX명> NOPARALLEL LOGGING;  -- 인덱스 NOPARALLEL LOGGING 모드로 변경 
4) ALTER TABLE <TABLE명> ADD CONSTRAINTS <PK_INDEX명> PRIMARY KEY (...PK컬럼..) ; --
  • 예시)
-- UNIQUE INDEX 생성   
CREATE UNIQUE INDEX MIG_ADM.PK_MA_XXXX_CLASS ON MIG_ADM.TB_MA_XXXX_CLASS
(CLSS_CD)
NOLOGGING
TABLESPACE TS_MIG
PARALLEL 8
UNUSABLE
;
--
ALTER INDEX MIG_ADM.PK_MA_XXXX_CLASS REBUILD ....;
-- PK 생성 

ALTER TABLE MIG_ADM.TB_MA_XXXX_CLASS ADD (
  CONSTRAINT PK_MA_XXXX_CLASS
  PRIMARY KEY
  (CLASS_CD)
  USING INDEX MIG_ADM.PK_MA_XXXX_CLASS
  ENABLE VALIDATE)
;
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=파티션_테이블_로컬인덱스_생성&oldid=5407"