by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

7. 조인을 내포한 DML 튜닝




I. 수정 가능 조인 뷰 활용

1. 전통적인 방식의 UPDATE

UPDATE 고객 c
SET  최종거래일시 = ( SELECT MAX(거래일시) FROM 거래
                       WHHERE 고객번호 = 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.고객번호
                 WAND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)))

=> 한 달 이내 거래가 있던 고객을 두번 조회하는 것으로 변경, 총 고객 수와 한 달 이내 거래가 발생한 고객 수에 따라 성능이 좌우된다.

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문을 사용하면 비효율을 감수해야 한다.

set절에 사용된 서브쿼리에는 캐싱 매커니즘이 작용하므로 distinct value 개수가 적은 1쪽 집합을 읽어 M쪽 집합을 갱신할 때 효과적이다.
exists 서브쿼리가 NL세미 조인이나 필터방식으로 처리되는 경우 캐싱 효과가 나타난다.

2. 수정 가능 조인 뷰

1. 조인뷰는 from절에 두 개 이상 테이블을 가진 뷰를 가리키며, 수정 가능 조인 뷰는 입력, 수정, 삭제가 허용되는 조인 뷰를 말한다.
2. 1쪽 집합과 조인되는 M쪽 집합에만 입력, 수정, 삭제가 허용된다.
3. 수정 가능 조인 뷰를 활용하면 전통적인 방식의 update문에서 참조 테이블을 두번 조인하는 비효율을 없앨 수 있다.

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

=> 1쪽 집합(dept)과 조인되는 M쪽 집합(emp)의 컬럼을 수정하므로 문제가 없어보이나, 수행하면 에러가 발생한다.
=> delete, insert 문도 에러가 발생한다.
=> dept테이블에 unique 인덱스를 생성하지 않았기 때문에 생긴 에러이다.
=> 1쪽 집합에 PK제약을 설정하거나 unique 인덱스를 생성하해야 수정 가능 조인 뷰를 통합 입력, 수정, 삭제가 가능하다.
=> dept테이블에 PK제약을 설정하면 emp 테이블은 키 보존 테이블, dept 테이블은 비 키-보존 테이블이 된다.

SQL> create table emp  as select * from scott.emp;

Table created.

SQL> create table dept as select * from scott.dept;

Table created.

SQL> create or replace view EMP_DEPT_VIEW as
  2  select e.rowid emp_rid, e.*, d.rowid dept_rid, d.dname, d.loc
  3  from emp e, dept d 
  4  where  e.deptno = d.deptno;

View created.

SQL> update EMP_DEPT_VIEW set loc = 'SEOUL' where job = 'CLERK';
update EMP_DEPT_VIEW set loc = 'SEOUL' where job = 'CLERK'
                         *
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table

SQL> select empno, ename, job, sal, deptno, dname, loc 
  2  from   EMP_DEPT_VIEW
  3  order by job, deptno;

     EMPNO ENAME      JOB               SAL     DEPTNO DNAME      LOC
---------- ---------- ---------- ---------- ---------- ---------- ----------
      7902 FORD       ANALYST          3000         20 RESEARCH   DALLAS
      7788 SCOTT      ANALYST          3000         20 RESEARCH   DALLAS
      7934 MILLER     CLERK            1300         10 ACCOUNTING NEW YORK
      7369 SMITH      CLERK             800         20 RESEARCH   DALLAS
      7876 ADAMS      CLERK            1100         20 RESEARCH   DALLAS
      7900 JAMES      CLERK             950         30 SALES      CHICAGO
      7782 CLARK      MANAGER          2450         10 ACCOUNTING NEW YORK
      7566 JONES      MANAGER          2975         20 RESEARCH   DALLAS
      7698 BLAKE      MANAGER          2850         30 SALES      CHICAGO
      7839 KING       PRESIDENT        5000         10 ACCOUNTING NEW YORK
      7654 MARTIN     SALESMAN         1250         30 SALES      CHICAGO
      7844 TURNER     SALESMAN         1500         30 SALES      CHICAGO
      7521 WARD       SALESMAN         1250         30 SALES      CHICAGO
      7499 ALLEN      SALESMAN         1600         30 SALES      CHICAGO

14 rows selected.

SQL> update EMP_DEPT_VIEW set comm = nvl(comm, 0) + (sal * 0.1) where sal <= 1500;
update EMP_DEPT_VIEW set comm = nvl(comm, 0) + (sal * 0.1) where sal <= 1500
                         *
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table


SQL> delete from EMP_DEPT_VIEW where job = 'CLERK';
delete from EMP_DEPT_VIEW where job = 'CLERK'
            *
ERROR at line 1:
ORA-01752: cannot delete from view without exactly one key-preserved table


SQL> alter table dept add constraint dept_pk primary key(deptno);

Table altered.

SQL> update EMP_DEPT_VIEW set comm = nvl(comm, 0) + (sal * 0.1) where sal <= 1500;

7 rows updated.

SQL> commit;

Commit complete.

키 보존 테이블이란?

1. 키 보존 테이블이란, 조인된 결과 집합을 통해서도 중복 값 없이 unique하게 식별히 가능한 테이블
2. 키 보존 테이블이란, 뷰에 rowid를 제공하는 테이블

=> EMP_DEPT_VIEW 뷰에서 rowid를 출력해보면, dept_rid에 중복값이 발생하고, emp_rid는 중복값이 없으며 뷰의 rowid와 일치한다.
=> dept테이블의 unique 인덱스를 제거하면 키 보존 테이블이 없기 때문에 뷰에서 rowid를 출력할 수 없다.

SQL> select ROWID, emp_rid, dept_rid, empno, deptno from EMP_DEPT_VIEW;

ROWID              EMP_RID            DEPT_RID                EMPNO     DEPTNO
------------------ ------------------ ------------------ ---------- ----------
AAAitBAAZAAADf8AAA AAAitBAAZAAADf8AAA AAAitCAAZAAADgEAAB       7369         20
AAAitBAAZAAADf8AAB AAAitBAAZAAADf8AAB AAAitCAAZAAADgEAAC       7499         30
AAAitBAAZAAADf8AAC AAAitBAAZAAADf8AAC AAAitCAAZAAADgEAAC       7521         30
AAAitBAAZAAADf8AAD AAAitBAAZAAADf8AAD AAAitCAAZAAADgEAAB       7566         20
AAAitBAAZAAADf8AAE AAAitBAAZAAADf8AAE AAAitCAAZAAADgEAAC       7654         30
AAAitBAAZAAADf8AAF AAAitBAAZAAADf8AAF AAAitCAAZAAADgEAAC       7698         30
AAAitBAAZAAADf8AAG AAAitBAAZAAADf8AAG AAAitCAAZAAADgEAAA       7782         10
AAAitBAAZAAADf8AAH AAAitBAAZAAADf8AAH AAAitCAAZAAADgEAAB       7788         20
AAAitBAAZAAADf8AAI AAAitBAAZAAADf8AAI AAAitCAAZAAADgEAAA       7839         10
AAAitBAAZAAADf8AAJ AAAitBAAZAAADf8AAJ AAAitCAAZAAADgEAAC       7844         30
AAAitBAAZAAADf8AAK AAAitBAAZAAADf8AAK AAAitCAAZAAADgEAAB       7876         20
AAAitBAAZAAADf8AAL AAAitBAAZAAADf8AAL AAAitCAAZAAADgEAAC       7900         30
AAAitBAAZAAADf8AAM AAAitBAAZAAADf8AAM AAAitCAAZAAADgEAAB       7902         20
AAAitBAAZAAADf8AAN AAAitBAAZAAADf8AAN AAAitCAAZAAADgEAAA       7934         10

14 rows selected.

SQL> alter table dept drop primary key;

Table altered.

SQL> select rowid, emp_rid, dept_rid, empno, deptno from EMP_DEPT_VIEW; 
select rowid, emp_rid, dept_rid, empno, deptno from EMP_DEPT_VIEW
                                                    *
ERROR at line 1:
ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

*_UPDATABLE_COLUMNS 뷰 참조

비 키-보존 테이블로부터 온 컬럼은 입력, 갱신, 삭제가 허용되지 않으며, *_UPDATABLE_COLUMNS 뷰를 통해 확인 할 수 있다.

SQL> alter table dept add constraint dept_pk primary key(deptno);

Table altered.

SQL> insert into EMP_DEPT_VIEW
  2        (empno, ename, job, mgr, hiredate, sal, comm, deptno, loc)
  3  select empno, ename, job, mgr, hiredate, sal, comm, deptno, loc 
  4  from EMP_DEPT_VIEW;
      (empno, ename, job, mgr, hiredate, sal, comm, deptno, loc)
                                                            *
ERROR at line 2:
ORA-01776: cannot modify more than one base table through a join view


SQL> select column_name, insertable, updatable, deletable
  2  from   user_updatable_columns
  3  where  table_name = 'EMP_DEPT_VIEW';

COLUMN_NAME                    INS UPD DEL
------------------------------ --- --- ---
EMP_RID                        YES YES YES
EMPNO                          YES YES YES
ENAME                          YES YES YES
JOB                            YES YES YES
MGR                            YES YES YES
HIREDATE                       YES YES YES
SAL                            YES YES YES
COMM                           YES YES YES
DEPTNO                         YES YES YES
DEPT_RID                       NO  NO  NO
DNAME                          NO  NO  NO
LOC                            NO  NO  NO

12 rows selected.

SQL> insert into EMP_DEPT_VIEW
  2        (empno, ename, job, mgr, hiredate, sal, comm, deptno)
  3  select empno, ename, job, mgr, hiredate, sal, comm, deptno 
  4  from EMP_DEPT_VIEW;

14 rows created.

SQL> commit;

Commit complete.

수정 가능 조인 뷰 제약 회피

bypass_ujvc 힌트는 키 보존 테이블이 없더라도 update 수행이 가능하게 하는 힌트이다.
update를 위해 참조하는 집합에 중복 레코드가 없을 때만 이 힌트를 사용해야 한다.

=> emp테이블에서 deptno로 group by한 결과는 unique하기 때문에 이 집합과 조인되는 dept 테이블은 키가 보존됨에도 에러가 발생한다.

SQL> alter table dept add avg_sal number(7,2);

Table altered.

SQL> update
  2  (select d.deptno, d.avg_sal d_avg_sal, e.avg_sal e_avg_sal
  3   from  (select deptno, round(avg(sal), 2) avg_sal from emp group by deptno) e
  4        , dept d
  5   where  d.deptno = e.deptno )
  6  set d_avg_sal = e_avg_sal ;
set d_avg_sal = e_avg_sal
    *
ERROR at line 6:
ORA-01779: cannot modify a column which maps to a non key-preserved table


SQL> update /*+ bypass_ujvc */
  2  (select d.deptno, d.avg_sal d_avg_sal, e.avg_sal e_avg_sal
  3   from  (select deptno, round(avg(sal), 2) avg_sal from emp group by deptno) e
  4        , dept d
  5    where  d.deptno = e.deptno )
  6  set d_avg_sal = e_avg_sal ;

3 rows updated.

SQL> select * from dept;

    DEPTNO DNAME      LOC           AVG_SAL
---------- ---------- ---------- ----------
        10 ACCOUNTING NEW YORK      2916.67
        20 RESEARCH   DALLAS           2175
        30 SALES      CHICAGO       1566.67
        40 OPERATIONS BOSTON

2. Merge문 활용

DW에서 데이터 적재 작업을 효과적으로 지원하게 위해 오라클 9i부터 merge into 문을 지원.
1. 전일 발생한 변경 데이터를 기간계 시스템으로 부터 추출 (Extraction)
2. customer_delta 테이블을 DW시스템으로 전송 (Transportation)
3. DW 시스템으로 적재 (Loading)

MERGE INTO customer t USING customer_delta s ON (t.cust_id = s.cust_id)
WHEN MATCHED THEN UPDATE
  SET t.cust_id = s.cust_id, t.cust_nm = s.cust_nm, t.email = s.email, ...
WHEN NOT MATCHED THEN INSERT
  (cust_id, cust_nm, email, tel_no, region, addr, reg_dt) VALUES
  (s.cust_id, s.cust_nm, s.email, s.tel_no, s.region, s.addr, s.reg_dt);

Optional Clauses

10g부터는 update와 insert를 선택적으로 처리할 수 있다.

MERGE INTO customer t USING customer_delta s ON (t.cust_id = s.cust_id)
WHEN MATCHED THEN UPDATE
  SET t.cust_id = s.cust_id, t.cust_nm = s.cust_nm, t.email = s.email, ...;

MERGE INTO customer t USING customer_delta s ON (t.cust_id = s.cust_id)
WHEN NOT MATCHED THEN INSERT
  (cust_id, cust_nm, email, tel_no, region, addr, reg_dt) VALUES
  (s.cust_id, s.cust_nm, s.email, s.tel_no, s.region, s.addr, s.reg_dt);

=> merge문으로 수정 가능 조인 뷰의 기능을 대체.

MERGE INTO dept d
USING (select deptno, round(avg(sal), 2) avg_sal from emp group by deptno) e
ON (d.deptno = e.deptno)
WHEN MATCHED THEN UPDATE set d.avg_sal = e.avg_sal;

Conditional Operations

10g에서는 on절에 기술한 조인문외에 추가로 조건절을 기술할 수 있다.

MERGE INTO customer t USING customer_delta s ON (t.cust_id = s.cust_id)
WHEN MATCHED THEN UPDATE
  SET t.cust_id = s.cust_id, t.cust_nm = s.cust_nm, t.email = s.email, ...
  WHERE reg_dt >= to_char('20000101','yyyymmdd')
WHEN NOT MATCHED THEN INSERT
  (cust_id, cust_nm, email, tel_no, region, addr, reg_dt) VALUES
  (s.cust_id, s.cust_nm, s.email, s.tel_no, s.region, s.addr, s.reg_dt)
  WHERE reg_dt < trunc(sysdate) ;

DELETE Clauses

10g에서는 merge문을 이용하여 이미 저장된 데이터를 조건에 따라 지울 수 있다.
=> update가 이루어진 결과로서 ?퇴일자가 null이 아닌 레코드만 삭제된다. 탈퇴일자가 null이 아니었어도 merge문을 수행한 결과가 null이면 삭제되지 않는다.

MERGE INTO customer t USING customer_delta s ON (t.cust_id = s.cust_id)
WHEN MATCHED THEN UPDATE
  SET t.cust_id = s.cust_id, t.cust_nm = s.cust_nm, t.email = s.email, ...
  DELETE WHERE t.withdraw_dt is not null --탈퇴일시가 null이 아닌 레코드 삭제
WHEN NOT MATCHED THEN INSERT
  (cust_id, cust_nm, email, tel_no, region, addr, reg_dt) VALUES
  (s.cust_id, s.cust_nm, s.email, s.tel_no, s.region, s.addr, s.reg_dt);

Merge Into 활용

1. SQL 수행 빈도 개선
저장하려는 레코드가 기존에 있던 것이면 update를 수행하고, 그렇지 않으면 insert를 수행하는 경우, SQL이 항상 두번씩 수행된다. (select 한번, insert 또는 update 한 번)
merger문을 활용하면 SQL이 한번만 수행된다
2. 논리I/O발생을 감소하여 SQL 수행 속도 개선
( 참고 : 298page ~ )

3. 다중 테이블 Insert 활용

오라클 9i부터는 조건에 따라 여러 테이블에 insert하는 다중 테이블 insert문을 제공한다.

INSERT INTO 청구보험당사자 ( 당사자ID, 접수일자, 접수순번, 담보구분, 청구순번, ...)
SELECT ...
FROM 청구보험당사자_임시 a, 거래당사자 b
WHERE a,당사자ID =b.당사자ID;

INSERT INTO 자동차사고접수당사자 ( 당사자ID, 접수일자, 접수순번, 담보구분, 청구순번, ...)
SELECT ...
FROM 가사고접수당사자_임시 a, 거래당사자 b
WHERE b.당사자구분 NOT IN ( '4','5','6')
AND a,당사자ID =b.당사자ID;

=> 다중 테이블 insert문을 활용하면 대용량 거래당사자 테이블을 한 번만 읽고 처리할 수 있다.

INSERT FIRST 
WHEN 구분 = 'A' THEN
  INTO 청구보험당사자 ( 당사자ID, 접수일자, 접수순번, 담보구분, 청구순번, ...)
  VALUES ( 당사자ID, 접수일자, 접수순번, 담보구분, 청구순번, ...)
WHEN 구분 = 'B' THEN
  INTO 자동차사고접수당사자 ( 당사자ID, 접수일자, 접수순번, 담보구분, 청구순번, ...)
  VALUES ( 당사자ID, 접수일자, 접수순번, 담보구분, 청구순번, ...)
SELECT a.당사자ID, a.접수일자, a.접수순번, a.담보구분, a.청구순번, ...
FROM (
	SELECT 'A' 구분
	FROM 청구보험당사자_임시
         UNION ALL
	SELECT 'B' 구분
	FROM 가사고접수당사자_임시
         WHERE 당사자구분 NOT IN ( '4','5','6')
      ) a, 거래당사자 b
WHERE a,당사자ID =b.당사자ID;

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.