[Oracle] 오라클 JOIN을 내포한 DML 튜닝

수정 가능 JOIN View 활용

- 다른 테이블과 JOIN이 필요한 경우 전통적인 방식의 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)))

-- 거래 테이블을 SET 절에서 세 번, WHERE 절에서 한 번 access

▼

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 절에서 한 번 access

-- 총 고객수와 한 달 이내 거래가 존재하는 고객 수에 따라 성능이 좌우

-- 총 고객수가 아주 많다면 해시 세미 조인 유도

→ WHERE EXISTS (SELECT /*+ unnest hash_sj */ 'X' FROM 거래

* JOIN View : from 절에 두 개 이상 테이블을 가진 View

- Updatable Join View는 1:M 관계에서 M쪽 집합만 입력, 수정, 삭제가 가능

→ 처리 범위가 넓은 UPDATE 문의 경우 WHERE 절이나 SET 절에서 테이블에 반복적으로 Random access 해야하므로 Updatable Join View를 활용

* 키-보존 테이블 : JOIN된 결과집합을 통해서도 중복 값 없이 Unique하게 식별이 가능한 테이블

ex) DEPT 테이블과 EMP 테이블이 JOIN 했을 때 EMP_NO는 여전히 유니크한 반면 DEPT_CD는 유니크함을 잃어버림

→ EMP 테이블이 키-보존 테이블 (키-보존 테이블만 변경 가능)

UPDATE 

(SELECT LOC, ADDR

    FROM DEPT D, EMP E

 WHERE D.DEPT_NO = E.DEPT_NO) 

     SET LOC = '서울'

WHERE SAL > 5000;

-- SAL > 5000이 아닌 사원의 부서까지 '서울'로 UPDATE 되는 경우 발생

-- DEPT_CD가 1111인 사원 중에 SAL이 5200인 사원과 4800인 사원이 있다고 가정할 때, 1쪽 집합을 UPDATE 함으로써 SAL이 4800인 사원도 LOC이 서울로 UPDATE 되어버리는 결과 발생

-- ORA-01779: cannot modify a column which maps to a non key-preserved table

MERGE문 활용

- DW에서 가장 흔히 발생하는 오퍼레이션인 두 시스템 간 데이터 동기화를 위해 오라클 9i부터 MERGE INTO문 지원

-- 전일 발생한 변경 데이터를 기간계 시스템으로부터 추출

CREATE TABLE CUSTOMER_DELTA

AS

SELECT * FROM CUSTOMER

WHERE MODIFY_DT BETWEEN TRUNC(SYSDATE)-1 AND TRUNC(SYSDATE)-1/86400;

▼

MERGE INTO CUSTOMER T USING CUSTOMER_DELTA S ON (T.CUST_ID = S.CUST_ID)

WHEN MATCHED THEN UPDATE

   SET T.CUST_NM = S.CUST_NM, T.EMAIL = S.EMAIL, ......

WHEN NOT MATCHED THEN INSERT

   (CUST_ID, CUST_NM, EMAIL, ......) VALUES

   (S.CUST_ID, S.CUST_NM, S.EMAIL, ......);

※ /*+ bypass_ujvc */ : 비 키-보존 테이블은 DML이 불가능하나 10g까지는 BYPASS_UJVC 힌트 사용 시 DML이 가능

(11g부터 지원 중단)

SELECT COUNT(*) INTO :cnt FROM DEPT WHERE DEPTNO = :val1;

if :cnt = 0 then

INSERT INTO DEPT(DEPTNO, DNAME, LOC) VALUES (:val1, :val2, :val3);

else

UPDATE DEPT SET DNAME = :val2, LOC = :val3 WHERE DEPTNO = :val1;

-- SQL이 항상 두 번씩 수행

▼

UPDATE DEPT SET DNAME = :val2, loc = :val3 WHERE DEPTNO = :val1;

if sql%rowcount = 0 then

INSERT INTO DEPT(DEPTNO, DNAME, LOC) VALUES (:val1, :val2, :val3);

end if;

-- SQL이 최대 두 번 수행

▼

MERGE INTO DEPT A

USING (SELECT :val1 DEPTNO, :val2 DNAME, :val3 LOC FROM DUAL) B

     ON (B.DEPTNO = A.DEPTNO)

WHEN MATCHED THEN

UPDATE DEPT SET DNAME = B.DNAME, LOC = B.LOC

WHEN NOT MATCHED THEN

INSERT (A.DEPTNO, A.DNAME, A.LOC) VALUES (B.DEPTNO, B.DNAME, B.LOC);

-- SQL이 한 번만 수행

UPDATE 청구일반내역 A

     SET A.청구일련번호 = (

SELECT RNUM FROM (

SELECT 청구년월, 청구생성번호, 일련번호, ROWNUM RNUM

   FROM (

SELECT 청구년월, 청구생성번호, 일련번호

        FROM 청구일반내역 C

           WHERE C.청구년월 = :청구년월

          AND C.청구생성번호 = :청구생성번호

          AND C.보류구분 = 'N'

ORDER BY C.의료급여종별구분, C.진료과목, C.수진자주민등록번호, C.진료일자

) 

) B

WHERE B.청구년월 = A.청구년월

    AND B.청구생성번호 = A.청구생성번호

    AND B.일련번호 = A.일련번호

)

WHERE A.청구년월 = :청구년월

    AND A.청구생성번호 = :청구생성번호

    AND A.보류구분 = 'N';

-- 청구일련번호를 정해진 정렬 기준에 따라 갱신

▼

MERGE INTO 청구일반내역 A

USING ( SELECT 청구년월, 청구생성번호, 일련번호,

              ROW_NUMBER() OVER (ORDER BY C.의료급여종별구분, C.진료과목, C.수진자주민등록번호,                                                                  C.진료일자) 청구일련번호

    FROM 청구일반내역 C

  WHERE C.청구년월 = :청구년월

 AND C.청구생성번호 = :청구생성번호

 AND C.보류구분 = 'N'

)B

ON B.청구년월 = A.청구년월 AND

           B.청구생성번호 = A.청구생성번호 AND

           B.일련번호 = A.일련번호)

WHEN MATCHED THEN UPDATE SET A.청구일련번호 = B.청구일련번호;