인덱스 유지 비용
테이블 데이터를 변경하면 관련된 인덱스에도 변경이 발생한다. 변경할 인덱스 레코드를 찾아가는 비용에 Redo, Undo를 생성하는 비용까지 더해지므로 인덱스 개수가 많을수록 DML 성능이 나빠진다.
Update 수행 시 테이블 레코드는 직접 변경하지만 인덱스 레코드는 Delete & Insert 방식으로 처리된다. 인덱스는 항상 정렬된 상태를 유지해야 하기 때문이며, 인덱스 유지를 위한 Undo 레코드도 두 개씩 기록된다. 변경 컬럼과 관련된 인덱스 개수에 따라 Update 성능이 좌우되며 Insert나 Delete문일 때는 인덱스 모두에 변경을 가해야 하므로 총 인덱스 개수에 따라 성능이 크게 달라진다.
이처럼 인덱스 개수가 DML 성능에 큰 영향을 미치므로 대량의 데이터를 입력/수정/삭제할 때는 인덱스를 모두 Drop 하거나 Unusable 상태로 변경한 다음에 작업하는 것이 빠를 수 있다.
Insert 튜닝
Oracle Insert 튜닝
일반적인 힙 구조 테이블은 순서 없이 Freelist로부터 할당받은 블록에 무작위로 값을 입력한다. Freelist에서 할당받은 블록을 버퍼 캐시에서 찾아보고, 없으면 데이터 파일에서 읽어 캐시에 적재한 후에 데이터를 삽입한다.
일반적인 트랜잭션을 처리할 때는 빈 공간부터 찾아 채워 나가는 위 방식이 효율적이지만 대량의 데이터를 Bulk로 입력할 때는 매우 비효율적이다. 빈 블록은 얼마 지나지 않아 모두 채워지고 이후부터는 순차적으로 뒤쪽에만 데이터를 쌓게 될텐데도 건건이 Freelist를 조회하면서 입력하기 때문이다.
FreeList를 거치지 않고 HWM 바깥 영역에, 버퍼 캐시를 거치지 않고 데이터 파일에 곧바로 입력하는 Direct Path Insert 방식을 사용하면 대용량 Insert 속도를 크게 향상시킬 수 있다. 이 방식을 사용할 때 사용자가 커밋할 때만 HWM을 상향조정하면 되기 때문이 Undo 데이터를 쌓지 않는다.
Oracle에서 Direct Path Insert 방식으로 데이터를 입력하는 방법
- insert select 문장에 /*+ append */ 힌트 사용
- 병렬 모드로 insert
- direct 옵션을 지정하고 SQL *Loader(sqlldr)로 데이터를 로드
- CTAS(create table ... as select) 문장을 수행
- nologging 모드 insert
Direct Path Insert 방식으로 데이터를 입력하면 Exclusive 모드 테이블 Lock이 걸려 작업이 수행되는 동안 다른 트랜잭션은 해당 테이블에 DML을 수행하지 못하게 된다. 또한, nologging 상태에서 입력한 데이터는 장애가 발생했을 때 복구가 불가능하다.
Freelist는 HMM(High-Water Mark) 아래쪽에 위치한 블록 중 어느정도 빈 공간을 가진 블록 리스트를 관리하는 자료구조다.
SQL Server Insert 튜닝
최소 로깅(Minimal nologging)
- DB로 로딩하는 Bulk Insert 구문을 사용할 때 With 옵션에 TABLOCK 힌트를 추가하면 최소 로깅 모드로 작동한다.
- Oracle CTAS와 같은 문장이 select into인데, 복구 모델이 'Bulk-logged' 또는 'Simple'로 설정된 상태에서
select * into target from source;
이 문장을 사용하면 최소 로깅 모드로 작동한다. - 힙 테이블에 insert할 때는 TABLOCK 힌트를 사용하면 된다. 이때, X 테이블 Lock 때문에 여러 트랜잭션이 동시에 insert할 수 없게 된다.
insert into t_heap with (TABLOCK) select * from t_source;
Update 튜닝
Truncate & Insert 방식 사용
update 주문 set 상태코드 = '9999' where 주문일시 < to_date('20000101', 'yyyymmdd');대량의 데이터를 위와 같이 일반 Update문으로 갱신하면 상당히 오랜 시간이 소요될 수 있다. 다음과 같은 이유 때문이다.
(Delete문일 때도 마찬가지)
- 테이블 데이터를 갱신하는 본연의 작업
- 인덱스 데이터까지 갱신
- 버퍼 캐시에 없는 블록을 디스크에서 읽어 버퍼 캐시에 적재한 후에 갱신
- 내부적으로 Redo와 Undo 정보 생성
- 블록에 빈 공간이 없으면 새 블록 할당(-> Row Migration 발생)
따라서 대량의 데이터를 갱신할 때는 Update문을 이용하기보다 다음과 같은 방법이 더 빠를 수 있다.
1) 대상 테이블의 테이블로 temp 테이블 생성
create table 주문_임시 as
select *
from 주문;2) 대상테이블의 제약조건 및 인덱스 삭제
alter table emp drop constraint 주문_PK;
drop index \[주문.\]주문_idx1;3) 대상 테이블 Truncate
truncate table 주문;4) temp 테이블에 있는 원본 데이터를 update할 값으로 수정하여 대상테이블에 insert
insert into 주문(고객번호, 주문일시, 상태코드)
select 고객번호,
주문일시,
(case when 주문일시 >= to_date('20000101','yyyymmdd') then '9999' else status end) 상태코드
from 주문_임시
;5) 대상테이블에 제약조건 및 인덱스 생성
alter table 주문 add constraint 주문_pk primary key(고객번호, 주문일시);
create index 주문_idx1 on 주문(주문일시, 상태코드);조인을 내포한 Update 튜닝
조인을 내포한 Update문을 수행할 때는 Update 자체의 성능보다 조인 과정에서 발생하는 비효율 때문에 성능이 느려지는 경우가 더 많다.
다른 테이블과 조인을 통해 Update를 수행할 때, 일반적인 Update문을 사용하면 비효율이 발생한다. Update를 위해 참조하는 테이블을 두 번 액세스해야 하기 때문이다.
update 고객
set (최종거래일시, 최근거래금액) = (select max(거래일시), sum(거래금액)
from 거래
where 고객번호 = 고객.고객번호 and 거래일시 >= trunc(add_months(sysdate, -1))
)
where exists (select 'x'
from 거래
where 고객번호 = 고객.고객번호 and 거래일시 >= trunc(add_months(sysdate, -1))
);위 Update를 위해서는 기본적으로 거래 테이블에 [고객번호 + 거래일시] 인덱스가 있어야 한다. 인덱스가 그렇게 구성되어 있어도 고객 수가 많고 고객별 거래 데이터가 많다면 Random 액세스 방식으로 조인을 수행하기 때문에 위 쿼리는 결코 빠르게 수행될 수 없다.
그럴 때는 서브쿼리에 unnest와 함께 hash_sj 힌트를 사용해 해시 세미 조인 방식으로 유도하는 것이 효과적이다. 해시 세미 조인 방식으로 수행하면 Random 액세스는 상당히 줄일 수 있지만 거래 테이블은 2번 액세스하는 비효율은 여전히 남는다.
수정 가능 조인 뷰(Oracle)
'조인 뷰'는 from절에 두 개 이상 테이블을 가진 뷰를 말하며, 조인 뷰를 통해 원본 테이블에 입력, 수정, 삭제가 가능하다. 여기서 한 가지 제약사항이 있는데, 키-보존 테이블에서만 입력, 수정, 삭제가 허용된다는 사실이다.
키 보존 테이블(Key-Preserve Table) : 조인된 결과집합을 통해서도 중복 없이 Unique하게 식별이 가능한 테이블
이를 위해서는 Unique 집합과 조인되어야 하는데, 옵티마이저는 조인되는 테이블에 Unique 인덱스가 있는지를 보고 Unique 집합 여부를 판단한다.
UPDATE /*+ bypass_ujvc */
( SELECT /*+ ordered use_hash(c) */
c.최종거래일시, c.최근거래횟수, c.최근거래금액
, t.거래일시, t.거래횟수, t.거래금액
FROM ( SELECT 고객번호, MAX(거래일시) 거래일시, COUNT(*) 거래횟수, SUM(거래금액) 거래금액
FROM 거래
WHERE 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1))
GROUP BY 고객번호) t, 고객 c
WHERE c.고객번호 = t.고객번호
)
SET 최종거래일시 = 거래일시
, 최근거래횟수 = 거래횟수
, 최근거래금액 = 거래금액 ;이 Update문이 제대로 수행되려면 고객 테이블이 키-보존 테이블이어야 한다. 그런데 거래 데이터를 집계한 인라인 뷰에 Unique 인덱스가 존재할 수 없으므로 Oracle은 고객 테이블을 키-보존 테이블로 인정하지 않는다. 집합적으로 Unique성이 보장됨에도 불구하고 Unique 인덱스를 찾을 수 없다는 이유로 옵티마이저가 필요 이상의 제약을 가한 셈인데, 다행히 이를 피해갈 수 있는 bypass_ujvc(Bypass Updatable Join View Check) 힌트가 제공된다. 이 힌트는 Update를 위해 참조하는 집합에 중복 레코드가 없음이 100% 보장될 때만 사용할 것을 당부한다.
Merge문 활용
merge into문을 이용하면 하나의 SQL 안에서 insert, update, delete 작업을 한번에 처리할 수 있다. merge into는 기간계 시스템으로부터 읽어온 신규 및 변경분 데이터를 DW 시스템에 반영하고자 할 때 사용하면 효과적이다.
merge into 테이블1 using 테이블2(뷰도 상관 X) on 조건
when matched then update set ...
when not matched then insert ...테이블2를 이용해 조건에 해당하는 데이터에 한해서 테이블 1을 어떻게 해보겠다. 조건에 맞으면 update, 조건에 맞지 않으면 insert하겠다.
...라는 뜻이다.
아래는 merge문을 이용해 insert, update를 동시에 처리하는 예시다.
merge into 고객 t using 고객변경분 s on (t.고객번호 = s.고객번호)
when matched then update set t.고객명 = s.고객명, t.이메일 = s.이메일
when not matched then insert (고객번호, 고객명, 이메일, 전화번호, 거주지역, 주소, 등록일시) values (s.고객번호, s.고객명, s.이메일, s.전화번호, s.거주지역, s.주소, s.등록일시);본인이 공부하던 책에는 예시가 이렇게 적혀있었다.
merge into 고객 t using 고객변경분 s on (t.고객번호 = s.고객번호) when matched then update set t.고객번호 = s.고객번호, t.고객명 = s.고객명, t.이메일 = s.이메일 when not matched then insert (고객번호, 고객명, 이메일, 전화번호, 거주지역, 주소, 등록일시) values (s.고객번호, s.고객명, s.이메일, s.전화번호, s.거주지역, s.주소, s.등록일시);하지만 이렇게 작성했더니...
이런 오류가 발생했다. 현재 on에서 썼던 부분을 when절에서 update하고자 썼더니 발생한 오류인 듯 하다. 그래서 update set절에서
t.고객번호 = s.고객번호
이 부분을 쓰지 않았을 때는 쿼리가 정상적으로 작동하는 것을 볼 수 있었다.
이 확장 기을 통해 Updatable Join View 기능을 대체할 수 있게 되었다. 앞에서 bypass_ujvc 힌트를 사용했던 update 문장을 예로 들면, 아래와 같이 merge문으로 처리할 수 있게 되었다.
