그 동안은 조회를 위한 튜닝이었다.
이제 데이터를 삽입/삭제하는 DML 튜닝에 대해 알아보자.
인덱스 유지 비용
인덱스는 사실 전적으로 조회를 위한 기능이다.
데이터를 수정하면 인덱스도 그에 맞춰줘야 하므로 인덱스는 무조건 DML에 좋지 않다.
Update를 수행할 때 테이블 레코드는 직접 변경하지만 인덱스 레코드는 delete & insert 방식으로 처리된다. 인덱스는 항상 정렬된 상태를 유지해야 하기 때문이며, 인덱스 유지를 위한 undo 레코드도 2개씩 기록된다. (하나는 delete 전 하나는 insert 전) 따라서 관련 인덱스 개수에 따라 Update 성능이 좌우된다.
Delete/Insert의 경우 인덱스 모두에 변경을 가해야 하므로 총 인덱스 개수에 따라 성능이 크게 달라진다. (오라클의 경우 칼럼이 모두 Null이면 인덱스엔 변경이 없다.)
어찌됐든 인덱스 개수가 DML 성능에 큰 영향을 미치므로 대량의 데이터를 수정할 때는 일일히 인덱스 변경하면서 건건이 추가하는 것보단 인덱스를 모두 drop 하거나 unusable 상태로 변경하고 다 추가한 다음에 재생성하는 게 나을 수도 있다.
Insert 튜닝
오라클의 경우
IOT는 정해진 key 기준으로 정렬하면서 데이터를 입력하지만 일반적인 힙 구조 테이블은 freelist로 할당받은 블록에 무작위로 값을 입력한다.
freelist는 빈 공간을 가진 블록 리스트를 관리하는 자료구조다.
데이터를 삽입할 때는 freelist에서 할당받은 블록을 버퍼 캐시에서 찾아보고(버퍼 캐시에 있던 데이터가 삭제되고 freelist로 반환된 경우), 없으면 데이터파일에서 읽어(삭제된 지 시간이 좀 지난 경우) 캐시에 적재한 후에 데이터를 삽입한다.
일반적인 트랜잭션을 처리할 때는 (삭제에 의해) 비워진 공간부터 찾아 채워 나가는 위 방식이 효율적이다.
하지만 대량의 데이터를 한꺼번에 입력할 때는 매우 비효율적이다.
빈 블록은 얼마 지나지 않아 모두 채워질 거고 이후엔 순차적으로 뒤에 데이터를 쌓게 될 텐데 일일히 freelist를 조회하기 때문이다.
이 경우 freelist를 거치지 않고 데이터 파일에 바로 꽂는 'Direct Path Insert' 방식을 활용하면 insert 속도를 크게 향상시킬 수 있다.
이때, Undo 데이터를 쌓지 않는데, 건건히 undo record를 만들지 않으므로 이때문에도 속도가 빠르다.
Direct Path Insert 방식으로 데이터를 입력하는 방법은 아래와 같다.
- insert select 문장에
/*+ append */힌트 사용 - 병렬 모드로 insert
- direct 옵션을 지정하고 SQL*Loader(sqlldr)로 데이터를 로드
- CTAS(create table ... as select) 문장을 수행
오라클에서 alter table t NOLOGGING 을 사용해서 테이블 속성을 nologging으로 바꿔주면 Direct Path Insert를 사용할 때 redo log까지 작성 안 하면서 더 빠르게 할 수 있다.
일반 Insert에선 로깅한다.
Direct Path Insert 방식으로 데이터를 입력하면 Exclusive 모드로 테이블 lock이 걸린다.
즉 작업이 수행되는 동안 그 어떤 트랜잭션도 테이블에 DML이 불가능하다.
따라서 이 인서트는 트랜잭션이 잘 없는 시간대에 해야 되고 undo가 불가능하기 때문에 따로 백업을 해놔야 한다.
Update 튜닝
Truncate & Insert
그냥 update문이 하는 일은 다음과 같다.
1. 테이블 데이터를 갱신한다.
2. 인덱스 데이터를 갱신한다.
3. 갱신하려는 레코드가 포함된 블록이 버퍼 캐시에 없으면 디스크에서 읽어서 캐싱
4. Redo, Undo 데이터 생성
5. 블록에 빈 공간이 없으면 Row Migration 발생해서 새 블록 할당
조인을 내포한 update
update 고객 set (최종거래일시, 최근거래금액) = (
select max(거래일시), sum(거래금액)
from 거래
where 1=1
and 고객번호 = 고객.고객번호
and 거래일시 >= trunc(add_months(sysdate,-1))
)
where exists (
select 'x'
from 거래
where 1=1
and 고객번호 = 고객.고객번호
and 거래일시 >= trunc(add_months(sysdate,-1))
);위 쿼리는 고객의 저번달부터의 최종거래일시와 최근거래금액의 합을 업데이트 하는 쿼리이다.
보면 where 안에서 봤던 걸 set에서 한 번 더 보는 것을 확인할 수 있다.
update /*+ bypass_ujvc */ (
select c.최종거래일시, c.최근거래금액, t.거래일시, t.거래금액
from (
select 고객번호, max(거래일시) 거래일시, sum(거래금액) 거래금액
from 거래
where 거래일시 >= trunc(add_months(sysdate,-1))
group by 고객번호
) t , 고객 c
where c.고객번호 = t.고객번호
)
set 최종거래일시 = 거래일시 , 최근거래금액 = 거래금액이럴 땐 bypass_ujvc 힌트를 사용하거나 merge into 를 사용하면 중복 없이 update가 가능하다.
