오라클 성능 고도화 원리와 해법 II (2012년)
조인을 내포한 DML 튜닝 0 0 57,573

by 구루비스터디 조인원리 BYPASS_UJVC MERGE 키보존테이블 [2018.04.01]


  1. I. 수정 가능 조인 뷰 활용
    1. 1. 전통적인 방식의 UPDATE
    2. 2. 수정 가능 조인 뷰
    3. 키 보존 테이블이란?
    4. _UPDATABLE_COLUMNS 뷰 참조
    5. 수정 가능 조인 뷰 제약 회피
  2. 2. Merge문 활용
    1. Optional Clauses
    2. Conditional Operations
    3. DELETE Clauses
    4. Merge Into 활용
  3. 3. 다중 테이블 Insert 활용


I. 수정 가능 조인 뷰 활용

1. 전통적인 방식의 UPDATE


1. test script 준비(2010 하반기 SCRIPT 참조)
- 고객 테이블 및 데이터 생성
SQL> CREATE TABLE 고객 AS
  2  SELECT LEVEL AS 고객번호
  3       , (SELECT SYSDATE - CEIL(DBMS_RANDOM.VALUE(1, 365)) FROM DUAL) AS 최종거래일시
  4       , 0 AS 최근거래횟수
  5       , 0 AS 최근거래금액
  6    FROM DUAL
  7  CONNECT BY LEVEL <= 1000000;

테이블이 생성되었습니다.

- 고객 테이블 인덱스 생성
SQL> ALTER TABLE 고객 ADD CONSTRAINT IDX_고객_PK PRIMARY KEY(고객번호);

테이블이 변경되었습니다.

- 거래 테이블 및 데이터 생성
SQL> CREATE TABLE 거래 AS
  2  SELECT CEIL(LEVEL / 1000000) 고객번호
  3       , ADD_MONTHS(SYSDATE,-4) + FLOOR( DBMS_RANDOM.VALUE(1,120) ) AS 거래일시
  4       , (FLOOR( DBMS_RANDOM.VALUE(1,13) )*100) + 500 AS 거래금액
  5    FROM DUAL
  6  CONNECT BY LEVEL <= 10000000;

테이블이 생성되었습니다.




※ SQL TRACE
********************************************************************************
SQL> EXPLAIN PLAN FOR
  2  UPDATE 고객 c
  3  SET  최종거래일시 = ( SELECT MAX(거래일시) FROM 거래
  4                         WHERE 고객번호 = c.고객번호
  5                         AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)))
  6     , 최근거래횟수 = ( SELECT COUNT(*) FROM 거래
  7                         WHERE 고객번호 = c.고객번호
  8                         AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)))
  9     , 최근거래금액 = ( SELECT SUM(거래금액) FROM 거래
 10                         WHERE 고객번호 = c.고객번호
 11                         AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)))
 12  WHERE EXISTS ( SELECT 'x' FROM 거래
 13                   WHERE 고객번호 = c.고객번호
 14                   AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)));

해석되었습니다.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 240844059

------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |      |    10 |   700 |       | 13553   (9)| 00:02:43 |
|   1 |  UPDATE             | 고객 |       |       |       |            |          |
|*  2 |   HASH JOIN SEMI    |      |    10 |   700 |    59M| 13553   (9)| 00:02:43 |
|   3 |    TABLE ACCESS FULL| 고객 |  1035K|    47M|       |   720   (6)| 00:00:09 |
|*  4 |    TABLE ACCESS FULL| 거래 |  1832K|    38M|       |  6852  (16)| 00:01:23 |
|   5 |   SORT AGGREGATE    |      |     1 |    22 |       |            |          |
|*  6 |    TABLE ACCESS FULL| 거래 | 18326 |   393K|       |  6135   (6)| 00:01:14 |
|   7 |   SORT AGGREGATE    |      |     1 |    22 |       |            |          |
|*  8 |    TABLE ACCESS FULL| 거래 | 18326 |   393K|       |  6135   (6)| 00:01:14 |
|   9 |   SORT AGGREGATE    |      |     1 |    35 |       |            |          |
|* 10 |    TABLE ACCESS FULL| 거래 | 18326 |   626K|       |  6135   (6)| 00:01:14 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("고객번호"="C"."고객번호")

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
   4 - filter("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)))
   6 - filter("고객번호"=:B1 AND "거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)))
   8 - filter("고객번호"=:B1 AND "거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)))
  10 - filter("고객번호"=:B1 AND "거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)))

Note
-----
   - dynamic sampling used for this statement

※Semi Join (반조인)
선행 Table의 Row가 수행 Table의 Row와 Match되기만 하면 즉각 Join 조건이 만족된 것으로 간주하고 해당 Row에 
대해서는 더 이상의 탐색을 진행하지 않는다. 따라서 보다 효율적이다.
Exists와 In Operation의 효율적인 처리를 위한 고안된 Join 방식이다.
주로 Hash Join(Hash Semi Join)의 형태나 Nested Loops Join(Nested Loops Semi Join)의 형태로 구현된다.
Sort Merge Semi Join 또한 이론적으로는 발생 가능하다.
********************************************************************************

※ TKPROF LOG
********************************************************************************
UPDATE 고객 c
SET  최종거래일시 = ( SELECT MAX(거래일시) FROM 거래
                       WHERE 고객번호 = c.고객번호
                       AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)))
   , 최근거래횟수 = ( SELECT COUNT(*) FROM 거래
                       WHERE 고객번호 = c.고객번호
                       AND 거래일시 >= TRUNC(ADD_MONTHet(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)))

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.18          0          7          0           0
Execute      1     87.45     115.94      29727     819213         23          10
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     87.46     116.12      29727     819220         23          10

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  고객 (cr=819213 pr=29727 pw=4867 time=115941430 us)
     10   HASH JOIN SEMI (cr=29373 pr=6550 pw=4867 time=24677884 us)
1000000    TABLE ACCESS FULL 고객 (cr=3045 pr=0 pw=0 time=5000047 us)
2352766    TABLE ACCESS FULL 거래 (cr=26328 pr=2613 pw=0 time=54130598 us)
     10   SORT AGGREGATE (cr=263280 pr=23177 pw=0 time=40484285 us)
2352766    TABLE ACCESS FULL 거래 (cr=263280 pr=23177 pw=0 time=37635492 us)
     10   SORT AGGREGATE (cr=263280 pr=0 pw=0 time=25108757 us)
2352766    TABLE ACCESS FULL 거래 (cr=263280 pr=0 pw=0 time=27684837 us)
     10   SORT AGGREGATE (cr=263280 pr=0 pw=0 time=25310851 us)
2352766    TABLE ACCESS FULL 거래 (cr=263280 pr=0 pw=0 time=27495547 us)

********************************************************************************



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

※ SQL TRACE
********************************************************************************
SQL> EXPLAIN PLAN FOR
  2  UPDATE 고객 c
  3  SET  ( 최종거래일시,  최근거래횟수, 최근거래금액 ) =
  4       ( SELECT MAX(거래일시), COUNT(*), SUM(거래금액)
  5     FROM 거래
  6           WHERE 고객번호 = c.고객번호
  7           AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)))
  8  WHERE EXISTS ( SELECT 'x' FROM 거래
  9                   WHERE 고객번호 = c.고객번호
 10                   AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)));

해석되었습니다.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 613077917

------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |      |    10 |   700 |       | 13553   (9)| 00:02:43 |
|   1 |  UPDATE             | 고객 |       |       |       |            |          |
|*  2 |   HASH JOIN SEMI    |      |    10 |   700 |    59M| 13553   (9)| 00:02:43 |
|   3 |    TABLE ACCESS FULL| 고객 |  1035K|    47M|       |   720   (6)| 00:00:09 |
|*  4 |    TABLE ACCESS FULL| 거래 |  1832K|    38M|       |  6852  (16)| 00:01:23 |
|   5 |   SORT AGGREGATE    |      |     1 |    35 |       |            |          |
|*  6 |    TABLE ACCESS FULL| 거래 | 18326 |   626K|       |  6135   (6)| 00:01:14 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("고객번호"="C"."고객번호")
   4 - filter("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)))
   6 - filter("고객번호"=:B1 AND "거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)))

Note

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
-----
   - dynamic sampling used for this statement
********************************************************************************

※ TKPROF LOG
********************************************************************************
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)))

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          4          0           0
Execute      1     35.79      42.29       3937     292653         20          10
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     35.81      42.30       3937     292657         20          10

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  고객 (cr=292653 pr=3937 pw=4867 time=42291709 us)
     10   HASH JOIN SEMI (cr=29373 pr=3937 pw=4867 time=16552524 us)
1000000    TABLE ACCESS FULL 고객 (cr=3045 pr=0 pw=0 time=4000037 us)
2352766    TABLE ACCESS FULL 거래 (cr=26328 pr=0 pw=0 time=21174969 us)
     10   SORT AGGREGATE (cr=263280 pr=0 pw=0 time=25322376 us)
2352766    TABLE ACCESS FULL 거래 (cr=263280 pr=0 pw=0 time=29662943 us)
********************************************************************************



-- 총 고객수가 많다면 exists 서브 쿼리를 아래와 같이 해시 세미 조인으로 유도

※ SQL TRACE
********************************************************************************
SQL> EXPLAIN PLAN FOR
  2  UPDATE 고객 c
  3  SET  ( 최종거래일시,  최근거래횟수, 최근거래금액 ) =
  4       ( SELECT MAX(거래일시), COUNT(*), SUM(거래금액)
  5     FROM 거래
  6           WHERE 고객번호 = c.고객번호
  7           AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)))
  8  WHERE EXISTS ( SELECT /*+ unnest hash_sj */ 'x' FROM 거래
  9                   WHERE 고객번호 = c.고객번호
 10                   AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)));

해석되었습니다.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 613077917

------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |      |    10 |   700 |       | 13553   (9)| 00:02:43 |
|   1 |  UPDATE             | 고객 |       |       |       |            |          |
|*  2 |   HASH JOIN SEMI    |      |    10 |   700 |    59M| 13553   (9)| 00:02:43 |
|   3 |    TABLE ACCESS FULL| 고객 |  1035K|    47M|       |   720   (6)| 00:00:09 |
|*  4 |    TABLE ACCESS FULL| 거래 |  1832K|    38M|       |  6852  (16)| 00:01:23 |
|   5 |   SORT AGGREGATE    |      |     1 |    35 |       |            |          |
|*  6 |    TABLE ACCESS FULL| 거래 | 18326 |   626K|       |  6135   (6)| 00:01:14 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("고객번호"="C"."고객번호")
   4 - filter("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)))
   6 - filter("고객번호"=:B1 AND "거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)))

Note

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
-----
   - dynamic sampling used for this statement
********************************************************************************

※ TKPROF LOG
********************************************************************************
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)))

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          4          0           0
Execute      1     36.57      42.91       3937     292653         20          10
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     36.57      42.91       3937     292657         20          10

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  고객 (cr=292653 pr=3937 pw=4867 time=42912267 us)
     10   HASH JOIN SEMI (cr=29373 pr=3937 pw=4867 time=16109592 us)
1000000    TABLE ACCESS FULL 고객 (cr=3045 pr=0 pw=0 time=4000038 us)
2352766    TABLE ACCESS FULL 거래 (cr=26328 pr=0 pw=0 time=21174967 us)
     10   SORT AGGREGATE (cr=263280 pr=0 pw=0 time=25995009 us)
2352766    TABLE ACCESS FULL 거래 (cr=263280 pr=0 pw=0 time=29817758 us)
********************************************************************************



-- 한 달 이내 거래를 발생시킨 고객이 많아 update발생량이 많다면 아래와 같이 변경할 수 있으나, 
-- 모든 고객 레코드에 lock이 발생하고 이전과 같은 값으로 갱신되는 비중이 높을수록 Redo 로그 발생량이 증가

※ SQL TRACE
********************************************************************************
SQL> EXPLAIN PLAN FOR
  2  UPDATE 고객 c
  3  SET  ( 최종거래일시,  최근거래횟수, 최근거래금액 ) =
  4       ( SELECT NVL(MAX(거래일시), c.최종거래일시)
  5                , DECODE( COUNT(*), 0, c.최근거래횟수, COUNT(*))
  6                , NVL(SUM(거래금액), c.최근거래금액)
  7         FROM 거래
  8          WHERE 고객번호 = c.고객번호
  9          AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)));

해석되었습니다.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 1071116265

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |      |  1035K|    47M|   720   (6)| 00:00:09 |
|   1 |  UPDATE             | 고객 |       |       |            |          |
|   2 |   TABLE ACCESS FULL | 고객 |  1035K|    47M|   720   (6)| 00:00:09 |
|   3 |   SORT AGGREGATE    |      |     1 |    35 |            |          |
|*  4 |    TABLE ACCESS FULL| 거래 | 18326 |   626K|  6135   (6)| 00:01:14 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("고객번호"=:B1 AND "거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)))

Note
-----
   - dynamic sampling used for this statement
********************************************************************************



  • 다른 테이블과 조인이 필요할때 전통적인 방식의 update문을 사용하면 비효율을 감수해야 한다.


2. 수정 가능 조인 뷰

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

SQL> EXPLAIN PLAN FOR
  2  UPDATE /*+ bypass_ujvc */ --bypass_ujvc 힌트는 view update시에 키보존을 생략할수 있게 해주는 힌트이다.. 
  3  ( SELECT /*+ ordered use_hash(c) */
  4           c.최종거래일시,  c.최근거래횟수, c.최근거래금액
  5            , t.거래일시,  t.거래횟수, t.거래금액
  6  FROM ( SELECT 고객번호, MAX(거래일시) 거래일시, COUNT(*) 거래횟수, SUM(거래금액) 거래금액
  7           FROM 거래
  8           WHERE 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1))
  9          GROUP BY 고객번호) t, 고객 c
 10  WHERE  c.고객번호 = t.고객번호
 11  )
 12  SET 최종거래일시 = 거래일시
 13    , 최근거래횟수 = 거래횟수
 14    , 최근거래금액 = 거래금액 ;

해석되었습니다.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
Plan hash value: 1452902634

--------------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |      |  1832K|   167M|       | 16119  (10)| 00:03:14 |
|   1 |  UPDATE               | 고객 |       |       |       |            |          |
|*  2 |   HASH JOIN           |      |  1832K|   167M|   104M| 16119  (10)| 00:03:14 |
|   3 |    VIEW               |      |  1832K|    83M|       |  7155  (19)| 00:01:26 |
|   4 |     SORT GROUP BY     |      |  1832K|    61M|       |  7155  (19)| 00:01:26 |
|*  5 |      TABLE ACCESS FULL| 거래 |  1832K|    61M|       |  6859  (16)| 00:01:23 |
|   6 |    TABLE ACCESS FULL  | 고객 |  1035K|    47M|       |   720   (6)| 00:00:09 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C"."고객번호"="T"."고객번호")
   5 - filter("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)))

Note
-----

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
   - dynamic sampling used for this statement



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

SQL> CREATE TABLE EMP AS SELECT * FROM SCOTT.EMP;
CREATE TABLE EMP AS SELECT * FROM SCOTT.EMP
             *
1행에 오류:
ORA-00955: 기존의 객체가 이름을 사용하고 있습니다.


SQL> CONN / AS SYSDBA
연결되었습니다.
SQL> CREATE TABLE EMP AS SELECT * FROM SCOTT.EMP;

테이블이 생성되었습니다.

SQL> CREATE TABLE DEPT AS SELECT * FROM SCOTT.DEPT;

테이블이 생성되었습니다.

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;

뷰가 생성되었습니다.

SQL> UPDATE EMP_DEPT_VIEW SET LOC = 'SEOUL' WHERE JOB = 'CLERK';
UPDATE EMP_DEPT_VIEW SET LOC = 'SEOUL' WHERE JOB = 'CLERK'
                         *
1행에 오류:
ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다

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 개의 행이 선택되었습니다.

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
                         *
1행에 오류:
ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다


SQL> DELETE FROM EMP_DEPT_VIEW WHERE JOB = 'CLERK';
DELETE FROM EMP_DEPT_VIEW WHERE JOB = 'CLERK'
            *
1행에 오류:
ORA-01752: 뷰으로 부터 정확하게 하나의 키-보전된 테이블 없이 삭제할 수 없습니다

SQL> ALTER TABLE DEPT ADD CONSTRAINT DEPT_PK PRIMARY KEY(DEPTNO);

테이블이 변경되었습니다.

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

7 행이 갱신되었습니다.

SQL> COMMIT;

커밋이 완료되었습니다.


키 보존 테이블이란?

  • 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
------------------ ------------------ ------------------ ---------- ----------
AAANM6AABAAAOxaAAA AAANM6AABAAAOxaAAA AAANM7AABAAAOxiAAB       7369         20
AAANM6AABAAAOxaAAB AAANM6AABAAAOxaAAB AAANM7AABAAAOxiAAC       7499         30
AAANM6AABAAAOxaAAC AAANM6AABAAAOxaAAC AAANM7AABAAAOxiAAC       7521         30
AAANM6AABAAAOxaAAD AAANM6AABAAAOxaAAD AAANM7AABAAAOxiAAB       7566         20
AAANM6AABAAAOxaAAE AAANM6AABAAAOxaAAE AAANM7AABAAAOxiAAC       7654         30
AAANM6AABAAAOxaAAF AAANM6AABAAAOxaAAF AAANM7AABAAAOxiAAC       7698         30
AAANM6AABAAAOxaAAG AAANM6AABAAAOxaAAG AAANM7AABAAAOxiAAA       7782         10
AAANM6AABAAAOxaAAH AAANM6AABAAAOxaAAH AAANM7AABAAAOxiAAB       7788         20
AAANM6AABAAAOxaAAI AAANM6AABAAAOxaAAI AAANM7AABAAAOxiAAA       7839         10
AAANM6AABAAAOxaAAJ AAANM6AABAAAOxaAAJ AAANM7AABAAAOxiAAC       7844         30
AAANM6AABAAAOxaAAK AAANM6AABAAAOxaAAK AAANM7AABAAAOxiAAB       7876         20
AAANM6AABAAAOxaAAL AAANM6AABAAAOxaAAL AAANM7AABAAAOxiAAC       7900         30
AAANM6AABAAAOxaAAM AAANM6AABAAAOxaAAM AAANM7AABAAAOxiAAB       7902         20
AAANM6AABAAAOxaAAN AAANM6AABAAAOxaAAN AAANM7AABAAAOxiAAA       7934         10

14 개의 행이 선택되었습니다.

SQL> ALTER TABLE DEPT DROP PRIMARY KEY;

테이블이 변경되었습니다.

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
                                                    *
1행에 오류:
ORA-01445: 키 보존 테이블이 없는 조인 뷰에서 ROWID를 선택할 수 없음


_UPDATABLE_COLUMNS 뷰 참조

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



SQL> ALTER TABLE DEPT ADD CONSTRAINT DEPT_PK PRIMARY KEY(DEPTNO);

테이블이 변경되었습니다.

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)
                                                      *
2행에 오류:
ORA-01776: 조인 뷰에 의하여 하나 이상의 기본 테이블을 수정할 수 없습니다.

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

COLUMN_NAME                    INS UPD DEL
------------------------------ --- --- ---
LOC                            NO  NO  NO

12 개의 행이 선택되었습니다.

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 개의 행이 만들어졌습니다.

SQL> COMMIT;

커밋이 완료되었습니다.

|


수정 가능 조인 뷰 제약 회피

  • bypass_ujvc 힌트는 키 보존 테이블이 없더라도 update 수행이 가능하게 하는 힌트이다.
  • update를 위해 참조하는 집합에 중복 레코드가 없을 때만 이 힌트를 사용해야 한다.
  • => emp테이블에서 deptno로 group by한 결과는 unique하기 때문에 이 집합과 조인되는 dept 테이블은 키가 보존됨에도 에러가 발생한다.



SQL> ALTER TABLE DEPT ADD AVG_SAL NUMBER(7,2);

테이블이 변경되었습니다.

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
    *
6행에 오류:
ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다

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 행이 갱신되었습니다.

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

※ SQL TRACE
********************************************************************************
SQL> EXPLAIN PLAN FOR
  2  UPDATE /*+ BYPASS_UJVC */
  3  (SELECT D.DEPTNO, D.AVG_SAL D_AVG_SAL, E.AVG_SAL E_AVG_SAL
  4   FROM (SELECT DEPTNO, ROUND(AVG(SAL), 2) AVG_SAL FROM EMP GROUP BY DEPTNO) E
  5         , DEPT D
  6  WHERE D.DEPTNO = E.DEPTNO)
  7  SET D_AVG_SAL = E_AVG_SAL;

해석되었습니다.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 1287183684

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT              |         |    28 |  1456 |     4  (25)| 00:00:01 |
|   1 |  UPDATE                       | DEPT    |       |       |            |          |
|   2 |   NESTED LOOPS                |         |    28 |  1456 |     4  (25)| 00:00:01 |
|   3 |    VIEW                       |         |    28 |   728 |     3  (34)| 00:00:01 |
|   4 |     SORT GROUP BY             |         |    28 |   728 |     3  (34)| 00:00:01 |
|   5 |      TABLE ACCESS FULL        | EMP     |    28 |   728 |     2   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    26 |     1   (0)| 00:00:01 |
|*  7 |     INDEX UNIQUE SCAN         | DEPT_PK |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - access("D"."DEPTNO"="E"."DEPTNO")
********************************************************************************

※ TKPROF LOG
********************************************************************************
UPDATE /*+ BYPASS_UJVC */
(SELECT D.DEPTNO, D.AVG_SAL D_AVG_SAL, E.AVG_SAL 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

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.03          0          3          0           0
Execute      1      0.00       0.00          0          8          1           3
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.03          0         11          1           3

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  DEPT (cr=8 pr=0 pw=0 time=476 us)
      3   NESTED LOOPS  (cr=8 pr=0 pw=0 time=382 us)
      3    VIEW  (cr=3 pr=0 pw=0 time=199 us)
      3     SORT GROUP BY (cr=3 pr=0 pw=0 time=172 us)
     28      TABLE ACCESS FULL EMP (cr=3 pr=0 pw=0 time=137 us)
      3    TABLE ACCESS BY INDEX ROWID DEPT (cr=5 pr=0 pw=0 time=134 us)
      3     INDEX UNIQUE SCAN DEPT_PK (cr=2 pr=0 pw=0 time=49 us)(object id 54078)
********************************************************************************


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;

"구루비 데이터베이스 스터디모임" 에서 2012년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3272

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입