SQLP 요점정리 #19 : 3과목 Tuning - 5장 고급 SQL Tuning - 3절 DML Tuning

3과목 SQL 고급 활용 및 Tuning

5장 고급 SQL Tuning

5.3 DML Tuning

• DML 수행시
  • Table 변경
  • Index 변경 : Update 수행시 Index는 Delete & Insert 수행
  • Redo, Undo 생성

5.3.1 Insert Tuning

1. Direct Path Insert
   • Freelist 조회없이, Buffer Cache를 거치지 않고 datafile에 바로 입력하는 방식
   • INSERT SELECT 문장에 /*+ append */ Hint 사용
   • Parallel Mode로 INSERT
   • direct 옵션을 지정하고 SQL#Loader(sqlldr)로 데이터 로드
   • CTAS (CREATE TABLE ... AS SELECT) 문장을 수행
2. nologging 모드 Insert
   • ALTER TABLE 테이블명 NOLOGGING;
   • Redo Log까지 최소화 (DPI에서는 Freelist 조회X, Buffer Cache X)
   • Direct Path Insert 시에만 작동
   • 단 Exclusive Mode Table Lock이 걸리므로 다른 Transaction이 해당 Table에 DML 수행을 차단
   • nologging 상태에서 입력한 데이터는 장애 발생시 복구가 불가능
     • 입력후 바로 Backup을 해야 함
     • 언제든 재생 가능한 데이터라면 상관없음
       • e.g. 배치 프로그램에서 중간 단계 임시 Table

5.3.2 Update Tuning

1. Truncate & Insert 방식

UPDATE contract SET status = '9999' WHERE ord_date < _TO_DATE('20000101', 'yyyymmdd);

• 대량의 데이터를 UPDATE하면 상당히 오랜시간이 걸린다.
  • UPDATE 자체 작업
  • Index Delete & Insert
  • Datafile을 Buffer Cache로 load한 후에 갱신
  • Redo, Undo 정보 생성
  • Block에 빈 공간이 없는 경우 새 블록 할당 -> Row Migration
• 그래서 차라리 Table을 새로 생성하여서 작업을 한 후에 Index를 생성하는 것이 더 효율적일 수 있다.

CREATE TABLE tmp_cont AS SELECT * FROM contract;  -- 임시 Table로 데이터 복사

ALTER TABLE DROP CONSTRAINT cont_pk;              -- INDEX 삭제
DROP INDEX contract.cont_idx;

TRUNCATE TABLE contract;                          -- Table 데이터 삭제 (No Undo)

INSERT INTO contract (ord_no, ord_date, status)   -- UPDATE 문에 대응하는 INSERT INTO SELECT 문
SELECT ord_no, ord_date,
       CASE WHEN ord_date >= TO_DATE('20000101', 'yyyymmdd') then '9999' ELSE status END
 FROM tmp_cont;

ALTER TABLE contract ADD CONSTRAINT cont_pk PRIMARY KEY (ord_no, ord_date);    -- INDEX 다시 생성
CREATE INDEX cont_idx ON contract(ord_date, status);

DROP TABLE tmp_cont;                              -- 임시 Table 삭제

이 작업은 UPDATE 뿐만 아니라 DELETE 에도 적용이 가능하다.

DELETE contract WHERE ord_date < TO_DATE('20010101','yyyymmdd');

위 문장을 수행하는 것보다 아래가 더 효율적일 수 있다.

CREATE TABLE tmp_cont                  -- DELETE 문에 대응하는 CTAS
AS
SELECT * FROM contract
 WHERE ord_date >= TO_DATE('20010101','yyyymmdd');

ALTER TABLE DROP CONSTRAINT cont_pk;
DROP INDEX contract.cont_pk;

TRUNCATE TABLE contract;

INSERT INTO contract
SELECT * FROM tmp_cont;

ALTER TABLE ADD CONSTRAINT cont_pk PRIMARY KEY (ord_no, ord_date);
CREATE INDEX cont_idx ON contract(ord_date, status);

DROP TABLE tmp_cont;

2. Join을 내포한 Update Tuning

• UPDATE 자체 성능보다는 Join 과정의 비효율 때문에 성능이 느려지는 경우가 더 많다.
• 최근 1달안에 거래내역이 있는 고객의 최종거래일시와 1달간의 거래금액을 UPDATE하는 문장에 대한 예제이다.

UPDATE 고객
   SET (최종거래일시, 최근거래금액) = (SELECT MAX(거래일시), SUM(거래금액)
                                         FROM 거래
                                        WHERE 고객번호 = 고객.고객번호
                                          AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)))
 WHERE EXISTS (SELECT 1 FROM 거래
                WHERE 고객번호 = 고객.고객번호
                  AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)));

• 문제점
  • 거래 Table을 2번 참조했다.
  • [고객번호 + 거래일시] Index가 필요하다.
  • Index에 데이터가 많으면 Random 액세스로 Join을 수행하기 때문에 비효율적이다.
• 해결방안1
  • Sub-query에서 unnest 와 hash_sj Hint를 이용해서 Semi Join 방법을 유도
  • 그래도 여전히 Table을 2번 참조하긴 해야 한다.

UPDATE 고객
   SET (최종거래일시, 최근거래금액) = (SELECT MAX(거래일시), SUM(거래금액)
                                         FROM 거래
                                        WHERE 고객번호 = 고객.고객번호
                                          AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)))
 WHERE EXISTS (SELECT /*+ unnest hash_sj */ 1
                FROM 거래
               WHERE 고객번호 = 고객.고객번호
                 AND 거래일시 >= TRUNC(ADD_MONTH(SYSDATE, -1)));

• 해결방안2
  • 수정가능 조인뷰 (Updatable Join View) 활용

UPDATE /*+ bypass_ujvc */
       (SELECT c.최종거래일시, c.최근거래금액, t.거래일시, t.거래금액
          FROM (SELECT 고객번호, MAX(거래일시) 거래일시, SUM(거래금액) 거래금액
                  FROM 거래
                 WHERE 거래일시 >= TRUNC(ADD_MONTH(SYSDATE, -1))
                 GROUP BY 고객번호) t,
               고객 c
         WHERE c.고객번호 = t.고객번호)
   SET 최종거래일시 = 거래일시,
       최근거래금액 = 거래금액;

• Updatable Join View 란 ?
  • Join View : FROM 절에 2 개의 Table을 가진 View
  • Unique Index가 있는 Table 쪽의 Unique 한 경우에만 UPDATE가 가능
    • GROUP BY를 통하면 결과가 무조건 Unique한데도 불구하고 Unique Index가 없으면 수정 불가
• Oracle MERGE문 활용

MERGE INTO 대상테이블 t using 소스테이블 s ON (t.id = s.id)  -- 조건
WHEN MATCHED THEN
    UPDATE SET t.a = s.a, ...
WHEN NOT MATCHED THEN
    INSERT (t.컬렴list) VALUES (s.컬럼list);

• Oracle 9i, MS-SQL 2008부터 지원
• Oracle 10g부터는 UPDATE, INSERT 를 선택적으로 따로 처리가 가능

MERGE문으로 앞에서 본 Updatable Join View를 대체 할 수 있다.

MERGE INTO 고객 c
     USING (SELECT 고객번호, MAX(거래일시) 거래일시, SUM(거래금액) 거래금액
             FROM 거래
            WHERE 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE,-1))
            GROUP BY 고객번호) t
        ON (c.고객번호 = t.고객번호)
WHEN MATCHED THEN
    UPDATE SET c.최종거래일시 = t.거래일시,
               c.최근거래금액 = t.거래금액