[SQLP] DML 튜닝

1. 인덱스 유지 비용

• 테이블 데이터를 변경하면 관련된 인덱스에도 변경이 발생
• 변경된 인덱스 레코드를 찾아가는 비용 + Redo, Undo 생성 비용이 발생
  • 그래서 인덱스 개수가 많을수록 DML 성능이 나빠짐
• Update를 수행할 때, 테이블 레코드는 직접 변경하지만 인덱스 레코드는 Delete 및 Insert 방식으로 처리됨
• Insert나 Delete 문일 때는 인덱스 모두에 변경을 가해 총 인덱스 개수에 따라 성능이 크게 달라짐
• 그래서 대량의 데이터 처리가 발생할 때, 인덱스를 모두 Drop 하거나 Unusable 상태로 변경한 다음 처리하는게 빠를 수 있다.

2. Insert 튜닝

가. Oracle Insert 튜닝

Direct Path Insert

• IOT(index-organized table) 는 정해진 key 순으로 정렬하면서 값을 입력하는 반면, 일반적인 힙 구조 테이블은 순서 없이 Freelist로부터 할당받는 블록에 무작위로 값을 입력한다.
  • Freelist 에서 할당 받는 블록을 버퍼 캐시에서 찾아보고, 없으면 데이터 파일에서 읽어 캐시에 적재한 후에 데이터를 삽입한다.
    • Freelist는 HWM(High-Water-Mark) 아래쪽에 위치한 블록 중 어느 정도 빈 공간을 가진 블록 리스트를 관리하는 자료구조다.
• 일반적인 트랜잭션을 처리할 때는 빈 공간부터 찾아 나가는 위 방식이 효율적이다.
• 그러나 대량의 데이터를 Bulk 로 입력할 때는 매우 비효율적이다.
  • 빈 블록은 얼마 지나지 않아 모두 채워지고 이후부터는 순차적으로 뒤쪽에만 데이터를 쌓게 될 텐데도 건건이 Freelist를 조회하면서 입력하기 때문이다.
• 그래서 이러한 과정을 거치지 않고 데이터 파일에 곧바로 입력하는 것이 Direct Path Insert 방식이다.
  • 이 때 Undo 데이터를 쌓지 않는 점도 속도 향상의 주요인이다.
• 아래는 Oracle의 Direct Path Insert 방식이다.
  • insert select 문장에 /*+ append */ 힌트 사용
  • 병렬 모드로 insert
  • direct 옵션을 지정하고 SQL*Loader(sqlldr)로 데이터를 로드
  • CTAS(create table … as select) 문장을 수행

nologging 모드 Insert

• Oracle에서 아래와 같이 테이블 속성을 nologging으로 바꿔주면 Redo 로그까지 최소화(데이터 딕셔너리 변경사항만 로깅)되므로 더 빠르게 insert 할 수 있다. 이 기능은 Direct Path Insert 일 때만 작동하며, 일반 insert문을 로깅하지 않도록 하는 방법은 없다.

alter table t NOLOGGING;

• 주의할 점은 Direct Path Insert 방식으로 데이터를 입력하면, Exclusive 모드 테이블 LOCK이 걸린다. 즉 작업이 수행되는 동안 다른 트랜잭션은 해당 테이블에 DML을 수행하지 못하게 된다.
• 그리고 nologging은 장애가 발생했을 때 복구가 불가능하다.
  • 그렇기 때문에 DW 시스템의 읽기 전용 데이터나 배치 프로그램 중간 단계의 임시 테이블 등에 작업에 사용하는 것이 좋다.

2. Update 튜닝

가. Truncate & Insert 방식 사용

update 주문 
set 상태코드 = '9999' 
where 주문일시 < to_date('20000101', 'yyyymmdd')

• 대량의 데이터를 아래의 쿼리같이 일반 Update 문으로 갱신하면 상당한 시간이 걸리는데, 다음과 같은 이유이다. Delete도 마찬가지이다.
  • 테이블 데이터를 갱신하는 본연의 작업
  • 인덱스 데이터까지 갱신
  • 버퍼 캐시에 없는 블록을 디스크에서 읽어 버퍼 캐시에 적재한 후에 갱신
  • 내부적으로 Redo와 Undo 정보 생성
  • 블록에 빈 공간이 없으면 새 블록 할당(→ Row Migration 발생)
• 따라서 대량의 데이터를 처리할 때 아래와 같은 방식이 더 빠를 수 있다.
  • 여기다가 nologging 옵션까지 더 하면 더 빨라질 수 있다.

create table 주문_임시 as select * from 주문;

truncate table 주문; 


insert into 주문(고객번호, 주문일시, , 상태코드) 
select 고객번호, 주문일시
    ,(
        case 
            when 주문일시 >= to_date('20000101', 'yyyymmdd') then '9999' 
            else status 
        end
    ) 상태코드 
from 주문_임시; 

alter table 주문 add constraint 주문_pk primary key(고객번호, 주문일시); 
create index 주문_idx1 on 주문(주문일시, 상태코드);

나. 조인을 내포한 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))
);

• 해당 쿼리를 실행할 때 기본적으로 거래 테이블에 [고객번호 + 거래일시] 인덱스가 있어야 한다.
• 인덱스가 구성돼 있어도 데이터가 많으면 빠르게 수행될 수 없다. (RANDOM 엑세스 방식으로 조인을 수행)

Oracle 수정 가능 조인 뷰 활용

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 
    최종거래일시 = 거래일시, 
    최근거래금액 = 거래금액;

• Oracle에서는 수정 가능한 조인 뷰를 활용할 수 있다.
  • 12c이상에서만 정상적으로 실행되고, 10g 이하에서는 Update 옆에 BYPASS_UJVC 힌트를 줘야한다.
• 조인 뷰는 from 절에 두 개 이상 테이블을 가진 뷰를 말하며, 조인 뷰를 통해 원본 테이블에 입력, 수정, 삭제가 가능하다.
• 이 때 제약사항이 있는 데 키-보존 테이블에만 입력, 수정, 삭제가 허용된다는 사실이다.
  • 키-보존 테이블이란 조인된 결과 집합을 통해서도 중복 없이 Unique하게 식별이 가능한 테이블을 말함
  • 옵티마이저가 조인되는 테이블에 Unique 인덱스가 있는 지를 보고 Unique 집합 여부를 판단함
  • 위와 같이 거래 테이블은 중복이 존재하여 group by를 하더라도 옵티마이저는 키-보존테이블로 인정하지 않는다고 하는데 BYPASS_UJVC 힌트로 이를 해결할 수 있다.

Oracle Merge 문 활용

• merge into 문을 이용하면 하나의 SQL 안에서 insert, update, delete 작업을 한 번에 처리할 수 있다.

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.등록일시);