파티션 테이블 로컬인덱스 생성
DB CAFE
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 로컬 인덱스 생성[편집]
대용량의 파티션된 로컬 인덱스를 한꺼번에 생성할 때 부하도 크고 시간도 오래 걸린다. 다음과 같이 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 (...) ;
2 UNUSABLE 파티션 테이블 테스트[편집]
-- 파티션 테이블 UNUSABLE 테스트 -- 1.테이블 생성 CREATE TABLE KCY_UNIQUE_IX_TEST (
STR_CD VARCHAR2(5 BYTE) NOT NULL, AGG_DT VARCHAR2(8 BYTE) NOT NULL, ITEM_CD VARCHAR2(14 BYTE) NOT NULL
) TABLESPACE TS_MIG NOLOGGING PARTITION BY RANGE (AGG_DT) (
PARTITION PT_1804 VALUES LESS THAN ('20180499') NOLOGGING NOCOMPRESS TABLESPACE TS_MIG, PARTITION PT_1805 VALUES LESS THAN ('20180599') NOLOGGING NOCOMPRESS TABLESPACE TS_MIG, PARTITION PT_1806 VALUES LESS THAN ('20180699') NOLOGGING NOCOMPRESS TABLESPACE TS_MIG, PARTITION PT_1807 VALUES LESS THAN ('20180799') NOLOGGING NOCOMPRESS TABLESPACE TS_MIG, PARTITION PT_1808 VALUES LESS THAN ('20180899') NOLOGGING NOCOMPRESS TABLESPACE TS_MIG, PARTITION PT_1809 VALUES LESS THAN ('20180999') NOLOGGING NOCOMPRESS TABLESPACE TS_MIG, PARTITION PT_1810 VALUES LESS THAN ('20181099') NOLOGGING NOCOMPRESS TABLESPACE TS_MIG, PARTITION PT_1811 VALUES LESS THAN ('20181199') NOLOGGING NOCOMPRESS TABLESPACE TS_MIG, PARTITION PT_1812 VALUES LESS THAN ('20181299') NOLOGGING NOCOMPRESS TABLESPACE TS_MIG, PARTITION PT_MAX VALUES LESS THAN (MAXVALUE) NOLOGGING NOCOMPRESS TABLESPACE TS_MIG
) NOCOMPRESS NOCACHE PARALLEL 8 MONITORING;
-- 2. UNIQUE 인덱스 생성 CREATE UNIQUE INDEX UX_KCY_UNIQUE_IX_01 ON KCY_UNIQUE_IX_TEST(ITEM_CD,AGG_DT)
LOCAL ( PARTITION PT_1804, PARTITION PT_1805, PARTITION PT_1806, PARTITION PT_1807, PARTITION PT_1808, PARTITION PT_1809, PARTITION PT_1810, PARTITION PT_1811, PARTITION PT_1812, PARTITION PT_MAX ) UNUSABLE ;
-- 3.데이터 입력 INSERT INTO KCY_UNIQUE_IX_TEST (STR_CD,AGG_DT,ITEM_CD) VALUES ('A','20180401','2222');
-- 4.UNUSABLE 조회 SELECT INDEX_OWNER,INDEX_NAME,PARTITION_NAME,STATUS
FROM DBA_IND_PARTITIONS WHERE INDEX_OWNER = 'MIG_ADM' AND STATUS='UNUSABLE';
-- 5.인덱스 리빌드 ALTER INDEX MIG_ADM.UX_KCY_UNIQUE_IX_01 REBUILD PARTITION PT_1804;
-- 5. 인덱스 REBUILD 처리DDL 생성 SELECT 'ALTER INDEX '||INDEX_OWNER||'.'||INDEX_NAME||' REBUILD PARTITION '||PARTITION_NAME||';'
FROM DBA_IND_PARTITIONS WHERE STATUS='UNUSABLE';
-- 6. 인덱스 UNUSABLE 파티션 삭제 SELECT 'DROP INDEX '||INDEX_OWNER||'.'||INDEX_NAME||' REBUILD PARTITION '||PARTITION_NAME||';'
FROM DBA_IND_PARTITIONS WHERE STATUS='UNUSABLE';
SELECT *
FROM KCY_UNIQUE_IX_TEST;
-- 6. 테이블 UNUSABLE 상태로 변경 ALTER INDEX MIG_ADM.UX_KCY_UNIQUE_IX_01 MODIFY PARTITION PT_1804 UNUSABLE;