오라클 DB에서 대량의 데이터를 빠르게 삭제하는 방법

지난번에 포스팅한 'RDB 대용량 테이블의 성능향상을 위한 팁'에 이어서 오늘은 대용량의 테이블에서 특정 조건을 만족하는 대량의 데이터를 빠르게 삭제하는 방법에 대해서 설명해 보겠다.

컨설팅을 진행하다보면 테이블의 데이터를 전혀 지우지 않아 문제가 되는경우를 종종 보게 된다. 이러한 시스템은 어떠한 타당한 이유가 있어서 그렇게 만들어졌다기 보다는 단순히 물리 삭제할 경우 발생하는 영향을 고려해야 하는 것에 부담을 느껴 임시방편적으로 논리 삭제 플러그를 세우는 것으로 대응하다가 시간의 경과와 함께 점차 늘어나는 데이터가 성능문제로 이어지는 경우가 대부분을 차지한다.

테이블 전체를 삭제하는것은 Truncate table이나 Drop table을 이용해 빠르고 손쉽게 가능하다. 문제는 특정 조건을 만족하는 대량의 데이터를 삭제하는 경우인데, 조건이 단순한 논리삭제 플러그라 할 지라도 데이터가 수억건에 이르게 되면 처리에 상당한 시간이 걸리게 된다.

이러한 데이터 라이프 사이클 관리에서 오는 성능 문제는 엄연히 기술적 채무에 해당하는데, 이전 포스팅에서 밝힌바와 같이 문제가 표면화 되는 즈음에는 원인을 제공한 담당자는 이미 자리를 뜬지 오래이고 현재 담당자는 이 문제가 얼마만큼 심각한지에 대해서 인식이 부족한 경우가 많아 서툴게 작업을 진행하다 낭패를 보는경우가 많다.

이제부터 소개하는 방법은 레코드 수가 수억건 이상의 대용량 테이블에서 3할 이상의 데이터를 삭제해야 하는 경우에 매우 유용한 것으로,  일반적인 delete문을 이용한 삭제에 비해 수배에서 수십배의 효율을 보인다.

대량 데이터 고속 삭제 절차

1. 대상 테이블의 DDL을 이용해 삭제 대상외의 데이터를 담을  임시테이블을 작성. (이 단계에서는 테이블만 작성하고 인덱스 및 제약조건등의 설정은 하지 않는다.)
2. Insert into Select를 이용해 비삭제 대상의 레코드를 임시테이블에 복사.
   이때 APPEND NOLOGGING PARALLEL hint를 이용하면 속도를 비약적으로 향상시킬 수 있다. Create as Select를 이용하면 1번을 생략하고 테이블 생성과 복사를 한번에 할 수도 있다.
3. 오리지널 테이블에 설정된 외부키,인덱스, Synonym, Materialized View등을 해제.
4. 오리지널 테이블 드롭(또는 Truncate)
5. 복사 테이블을 리네임.
6. 인덱스, 외부키, Synonym, Materialized View등을 설정.

위의 방법으로 레코드를 삭제할 경우 자동적으로 테이블 세그먼트와 인덱스의 재편성이 이루어져 최적의 성능을 발휘 할 수 있게 된다.