SQL튜닝이론 - 파티션을 활용한 대량 DML 튜닝 방법( exchanging partition, drop partition for, truncate partition, modify partition, rebuild partition)

 

SQL튜닝이론 - 파티션을 활용한 대량 DML 튜닝 방법( exchanging partition, drop partition for, truncate partition, modify partition, rebuild partition)

 

파티션을 활용한 대량 UPDATE 튜닝

 

인덱스가 DML 성능에 큰 영향을 미치기때문에 대량 데이터를 CRUD 할때는 인덱스를 DROP 하거나 UNUSABLE 상태로 변경한 후 작업하는 방법을 활용합니다. CRUD 할 데이터의 비중이 전체의 5% 를 넘으면 인덱스 없이 작업한 후 재생성하는 것이 유리합니다.

 

-- 거래테이블이 10억건이 저장돼있을때 UPDATE 문을 이용해 2015년 1월 1일 전에 발생한 거래 데이터를 수정하려힙니다.
-- 거래 일자 조건을 만족하면서 상태코드가 'ZZZ 가 아닌 데이터가 5000만 건 일때, 테이블 레코드 5000만 건을 변경하면서 거래_X2 인덱스 까지 실시간으로 관리하려면 많은 시간이 소요됩니다. 
update 거래 set 상태코드 = 'ZZZ'
where 상태코드 <> 'ZZZ'
and 거래일자 < '20150101'

 

파티션 EXCHANGE를 이용한 대량 데이터 변경

 

파티셔닝 된 테이블의 인덱스가 로컬 인덱스라면 수정된 값을 임시 세그먼트로 만들어 원본 파티션과 바꿔치기 하는 방식을 사용할 수 있습니다. 작업순서는 다음과 같습니다.

 

-- 1. 수정 데이터 저장을 위한 임시 테이블을 생성합니다. 되도록 nologging 모드로 생성합니다.
CREATE TABLE 거래_T
NOLOGGING
AS
SELECT * FROM 거래 WHERE 1=2;

-- 2. 거래 데이블의 변경대상 데이터를 읽어 임시 테이블에 입력하면서 상태코드 값을 수정합니다
INSERT INTO 거래_T
SELECT 고객번호, 거래일자, 거래순번, ... , (CASE WHEN 상태코드 <> 'ZZZ' ELSE 상태코드 END ) 상태코드
FROM 거래
WHERE 거래일자 < '20150101';

-- 3. 원본 테이블의 인덱스와 같은 구조로 임시 테이블의 인덱스를 생성합니다. 되도록 nologging 모드로 생성합니다.
create unique index 거래_t_pk on 거래_t (고객번호, 거래일자, 거래순번) nologging;
create index 거래_t_x1 on 거래_t(거래일자, 고객번호) nologging;
create index 거래_t_x2 on 거래_t(상태코드, 거래일자) nologging;

-- 4. 값의 범위가 2014년 12월 인 파티션과 임시 테이블을 교체합니다.
alter table 거래
exchange partition p201412 with table 거래_t
including indexes without validation;

-- 5. 임시 테이블을 DROP 합니다.
drop table 거래_t;

-- 6. 파티션을 logging 모드로 전환합니다. 
alter table 거래 modify partition p201412 logging;
alter index 거래_pk modify partition p201412 logging;
alter index 거래_x1 modify partition p201412 logging;
alter index 거래_x2 modify partition p201412 logging;

 

파티션을 활용한 대량 DELETE 튜닝

 

대량의 데이터를 삭제할때도 인덱스를 실시간으로 관리하면 많은 시간이 소모됩니다.

 

delete from 거래
where 거래일자 < '20150101'

 

DELETE 연산은 아래와 같은 부수작업을 동반하므로 느립니다.

 

1. 테이블 레코드 삭제
2. 테이블 레코드 삭제에 대한 UNDO LOGGING
3. 테이블 레코드 삭제에 대한 REDO LOGGING
4. 인덱스 레코드 삭제
5. 인덱스 레코드 삭제에 대한 UNDO LOGGING
6. 인덱스 레코드 삭제에 대한 REDO LOGGING
7. UNDO에 대한 REDO LOGGING

추가적으로, 각 인덱스 레코드를 찾아가는 수직 탐색이 발생합니다.

 

파티션 DROP을 이용한 대량 데이터 삭제

 

테이블이 삭제 조건절(거래일자)을 기준으로 파티셔닝되어 있고 인덱스가 로컬 파티션이라면 파티션 DROP 을 통해 아주 빠르게 대량데이터를 삭제할 수 있습니다.

 

alter table 거래 drop partition p201412;

-- 오라클 11g 부터 아래와 같이 값을 이용해 대상 파티션을 지정할 수도 있습니다.
alter table 거래 drop partition for ('20141201')

 

파티션 TRUNCATE 를 이용한 대량 데이터 삭제

 

삭제할 데이터가 대량데이터라면 삭제하지 않을 데이터만 백업했다가 재입력하는 방식을 고려할 수 있습니다.

 

-- 1. 임시 테이블을 생성해 남길 데이터만 복제합니다.
create table t
as
select *
from 거래
where 거래일자 >= '20150101'
and 상태코드 = 'ZZZ'

-- 2. 삭제 대상 테이블 파티션을 TRUNCATE 합니다.

alter table 거래 truncate partition p201412;

-- 오라클 11g 부터 아래와 같이 값을 이용해 대상 파티션을 지정할 수 있습니다.
alter table 거래 truncate partition for ('20141201');

-- 3. 임시 테이블의 데이터를 원본 테이블에 복제합니다. 
insert into 거래
select * from 거래_t;

-- 4. 임시 테이블을 drop 합니다.
drop table 거래_t;

 

서비스의 중단 없이 파티션을 DROP 혹은 TRUNCATE 하려면 아래 조건을 모두 만족해야 합니다.

1. 파티션 키와 조건 컬럼이 일치해야합니다.
2. 파티션 단위와 조건 컬럼의 단위가 일치히야 합니다.
3. 모든 인덱스가 로컬 파티션 인덱스여야 합니다.

 

파티션을 활용한 대량 INSERT 튜닝

 

비파티션 테이블의 경우

 

비파티션 테이블에 손익분기점이 넘는 대량 데이터를 INSERT 하려면, 인덱스를 UNUSABLE 했다가 복구하는 방식이 더 효율적일 수 있습니다.

 

alter table target_t nologging

-- 인덱스를 unusable 상태로 전환한다.
alter index target_t_x01 unusable;

-- append 힌트를 통해 DIRECT PATH INSERT 방식으로 대량 데이터를 입력합니다.
insert /*+append*/ into target_t
select * from source_t

-- 테이블을 nologging 모드로 한 후 인덱스를 재생성합니다.
alter index target_t_x01 rebuild nologging

-- nologging 상태인 테이블과 인덱스를 logging 상태로 복구합니다.

alter table target_x logging;
alter index target_t_x01 logging;

 

파티션 테이블의 경우

 

초대용량 인덱스의 경우 재생성하는 부담이 크기때문에 인덱스를 그대로 둔 상태에서 INSERT 하는 것이 좋습니다. 테이블이 파티셔닝 상태여야하고 인덱스도 로컬 파티션 인덱스라면 가장 효율적입니다. 파티션 단위로 인덱슬르 재생성할 수 있기 때문입니다.

 

-- 데이터 입력 대상 파티션을 Nologging 모드로 변경합니다.
alter table target modify partition p_201712 nologging;

-- 해당 파티션과 메칭되는 인덱스 파티션을 비활성화 합니다.
alter index target_t_x01 modify partition p_201712 unusable;

-- 초대량 데이터를 DIRECT PATH INDSET 방식으로 대량 데이터를 입력합니다.
insert /*+append*/ into target_t
select * from source_t where dt between '20171201' and '20171231';

-- 테이블이 Nologging인 상태에서 인덱스 파티션을 재생성합니다.
alter index target_t_x01 rebuild partition p_201712 nologging

-- nologging 상태의 테이블 파티션과 인덱스 파티션을 logging 모드로 전환합니다.
alter table target_t modify partiton p_201712 logging;
alter index taret_t_x01 modify partiton p_201712 logging;

 

# exchanging partition

# drop partition for

# truncate partition

# modify partition

# rebuild partition