대량 데이터 delete 작업 시 tip(database.sarang.net)

OS환경 : Oracle Linux6.8(64bit)

DB 환경 : Oracle Database 11.2.0.4

질문 : 

데이타 삭제 관련한 문의 입니다..

대량의 데이타 삭제시

예를 들어 300~900만건 중 몇십만건 또는 백만건 정도의

데이타를 Where절 이하에 조건을 주어 삭제하려는 작업을

하려고 합니다..

하지만 Delete시에 Rollback Segment를 타기 때문에..

DB서버에 많은 IO부하를 일으키고 수행시간도 그 많큼 많이 걸리는데요..

Backup을 받았고 또 궂이 복구의 필요성도 없는 데이타 이기에

 

Rollback Segmanet를 타지 않고 대량의 데이타를 지울 수 있는

방법을 찾고 있습니다..

 

현재 Archive mode이기 때문에 불필요한 Archive화일도 쌓이게 되구요..

좋은 방법 없을까요?

많은 고수님들의 조언을 부탁드립니다.

답변 :

1. session 레벨로 파라미터 변경

SQL> alter session enable parallel dml;
SQL> alter session set sort_area_size = 1000000;

 

2. 임시 데이터용 테이블 생성

create table temp_table
storage(initial 50m) -- 충분한 extent 할당으로 동적 exent 할당 예방
tablespace another_disk_ts -- 서로 다른 디스크에 저장하여 I/O 분산
nologging -- log양을 최소화시킴
as
select * from the_table
where 1=2; -- table 정의만 생성

3. 임시 테이블로 병렬 insert 작업 진행

SQL> 
insert /*+ parallel(temp_table, 4) */ -- 병렬 direct path loading
into temp_table
--nologging 이 nologging 옵션은 적용되지 않음(그냥 alias일뿐이라 주석처리함)
select * from the_table
where 삭제되지 않는 row만;

4. 이전 테이블 truncate

SQL> truncate table the_table reuse storage; -- 저장공간은 그대로 두고 row만 삭제

5. 임시테이블 데이터 원본테이블로 insert

SQL> 
insert /*+ parallel(the_table, 4) */
into the_table
--nologging 이 nologging 옵션은 적용되지 않음(그냥 alias일뿐이라 주석처리함)
select * from temp_table;

위 방법은 3가지 원리를 이용한 것입니다.

1. delete보다 insert가 훨씬 적은양의 rollback 및 redolog 양을 발생시킴.

2. delete보다 truncate가 훨씬 빠름.

3. parallel direct path loading 및 nologging을 이용한 속도향상

delete되는 양이 많다면 위 방법이 효과가 있을 것입니다.

parallel operation은 CPU 가 많고 메모리도 충분할 때 효과가 있습니다.

효과가 있으면 답신 주시기 바라겠습니다.

그럼.

 

박광서.

이방법 사용후에는 꼭 full backup을 진행 해주어야한다.

결과값 : 

대량delete테스트.txt

참조 : http://database.sarang.net/?inc=read&aid=12874&criteria=oracle&subcrit=&id=&limit=20&keyword=delete+nologging&page=1