대량 데이터를 Update 또는 Delete 작업하는 경우 많은 시간이 소요됩니다.
Update/Delete 할때마다 기존(변경전/삭제전) 데이터를 Undo Segment 와 Redo Log 에 Writing 해야 하기 때문입니다.
그래서 Update/Delete 해야할 데이터량이 많은 경우에는 CTAS(Create Table As Select) 구문으로 변경해서 작업하는 것이 훨씬 빠르고 유리합니다.
얼마나 빨라지는지 한번 테스트 해보겠습니다.
먼저, 테스트할 대용량 테이블을 생성합니다.
create table TEST1
as
select empno, salary, deptno,
lpad(big_ename, 3000, big_ename) as big_ename,
lpad(big_addr, 3000, big_addr) as big_addr
from ( select level + 10000000 as empno,
mod(level,1000) + 10000 as salary,
mod(level,20) as deptno,
chr(97+mod(level,26)) as big_ename,
chr(65+mod(level,26)) as big_addr
from dual
connect by level <= 200000
)
;
connect by 구문을 이용해서 20만건, 1.6GB 의 테스트 테이블을 생성했습니다.
이전에 이 방법 관련해서 포스트한 적이 있는데, 자세한 설명을 원하시면 >>여길 참조<<바랍니다.
1) 대량 Update 테스트
where deptno > 5 조건으로 Update 를 합니다.
deptno 는 0 ~ 19 까지 균등하게 있는데, 전체 20만건중에 5보다 큰 데이터는 14만건 입니다.
전체 데이터중 70% 정도를 Update 합니다.
일반적인 Update 구문으로는 1분6초(66초)가 걸렸습니다.
Update 된 데이터를 rollback 하고, 이번에는 CTAS 구문으로 바꿔서 수행해보겠습니다.
4초만에 끝났습니다. 66초 -> 4초 이면 엄청난 성능개선이네요~
Update 를 CTAS 구문으로 바꾸면서 case when 문을 사용했습니다.
deptno > 5 경우는 Update 치는 값으로 집어넣고, 그외의 경우는 원래 데이터를 그냥 복사하는 방식입니다.
이렇게 작업해 준 다음에, TEST1 과 TEST1_TEMP 를 서로 바꿔치기 해주면 됩니다.
바꿔주는 작업은 rename 구문을 이용합니다.
rename 은 아주 순식간에 끝납니다.
(단, 만약 원본 테이블에 Index 가 있었다면 rename 후 Index 생성 작업은 별도로 필요합니다.)
2) 대량 Delete 테스트
이번에는 Update 와 같은 방식으로 Delete 를 테스트 해 보겠습니다.
54초가 걸렸네요.
롤백하고, CTAS 구문으로 바꿔서 수행해보겠습니다.
1초만에 끝나버렸네요.
앞에서는 5보다 큰 데이터(14만건)를 삭제하는 것이었는데, CTAS 에서는 반대로 5보다 작거나 같은 놈들(6만건)을 가지고 테이블을 만들라는 식으로 처리했을 뿐입니다.
이것도 위에서처럼 나중에 새로 만들어진 테이블과 원본 테이블을 rename 으로 바꿔치기 해주면 끝입니다.
원본 테이블이 파티션인 경우에는 >>Partition Exchange 기능<< 을 이용하면 됩니다.
확실히 Update, Delete 할 대상 데이터가 많은 경우에는 CTAS 로 하면 많이 빨라지는 것을 확인해봤습니다.
