최근 시스템 장애로 인해 약 50만 건의 데이터를 manual하게 일괄 업데이트 해야할 일이 생겼다
이 때, 널리 사용되는 update구문을 사용하게 되면 시간이 꽤 많이 소요될 수 있는데 그 이유는 오라클의 구조에 있다.
오라클은 data의 update/delete 시, 일종의 snapshot/rollback을 위해 기존의 데이터를 undo segment와 redo log에 기록을 한다.
따라서 undo segment와 redo log 작업을 회피하여 update/delete할 수 있는 방법을 소개하려 한다.
- Undo Segment
위 소개글에서 잠깐 Undo Segment에 대한 언급을 하여 궁금한 사람이 있을까 정리를 해둔다. CTAS에 대해 궁금한 사람은 다음 문단으로 넘어가 참고하길 바란다.
사용자가 다양한 경로(SQL Client Tool, Application 등)로 Oracle Server에 접속하면 Undo tablespace의 가장 적게 사용 중인 segment를 할당해준다. 사용자가 Oracle Server에 접속하여 Insert/Update/Delete등의 작업을 하면 rollback을 위해 할당받은 segment에 트랜잭션이 종료될 때까지 저장해둔다. 이 때, segment는 유저 별로 독점적으로 제공되는 것이 아닌, 여러 Oracle 사용자가 실행한 트랜잭션이 저장되는 공유공간이다. 따라서 발생하는 주의사항이 있다.
바로 "경합현상" 이다. 예를 들어, 1번 segment에 대해 사용했던 block의 수가 10000인데, 그 중 해당 segment를 바로 사용할 수 있었던 block도 있었을 것이고, 성능문제로 대기상태에 머물렀던 block도 있었을 것이다. 이 때, 대기 상태에 머무른 block의 수가 많아지면 경합현상이 발생하고 있으니 충분한 segment를 생성해줘야하고, 그 비율(대기했던 블록 수/전체 블록 수)은 5%를 기준이라고 생각하면 된다.
-- 경합현상 발생 유무 확인
select sum(waits) * 100 / sum(gets) as rate
from V$ROLLSTAT;
undo segment는 자동관리되고 있지만, 각 세션별로 할당해주는 것이 각 어플리케이션에 최적화가 되지않아 경합현상이 계속해서 발생할 수 있다. 따라서 자동관리로 도저히 관리가 되지않는다면, 수동관리해주는 것이 바람직하다.
- CTAS(Create Table As Select)
해당 테이블에서 바로 데이터를 수정하지 않고, 신규 테이블을 생성한 후 이름을 맞교환하는 idea이다.
update old_table
set col1 = 'new_data'
where col2 > 10000;create table new_table as
select case when(col2 > 10000) then 'update_data'
else col1 end as col1
, col2
, col3
from old_table;
rename old_table to tmp_table;
rename new_table to old_table;
이처럼 기존 테이블에서 값을 일부 수정하여 그대로 가져와서 신규 테이블을 생성하고, 기존 테이블과 신규 테이블의 이름을 맞교환한다.
하지만 이 방법을 사용할 경우, 기존 테이블의 기타 정보(index, grant, synonym 등)를 별도로 추가해줘야하니 사전에 충분히 테이블에 대한 정보를 갖춘 상태여야한다.