오서비네 이글루 : [펌] PE(Partition Exchange)를 이용해 대량의 Delete를 대신하기

2009년 03월 27일

[펌] PE(Partition Exchange)를 이용해 대량의 Delete를 대신하기

From : http://www.egloos.com/egloo/insert.php?eid=e0040769 (욱짜

님의 블로그)

PE(Partition Exchange)를 이용해 대량의 Delete를 대신하기

Advanced Oracle 2008/10/31 17:43

대량의 데이터에 대한 Delete 작업이 필요한 경우가 있다. 가령 다음과 같이...

Table t1의 Column c1 값이 1인 경우에 대해 모든 Data를 삭제하고 싶다.

하지만 이 경우 대량의 Undo와 Redo Data로 인해 심각한 성능 문제가 대두된다. 그래서 이런 경우 다음과 같은 방식이 권장되기도 한다.

Table t1의 Copy 본인 Table t2를 만든다.
Table t2에 Column c1 값이 1인 경우를 제외하고 Data를 Copy한다.
Table t1을 rename(혹은 drop)하고 Table t2를 t1으로 rename한다.
Index를 기존과 똑같은 이름으로 생성한다.

위의 방법이 가끔 사용되기는 하지만 몇 가지 골치아픈 절차가 따른다. 가령 Index이름을 어떻게 맞추어 줄 것인가, Down Time을 어떻게 최소화시킬 것인가....

이 경우에 Partition Exchange를 사용하면 좀 더 간편하게 동일한 목적을 달성할 수 있다.

아래 예제를 보자.

다음과 같은 Table t1이 있다.

create table t1(c1 int, c2 int);

alter table t1 add constraint t1_pk
primary key (c1, c2);

create index t1_n2 on t1(c2);

insert into t1
select mod(level,5)+1, level
from dual
connect by level <= 10000
;

select c1, count(*)
from t1
group by c1
;

C1 COUNT(*)
---------- ----------
1 2000
2 2000
4 2000
5 2000
3 2000

우리의 목적은 Column c1의 값이 "1"인 경우의 Data를 모두 삭제하는 것이다.

이를 위해서 Table t1의 복사본인 Table t2를 만든다.

단 Partition Exchange를 사용하기 위해 하나의 Partition으로 구성한다. Column c1 값이 1이 배제되도록 Data를 생성한다. 즉 Column c1의 값이 1인 경우가 삭제된 것과 동일하다.

create table t2(c1, c2)
nologging
partition by range(c1)
(
partition p1 values less than(maxvalue)
)
as
select c1, c2 from t1
where c1 in (2, 3, 4, 5)
;

Partition Exchange를 위해 Index 구성도 통일시켜준다.

create unique index t2_n1 on t2(c1, c2) local;

alter table t2 add constraint t2_pk
primary key (c1, c2) using index;

create index t2_n2 on t2(c2) local;

다음과 같이 Table t1과 Partition t2.p1을 Exchange한다.

alter table t2
exchange partition p1
with table t1
including indexes
;

Table t1에서 Column c1의 값이 1인 경우는 완전히 삭제되었다.

select c1, count(*)
from t1
group by c1
;

C1 COUNT(*)
---------- ----------
2 2000
4 2000
5 2000
3 2000

Table t2는 Table t1으로 교체되었으므로 모든 Data를 가지고 있다.

select c1, count(*)
from t2
group by c1
;

C1 COUNT(*)
---------- ----------
1 2000
2 2000
4 2000
5 2000
3 2000

Index 또한 이름이 그대로 유지되며 VALID 상태를 유지한다.

select index_name, status
from user_indexes
where table_name = 'T1'
;

INDEX_NAME STATUS
-------------------- ------------------------
T1_PK VALID
T1_N2 VALID

Partition Exchange를 사용함으로써 훨씬 간단하게 원하는 바를 달성할 수 있다.

간단하지만 유용한 기법이라고 하겠다.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-동일조건변경허락 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

'Advanced Oracle' 카테고리의 다른 글

11g is better - 10046 Trace가 좋아졌다!!! (0)
Oracle Library Cache를 이해하기 위한 숙제 (1)	2008/11/09
[새로쓴 대용량 데이터베이스 솔루션 I]을 보고 깜짝 놀라다. (9)	2008/11/01
PE(Partition Exchange)를 이용해 대량의 Delete를 대신하기 (11)	2008/10/31
FIRST_ROWS vs. FIRST_ROWS_N 그리고 Cost의 개념 (10)	2008/10/23

=======

extremedb 2008/11/02 17:50 Modify/Delete Reply
아주 유용한 예제입니다.
PE 를 이런용도로도 사용하는 군요.^^
대용량 테이블일 경우 down time 을 더 낮추려면 Without Validation 을 적용하는것을
추가적으로 고려해야 성능이 더욱 좋아질 것입니다.
물론 PE 될 테이블의 데이터가 이미 VALID 된 상태라고 가정하고 말입니다.
좋은글 감사합니다.
Stargazer 2008/11/03 09:46 Modify/Delete Reply
생각의 전환...역시...대단하십니다..
백면서생 2008/11/03 16:27 Modify/Delete Reply
좋은 글 잘 봤습니다.~
oracler 2008/11/08 08:58 Modify/Delete Reply
오...이런,, 좋으네요 그냥 대단하시다는 말밖에 안나옵니다.

그런데 전통적인 위의 방법에서 인덱스 이름 맟추기와 다운타임 최소화가
왜 문제가 되는지 모르겠네요 ?
결국 alter index rename 과 drop, rename 시간인데
인덱스 이름 맟추는것도 문제가 없으며 모두 금방 끝나는거 아닌가여
욱짜 2008/11/08 11:35 Modify/Delete Reply
인덱스 이름의 경우 전통적인 방법에 비해서 좀 더 편하다 정도로 이해하시면 될 거 같습니다. 수동으로 인덱스 이름을 맞추어주는 경우 오타 등으로 인해 생각지 못한 문제가 발생하는 경우가 종종 있습니다.

Down Time의 경우에는 DML을 Blocking하는 DDL Operation의 회수가 여러번에서 1번으로 줄어든다 정도의 의미로 이해하시면 될 거 같습니다. 이로 인해 시간이 크게 절약된다는 의미는 아니구요.