SQL 튜닝이론 - 수정가능 조인뷰 (키보존테이블)

SQL 튜닝이론 - 수정가능 조인뷰 (키보존테이블)

 

고객별 한 달 이내  거래데이터를 업데이트 할때 일반적으로 다음과 같이 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 고객 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))
)

 

위 방식도 첫번째 만큼은 아니지만 거래 데이터를 중복해서 읽는 비효율이 있습니다. 총 고객수와 한 달이내 거래 고객 수에 따라 성능이 좌우됩니다. 총 고객 수가 아주 많다면 Exists 서브쿼리를 아래와 같이 해시 세미 조인으로 유도할 수 있습니다.

 

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))
)

 

한 달 이내 거래를 발생시킨 고객이 많아 UPDATE 발생량이 많다면, 아래와 같이 변경하는 것을 고려할 수 있습니다. 이 방식은 모든 고객 레코드에 LOCK이 걸리는 것은 물론, 이전과 같은 값으로 갱신되는 비중이 높을 수록 REDO 로그 발생량이 증가합니다.

 

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

 

이처럼 다른 테이블과 조인이 필요할 때 전통적인 UPDATE 문을 사용하면 비효율을 완전히 해소할 수 없습니다.

 

수정가능 조인 뷰

 

수정가능 조인뷰를 참조하면 참조 테이블과 두 번 조인하는 비효율을 없앨 수 있습니다.

 

 update(
     -- 조인뷰
     select /*+order use_hash(c) no_merge(t)*/
    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 절에 두 개 이상 테이블을 가진 뷰를 말합니다. 수정가능 조인뷰는 말 그대로 입력, 수정, 삭제가 허용되는 조인 뷰를 말합니다. 단, 1쪽 집합(거래)에 조인하는 M쪽 집합(고객)에만 입력, 수정, 삭제가 허용됩니다.

 

-- EMP(M), DEPT(1)
CREATE TABLE EMP AS SELECT * FROIM SCOTT.EMP;
CREATE TABLE DEPT AS SELECT * FROM SCOTT.DEPT;

CREATE OR REPLACE VIEW EMP_DEPT_VIEW AS
SELECT E.ROWID EMP_RID, E.*, D.ROEIS DEPT_RID, D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE  E.DEPTNO = D.DEPTNO

UPDATE EMP_DEPT_NIEW SET LOC = 'SEOUL' WGHERE JOB ='CLECK'

 

위 쿼리는 작동되지 않습니다. EMP 테이블(M)의 컬럼의 값을 조건으로 DEPT 테이블(1)의 컬럼의 값을 변경하기 때문입니다. 실제로 수행해보면 아래와 같은 에러가 발생합니다.

 

CANNOT MODIFY A COLUMN WHITCH MAPS TO A NON-PRESERVED TABLE

 

옵티마이저가 어느 테이블이 1쪽 테이블인지 알 수 없기 때문입니다. DELETE, INSERT도 마찬가지 입니다. 아래와 같이 1쪽 집합에 PK 제약을 설정하거나 UNIQUE 인덱스를 생성해서 1쪽 집합임을 명시해야 수정가능 조인뷰를 통한 입력/수정/삭제가 가능합니다.

 

ALTER TABLE DEPT ADD CONSTRAINT DEPT_PK PRIMARY KEY(DEPTNO);
UPDATE EMP_DEPT_VIEW SET COMM = NLV(COMM,0) + (SAL * 0.1) WHERE SAL <= 1500;

 

위와 같이 PK 제약을 설정하면 EMP 테이블은 키-보존테이블이되고 DEPT 테이블은 비 키-보존테이블로 남습니다.

 

키 보존 테이블

조인된 결과집합을 통해 중복 값 없이 UNIQUE하게 식별이 가능한 테이블을 말합니다. UNIQUE 한 1쪽 집합과 조인되는 테이블이어야 조인된 결과집합을 통한 식별이 가능합니다. 단적으로 말해 '키 보존 테이블' 이란 뷰에 ROWID 를 제공하는 테이블을 말합니다. DEPT 테이블로부터 UNIQUE 인덱스를 제거하면 키 보존 테이블이 없기 때문에 뷰에서 ROWID를 출력할 수 없게 됩니다.

 

ORA-01779 오류 회피

 

DEPT 테이블에 AVG_SAL 컬럼을 추가합니다.

 

ALTER TABLE DEPT ADD AVG_SAL NUM(7,2);

 

EMP로부터 부서별 평균 금여를 계산해서 추가한 컬럼에 반영합니다.

 

UPDATE
(SELECT D.DEPTNO, D.AVG_SAL AS D_AVG_SAL, E.AVG_SAL AS E_AVG_SAL
FROM (
SELECT DEPTNO, ROUND(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

 

오라클 11G 이하 버전에서 위 UPDATE 문을 실행하면 ORA-01779 에러가 발생합니다. EMP 테이블을 DEPTNO 로 GROUP BY 했으므로 DEPTNO 컬럼으로 조인한 DEPT 테이블이 키 보존테이블임에도 옵티마이저는 DEPT 테이블을 키 보존테이블로 보지 않습니다.

 

11G 에서도 1쪽 집합에 UNIQUE 인덱스가 있으면, 수정가능 조인 뷰를 이용한 UPDATE가 가능합니다. 12G 부터는 오히려 수정가능조인뷰에 대한 기능이 개선됐습니다. GROUP BY 한 집합과 조인한 테이블은 원래 비 보존 테이블이었다고해도 키 보존 테이블로 인식합니다.

 

고객 테이블을 GROUP BY 처리해서 조인하면 고객 테이블에 UNIQUE 인덱스가 없어도 에러를 회피할 수 있습니다.

 

UPDATE (
    SELECT O.주문금액, O.할인금액
    FROM 주문_T O
    , (SELECT 고객번호 FROM 고객_T WHERE 고객등급 = 'A' GROUP BY 고객번호) C
    WHERE O.고객번호 = C.고객번호
    AND O.주문금액 >= 100000
)
SET 할인금액 = 주문금액 * 0.2, 주문금액 = 주문금액 * 0.8

 

배치 프로그램이나 데이터 이행 프로그램에서 사용하는 중간 임시테이블에는 일일이 PK 제약이나 인덱스를 생성하지 않으므로 이 페턴을 사용할 수 있습니다.