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

Oracle Partition DELETE 튜닝 정리

DELETE FROM INFO_TABLE
WHERE TIME < '20220101'

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

위의 테이블에서 테이블과 인덱스를 실시간으로 관리하는 것은 많은 시간을 필요로 한다.

인덱스를 Drop 했다가 재생성하는 방법도 모든 인덱스를 재생성해야 하므로 시간이 많이 소요된다.

 

* Delete의 과정

1. 레코드 삭제

2. 테이블 레코드 삭제에 대한 Undo Logging

3. 테이블 레코드 삭제에 대한 Redo Logging

4. 인덱스 삭제

5. 인덱스 레코드 삭제에 대한 Undo Logging

6. 인덱스 레코드 삭제에 대한 Redo Logging

7. Undo에 대한 Redo logging

위와 같은 과정을 삭제 시 모두 수행하여야 되기 때문에 레코드를 탐색하는 비용뿐만 아니라 인덱스의 개수에 따른 Logging과정을 필요로 한다.

 

만약 파티션 단위로 대량의 데이터를 삭제를 수행한다면 간단하게 처리할 수 있다.

ALTER TABLE INFO_TABLE DROP PARTITION p202112

//Oracle 11g 이상에서 지원
ALTER TABLE INFO_TABLE DROP PARTITION for('p202112');

 

파티션 키에 따른 데이터를 일괄로 삭제하는 것이 아닌 다른 조건이 존재하는 경우 Truncate를 이용해 삭제하는 것을 고려해 볼 수 있다.

 

1. 임시 테이들을 생성하고 남길 데이터만 복제한다.

CREATE TABLE TEMP_INFO
AS
SELECT *
FROM INFO_TABLE
WHERE TIME >= '20211201' AND TIME < '20220101'
AND VALUE1 = 'Y'

2021-12월 데이터 중에 VALUE1 = 'Y'가 아닌 것을 삭제하려고 할 때 VALUE1 = 'Y'인 레코드만 임시 테이블에 저장한다.

 

2. 삭제 대상 테이블 파티션을 Trucate 한다.

ALTER table INFO_TABLE TRUNCATE PARTITION p202112

3. 임시 테이블을 복제해 둔 데이터를 원본 테이블에 insert 한다. (로컬 파티션 인덱스로 자동으로 인덱스 관리)

INSERT INTO INFO_TABLE
SELECT * FROM TEMP_INFO

4. 임시 테이블을 Drop 한다.

DROP table TEMP_INFO

 

* 서비스 중단 없이 파티션을 Drop, Truncate 할 수 있는 조건

1. 파티션 키와 삭제 기준 컬럼이 일치해야 됨

EX) INFO_TABLE 의 파티션키 TIME , 삭제 기준 TIME

 

2. 파티션 단위와 파티션 삭제 단위가 일치

EX) 월 단위 파티션닝 되어있으며 삭제 기준이 2021년 12월

 

3. 모든 인덱스가 로컬 파티션 인덱스이여야 함

EX) PK인덱스는 UNIQUE 인덱스 이므로 파티션 키가 포함되어 있어야 하며 로컬 인덱스로 생성, TIME 인덱스는 로컬 인덱스