Oracle 파티션 테이블과 파티션 인덱스 (UPDATE 튜닝)

Oracle Partition UPDATE 튜닝 정리

인덱스가 DML 성능에 큰 영향을 미치므로 대량 데이터를 입력/수정/삭제 시에는 인덱스를 DROP하거나 Unusable 상태로 변경하고 작업하는 방법을 많이 활용한다 (전체의 5%이상일시)

UPDATE INFO_TABLE SET VALUE2='CHANGE'
WHERE VALUE1 = 'Y'
AND TIME < '20220101'
AND TIME > '20211130'

(INFO_TABLE은 파티션 키는 TIME 이며 PK 는 VALUE1+TIME , TIME만 으로 구성된 인덱스도 존재)

위와 같은 쿼리를 수행할시 변경해야될 레코드가 전체의 5%이상이라고 할시 인덱스와 테이블을 모두 수정하는데에 많은 시간이 필요하다.

만약 위의 경우에서 테이블이 파티션이고 인덱스도 로컬 파티션으로 생성했다면 임시세그먼트를 생성해서 원본 파티션과 바꿔치기 하는 방식으로 진행이 가능하다.

 

1. nologging 모드로 임시테이블 생성

CREATE TABLE TEMP_INFO
nologging
as
SELECT * FROM INFO_TABLE
WHERE 1 = 2

 

2. INFO_TABLE을 읽어 임시테이블에 입력하면서 VALUE2 값 수정

INSERT /*+ append */ INTO TEMP_INFO
SELECT TIME,VALUE1
       , (CASE WHENE VALUE1 == 'Y' THEN 'CHANGE' else VALUE2 end) VALUE2
FROM INFO_TABLE
WHERE TIME < '20220101'
AND TIME > '20211130'

 

3. 임시 테이블을 원본 테이블과 같은 구조로 인덱스를 생성한다. 할수 있다면 nologging 모드로 생성한다.

CREATE UNIQUE INDEX TEMP_INFO_PK ON TEMP_INFO(VALUE1,TIME) nologging;
CREATE INDEX TEMP_INFO_INDEX ON TEMP_INFO(TIME) nologging;

 

4. 변경하려는 파티션( 2021년 12월 파티션, p202112 )과 임시테이블을 Exchange 한다.

ALTER TABLE INFO_TABLE
EXCHAGNE PARTITION p202112 WITH TABLE TEMP_INFO
INCLUDING INDEXES WITHOUT VALIDATION;

 

5. 임시 테이블을 DROP한다.

DROP TABLE TEMP_INFO

 

6. 해당 파티션의 파티션 테이블과 파티션 인덱스에서 nologging 모드를 해제 한다.

ALTER TABLE INFO_TABLE MODIFY PARTITION p202112 logging;
ALTER INDEX TEMP_INFO_PK MODIFY PARTITION p202112 logging;
ALTER INDEX TEMP_INFO_INDEX MODIFY PARTITION p202112 logging;