Chap02. 07 조인을 내포한 DML 튜닝

01. 수정 가능 조인 뷰 활용

---

 

전통적인 방식의 UPDATE

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

• 위와 같은 Update문이 있다면 아래와 같이 수정이 필요합니다.
  • UPDATE 시 최종거래일시, 최근거래횟수, 최근거래금액을 구하기 위해 한번의 탐색이 아닌 세 번 탐색하기 때문

 

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

•  비효율이 없는 쿼리는 아니며
  • 한달 이내 거래가 있던 고객을 두 번 조회하기 때문(SET 절, WHERE 절)
  • 총 고객 수와 한 달 이내 거래가 발생한 고객 수에 따라 성능이 좌우 됩니다.

 

 

1. [ 총 고객 수가 많을 시 ] Exists Subquery를 아래 처럼 Hash Semi Join 으로 유도하는 것을 고려

UPDATE 고객 C
   SET (최종거래일시, 최근거래횟수, 최근거래금액) = (SELECT MAX(거래일시), COUNT(*), SUM(거래금액)
                                                   FROM 거래
                                                  WHERE 고객번호 = C.고객번호
                                                    AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)))
WHERE EXISTS (SELECT /*+ UNNEST HASH_SJ */ 'X' FROM 거래
               WHERE 고객번호 = C.고객번호
                 AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)));

• UNNEST : Subquery를 Join형태로 SQL을 변형하여 실행 (4Chap에서 자세히)
• HASH_SJ : EXISTS나 IN 조건을 사용한 경우 Subquery에 UNNEST와 함께 Hash_Sj Hint를 부여하여 Hash_Join_Semi로 처리하도록 제어

 

 

2. 만약 한 달 이내 거래를 발생시킨 고객이 많을경우

UPDATE 고객 C
   SET (최종거래일시, 최근거래횟수, 최근거래금액) = (SELECT NVL(MAX(거래일시), C.최종거래일시)
                                                      , DECODE(COUNT(*), 0, C.최근거래횟수, COUNT(*))
                                                      , NVL(SUM(거래금액), C.최근거래금액)
                                                   FROM 거래
                                                  WHERE 고객번호 = C.고객번호
                                                    AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)));

• 모든 고객 Record에 Lock이 발생함은 물론, 이전과 같은 값으로 갱신 되는 비중이 높을 수록 Redo Log 발생량이 증가해 오히려 비효율적일 수 있습니다.
• SET 절에 사용되는 Subquery는 캐싱 매커니즘이 작용하므로 Distinct Value 개수가 적은 1쪽 집합을 읽어 M쪽 집합을 갱신할 때 효과적입니다.
• 물론 Exists Subquery가 NL Semi Join이나 Filter방식으로 처리된다면 Caching 효과가 나타난다.

 

 

 

수정 가능 조인 뷰

• 아래와 같이 수정 가능 조인 뷰를 활용하면 참조 Table과 두 번 Join하는 비효율을 없앨 수 있습니다.

 

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

• 조인 뷰 : FROM 절에 두 개 이상 Table을 가진 뷰
• 수정 가능 조인 뷰(UPDATATBLE/MODIFIABLE JOIN VIEW) : 입력, 수정, 삭제가 허용되는 조인 뷰를 말함
  • 단, 1쪽 집합과 조인되는 M쪽 집합에만 입력, 수정, 삭제가 허용 된다.

 

Test Script

CREATE TABLE EMP_T AS SELECT * FROM SCOTT.EMP;
CREATE TABLE DEPT_T AS SELECT * FROM SCOTT.DEPT;

CREATE OR REPLACE VIEW EMP_DEPT_VIEW AS
SELECT E.ROWID EMP_RID, E.*, D.ROWID DEPT_RID, D.DNAME, D.LOC
  FROM EMP_T E, DEPT_T D
 WHERE E.DEPTNO = D.DEPTNO;

UPDATE EMP_DEPT_VIEW set LOC = 'SEOUL' WHERE JOB = 'CLERK';

 

 

 

SELECT EMPNO, ENAME, JOB, SAL, DEPTNO, DNAME, LOC
  FROM EMP_DEPT_VIEW
 ORDER BY JOB, DEPTNO;

Data

• JOB = 'CLERK' 인 사원이 10, 20, 30 부서 모두 속해 있으며,
• UPDATE 수행 후 세 부서의 소재지는 모두 'SEOUL'로 변경 될 것입니다.
• 세 부서의 소재지가 같다고 이상하지 않지만, 다른 JOB을 가진 사원의 부서 소재지까지 바뀌는 것은 원하던 결과가 아닐 수 있습니다.

 

마지막 Update 시 Error Ora-01779

• 아직 DEPT 테이블에 UNIQUE 인덱스를 생성하지 않았기 때문에 생긴 에러이며,
• 옵티마이저 입장에서는 어느 쪽이 1집합인지 알 수 없었던 것입니다.

 

ALTER TABLE DEPT_T ADD CONSTRAINT DEPT_T_PK PRIMARY KEY (DEPTNO);

UPDATE EMP_DEPT_VIEW SET COMM = NVL(COMM, 0) + (SAL * 0.1) WHERE SAL <= 1500;

• 1쪽 집합에 PK 제약을 설정하거나 UNIQUE 인덱스를 생성해야 수정 가능 조인 뷰를 통한 입력/수정/삭제가 가능해집니다.
• PK 제약을 설정하면 EMP 테이블은 '키-보존 테이블(Key-Preserved Table)'이 되고, DEPT 테이블은 '비 키-보존 테이블(Non Key-Preserved Table)'로 남습니다.

 

 

키 보존 테이블

• 조인된 결과집합을 통해서도 중복 값 없이 Unique하게 식별이 가능한 테이블을 말합니다.
• Unique한 1쪽 집합과 조인되는 테이블이어야 조인된 결과집합을 통한 식별이 가능합니다.
• 뷰에 Rowid를 제공하는 테이블을 말합니다.

 

_UPDATABLE_COLUMNS 뷰 참조

• 수정 가능 조인 뷰에 DML 실패 시 그에 대한 내용을 확인할 수 있는 시스템에서 제공 하는 뷰입니다.

 

수정가능 조인 뷰 제약 회피

• BYPASS_UJVC(Updatable Join View Check) : 키 보존 테이블이 없더라도 UPDATE 수행이 가능하게 하는 힌트
  • UPDATE를 위해 참조하는 집합에 중복 레코드가 없을 때만 이 힌트를 사용

UPDATE /*+ BYPASS_UJVC */
       (SELECT D.DEPTNO, D.AVG_SAL D_AVG_SAL, E.AVG_SAL E_AVG_SAL
          FROM (SELECT DETPNO, ROUNT(AVG(SAL), 2) AVG_SAL FROM EMP GROUP BY DEPTNO) E
              ,DEPT D
         WHERE D.DEPTNO = E.DEPTNO )
   SET D_AVG_SAL = E_AVG_SAL;

 

 

 

02. Merge문 활용

---

MERGE INTO TEST1 T1

USING TEST2 T2

ON (T1.C1 = T2.C1)

WHEN MATCHED THEN UPDATE SET T1.C2 = T2.C3;

 

 

 

 

 

03. 다중 테이블 Insert 활용

---