1. HOME
  2. [종료]구루비 DB 스터디
  3. 2016년 상반기 - 오라클 데이터베이스 스터디
  4. 02. 조인 원리와 활용 (06. 스칼라 서브쿼리를 이용한 조인 ~ 08. 고급 조인 테크닉)
  5. 07. 조인을 내포한 DML 튜닝~

07. 조인을 내포한 DML 튜닝~ (by manon94) [2016.07.07]

(1) 수정 가능 조인 뷰 활용

전통적인 방식의 Update

  • 한달 이내 발생한 거래 정보로 고객 테이블 업데이트
항목별 서브쿼리 사용 비효율
{code:sql}
UPDATE 고객 c
SET 최종거래일시 = (SELECT MAX(거래일시) FROM 거래 WHERE 고객번호 = c.고객번호 AND 거래일시 >= ADD_MONTHS(TRUNC(sysdate), -1))
, 최근거래횟수 = (SELECT COUNT(*) FROM 거래 WHERE 고객번호 = c.고객번호 AND 거래일시 >= ADD_MONTHS(TRUNC(sysdate), -1))
, 최근거래금액 = (SELECT SUM(거래금액) FROM 거래 WHERE 고객번호 = c.고객번호 AND 거래일시 >= ADD_MONTHS(TRUNC(sysdate), -1))
WHERE EXISTS (SELECT 1 FROM 거래 WHERE 고객번호 = c.고객번호 AND 거래일시 >= ADD_MONTHS(TRUNC(sysdate), -1))
;
{code}
여러 항목을 한번의 서브쿼리로 개선 => set 절과 Where 절 2번의 서브쿼리 사용 비효율
{code:sql}
UPDATE 고객 c
SET (최종거래일시, 최근거래횟수, 최근거래금액)
= (SELECT MAX(거래일시)
, COUNT(*)
, SUM(거래금액)
FROM 거래
WHERE 고객번호 = c.고객번호
AND 거래일시 >= ADD_MONTHS(TRUNC(sysdate), -1)
)
WHERE EXISTS (SELECT 1 FROM 거래 WHERE 고객번호 = c.고객번호 AND 거래일시 >= ADD_MONTHS(TRUNC(sysdate), -1))
;
{code}
해시 새미 조인 방식으로 유도, Unnest hash_sj => 여전히 거래 2번 읽음
{code:sql}
UPDATE 고객 c
SET (최종거래일시, 최근거래횟수, 최근거래금액)
= (SELECT MAX(거래일시)
, COUNT(*)
, SUM(거래금액)
FROM 거래
WHERE 고객번호 = c.고객번호
AND 거래일시 >= ADD_MONTHS(TRUNC(sysdate), -1)
)
WHERE EXISTS (SELECT /*+ UNNEST HASH_SJ */ 1
FROM 거래
WHERE 고객번호 = c.고객번호
AND 거래일시 >= ADD_MONTHS(TRUNC(sysdate), -1)
)
;
{code}
Where 절을 생략하여 거래 한번만 읽기 => 전체 고객이 업데이트 되는 비효율
{code:sql}
UPDATE 고객 c
SET (최종거래일시, 최근거래횟수, 최근거래금액)
= (SELECT NVL(MAX(거래일시), c.최종거래일시)
, DECODE(COUNT(), 0, c.최근거래횟수, COUNT())
, NVL(SUM(거래금액), c.최근거래금액)
FROM 거래
WHERE 고객번호 = c.고객번호
AND 거래일시 >= ADD_MONTHS(TRUNC(sysdate), -1)
)
;
{code}

수정 가능 조인 뷰

  • 조인 뷰 : 두개 이상의 테이블이 조인 된 뷰
  • 수정 가능 조인 뷰 : (1:M) 조인 시 M 쪽 집합에만 DML 이 허용된다.
조인 뷰 업데이트 => 거래 두번 읽는 비효율 없음
{code:sql}

UPDATE /*+ BYPASS_UJVC */
(
SELECT c.최종거래일시, c.최근거래횟수, c.최근거래금액
, t.거래일시, t.거래횟수, t.거래금액
FROM (SELECT 고객번호
, MAX(거래일시) 거래일시
, COUNT(*) 거래횟수
, SUM(거래금액) 거래금액
FROM 거래
GROUP BY 고객번호
AND 거래일시 >= ADD_MONTHS(TRUNC(sysdate), -1)
) t
, 고객 c
WHERE t.고객번호 = c.고객번호
)
SET 최종거래일시 = 거래일시
, 최종거래횟수 = 거래횟수
, 최종거래금액 = 거래금액
;

|

|| 조인 뷰 수정 테스트 ||
|{code:sql}
SQL> CREATE TABLE emp1  AS SELECT * FROM emp;

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

SQL> CREATE TABLE dept1 AS SELECT * FROM dept;

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

SQL> CREATE OR REPLACE VIEW view_emp_dept
  2  AS
  3  SELECT e.ROWID emp_rid
  4       , e.*
  5       , d.ROWID dept_rid
  6       , d.dname, d.loc
  7    FROM emp1  e
  8       , dept1 d
  9   WHERE e.deptno = d.deptno
 10  ;

뷰가 생성되었습니다.

SQL> -- 1 쪽 집합(dept) Update --
SQL> UPDATE view_emp_dept SET loc = 'SEOUL' WHERE job = 'CLOCK';
UPDATE view_emp_dept SET loc = 'SEOUL' WHERE job = 'CLOCK'
                         *
1행에 오류:
ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다

SQL> -- M 쪽 집합(emp) Update --
SQL> UPDATE view_emp_dept SET sal = sal * 1.1 WHERE deptno = 30;
UPDATE view_emp_dept SET sal = sal * 1.1 WHERE deptno = 30
                         *
1행에 오류:
ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다

SQL> CREATE UNIQUE INDEX pk_dept1 ON dept1(deptno);

인덱스가 생성되었습니다.

SQL> -- M 쪽 집합(emp) Update --
SQL> UPDATE view_emp_dept SET sal = sal * 1.1 WHERE deptno = 30;

6 행이 갱신되었습니다.

SQL> CREATE UNIQUE INDEX pk_emp1 ON emp1(empno);

인덱스가 생성되었습니다.

SQL> -- 1 쪽 집합(dept) Update --
SQL> UPDATE view_emp_dept SET loc = 'SEOUL' WHERE job = 'CLOCK';
UPDATE view_emp_dept SET loc = 'SEOUL' WHERE job = 'CLOCK'
                         *
1행에 오류:
ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다

SQL>

|

키보존 테이블이란?

ROWID 확인 : 조인뷰의 ROWID 와 M쪽 테이블(EMP)의 ROWID 가 같음
{code:sql}
SQL> SELECT ROWID rid
2 , emp_rid
3 , dept_rid
4 , deptno
5 , empno
6 FROM view_emp_dept
7 ;

RID EMP_RID DEPT_RID DEPTNO EMPNO

--
--
--
--
--
AAATnYAAEAAAAJ7AAA AAATnYAAEAAAAJ7AAA AAATnZAAEAAAAoDAAC 20 7369
AAATnYAAEAAAAJ7AAB AAATnYAAEAAAAJ7AAB AAATnZAAEAAAAoDAAD 30 7499
AAATnYAAEAAAAJ7AAC AAATnYAAEAAAAJ7AAC AAATnZAAEAAAAoDAAD 30 7521
AAATnYAAEAAAAJ7AAD AAATnYAAEAAAAJ7AAD AAATnZAAEAAAAoDAAC 20 7566
AAATnYAAEAAAAJ7AAE AAATnYAAEAAAAJ7AAE AAATnZAAEAAAAoDAAD 30 7654
AAATnYAAEAAAAJ7AAF AAATnYAAEAAAAJ7AAF AAATnZAAEAAAAoDAAD 30 7698
AAATnYAAEAAAAJ7AAG AAATnYAAEAAAAJ7AAG AAATnZAAEAAAAoDAAB 10 7782
AAATnYAAEAAAAJ7AAH AAATnYAAEAAAAJ7AAH AAATnZAAEAAAAoDAAC 20 7788
AAATnYAAEAAAAJ7AAI AAATnYAAEAAAAJ7AAI AAATnZAAEAAAAoDAAB 10 7839
AAATnYAAEAAAAJ7AAJ AAATnYAAEAAAAJ7AAJ AAATnZAAEAAAAoDAAD 30 7844
AAATnYAAEAAAAJ7AAK AAATnYAAEAAAAJ7AAK AAATnZAAEAAAAoDAAC 20 7876
AAATnYAAEAAAAJ7AAL AAATnYAAEAAAAJ7AAL AAATnZAAEAAAAoDAAD 30 7900
AAATnYAAEAAAAJ7AAM AAATnYAAEAAAAJ7AAM AAATnZAAEAAAAoDAAC 20 7902
AAATnYAAEAAAAJ7AAN AAATnYAAEAAAAJ7AAN AAATnZAAEAAAAoDAAB 10 7934

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

SQL>

|

|| 키보존 안 된 뷰의 ROWID 조회 ||
|{code:sql}
SQL> DROP INDEX pk_dept1;

인덱스가 삭제되었습니다.

SQL> SELECT ROWID rid
  2       , emp_rid
  3       , dept_rid
  4       , deptno
  5       , empno
  6    FROM view_emp_dept
  7  ;
  FROM view_emp_dept
       *
6행에 오류:
ORA-01445: 키 보존 테이블이 없는 조인 뷰에서 ROWID를 선택할 수 없음

SQL> ALTER TABLE dept1 ADD CONSTRAINT pk_dept1 PRIMARY KEY(deptno);

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

SQL>

|

*_UPDATABLE_COLUMNS 뷰 참조

(1:M) 조인 뷰에서 M 쪽 테이블의 컬럼만 수정 가능
{code:sql}
SQL> INSERT INTO view_emp_dept(empno, ename, deptno, dname)
2 VALUES (9999, 'MANON94', 40, 'GURUBEE')
3 ;
INSERT INTO view_emp_dept(empno, ename, deptno, dname)
*
1행에 오류:
ORA-01776: 조인 뷰에 의하여 하나 이상의 기본 테이블을 수정할 수 없습니다.

SQL> SELECT *
2 FROM user_updatable_columns
3 WHERE table_name = 'VIEW_EMP_DEPT'
4 ;

OWNER TABLE_NAME COLUMN_NAME UPD INS DEL

--
--
-- --- --- ---
SCOTT VIEW_EMP_DEPT EMP_RID YES YES YES
SCOTT VIEW_EMP_DEPT EMPNO YES YES YES
SCOTT VIEW_EMP_DEPT ENAME YES YES YES
SCOTT VIEW_EMP_DEPT JOB YES YES YES
SCOTT VIEW_EMP_DEPT MGR YES YES YES
SCOTT VIEW_EMP_DEPT HIREDATE YES YES YES
SCOTT VIEW_EMP_DEPT SAL YES YES YES
SCOTT VIEW_EMP_DEPT COMM YES YES YES
SCOTT VIEW_EMP_DEPT DEPTNO YES YES YES
SCOTT VIEW_EMP_DEPT DEPT_RID NO NO NO
SCOTT VIEW_EMP_DEPT DNAME NO NO NO
SCOTT VIEW_EMP_DEPT LOC NO NO NO

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

SQL> INSERT INTO view_emp_dept(empno, ename, deptno)
2 VALUES (9999, 'MANON94', 40)
3 ;

1 개의 행이 만들어졌습니다.

SQL>

|

h4. 수정가능 조인 뷰 제약 회피


|| Group By 결과가 Unique 함에도 불구하고 에러 발생 ||
|{code:sql}
SQL> ALTER TABLE dept1 ADD avg_sal NUMBER(7,2);

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

SQL> UPDATE
  2  (
  3  SELECT d.avg_sal, e.sal
  4    FROM dept1 d
  5       , (SELECT deptno, AVG(sal) sal FROM emp GROUP BY deptno) e
  6   WHERE d.deptno = e.deptno
  7  )
  8     SET avg_sal = sal
  9  ;
   SET avg_sal = sal
       *
8행에 오류:
ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다


SQL>

|

BYPASS_UJVC 힌트 사용 => 11G 부터는 해당 힌트 사용 못함
{code:sql}
SQL> UPDATE /*+ BYPASS_UJVC */
2 (
3 SELECT d.avg_sal, e.sal
4 FROM dept1 d
5 , (SELECT deptno, AVG(sal) sal FROM emp GROUP BY deptno) e
6 WHERE d.deptno = e.deptno
7 )
8 SET avg_sal = sal
9 ;
SET avg_sal = sal
*
8행에 오류:
ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다

SQL>

|

h2. (2) Merge 문 활용

|| Merge ||
|{code:sql}
SQL> MERGE INTO dept1 d
  2  USING (SELECT deptno, AVG(sal) sal FROM emp GROUP BY deptno) e
  3  ON (d.deptno = e.deptno)
  4  WHEN MATCHED THEN
  5  UPDATE SET d.avg_sal = e.sal
  6  ;

3 행이 병합되었습니다.

SQL>

|

Merge 활용 1 : 있으면 Update / 없으면 Insert
{code:sql}
DECLARE
v_deptno NUMBER := 50;
v_dname VARCHAR2(10) := 'GURUBEE';
v_cnt NUMBER;
BEGIN
SELECT COUNT(*) INTO v_cnt FROM dept1 WHERE deptno = v_deptno;
IF v_cnt = 0 THEN
INSERT INTO dept1(deptno, dname) VALUES(v_deptno, v_dname);
ELSE
UPDATE dept1 SET dname = v_dname WHERE deptno = v_deptno;
END IF;
END;
/

MERGE INTO dept1
USING dual
ON (deptno = 50)
WHEN MATCHED THEN
UPDATE SET dname = 'GURUBEE'
WHEN NOT MATCHED THEN
INSERT (deptno, dname) VALUES(50, 'GURUBEE')
;

|

|| Merge 활용 2 : 일련번호 Update 개선 ||
|{code:sql}
SQL> UPDATE emp1 e
  2     SET comm = (SELECT rn
  3                   FROM (SELECT empno
  4                              , ROW_NUMBER() OVER(ORDER BY ename) rn
  5                           FROM emp
  6                         )
  7                  WHERE empno = e.empno
  8                 )
  9  ;

15 행이 갱신되었습니다.


Execution Plan
----------------------------------------------------------
Plan hash value: 3002760866

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT     |      |    15 |   390 |    79  (38)| 00:00:01 |
|   1 |  UPDATE              | EMP1 |       |       |            |          |
|   2 |   TABLE ACCESS FULL  | EMP1 |    15 |   390 |     4   (0)| 00:00:01 |
|*  3 |   VIEW               |      |    14 |   364 |     4  (25)| 00:00:01 |
|   4 |    WINDOW SORT       |      |    14 |   140 |     4  (25)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| EMP  |    14 |   140 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   3 - filter("EMPNO"=:B1)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          4  recursive calls
         30  db block gets
        120  consistent gets
          0  physical reads
       6692  redo size
        689  bytes sent via SQL*Net to client
        827  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
         16  sorts (memory)
          0  sorts (disk)
         15  rows processed

SQL> MERGE INTO emp1 a
  2  USING (SELECT empno
  3              , ROW_NUMBER() OVER(ORDER BY ename) rn
  4           FROM emp
  5         ) b
  6  ON (a.empno = b.empno)
  7  WHEN MATCHED THEN
  8  UPDATE SET a.comm = b.rn
  9  ;

14 행이 병합되었습니다.


Execution Plan
----------------------------------------------------------
Plan hash value: 2424884718

------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                |         |    14 |   364 |     7  (29)| 00:00:01 |
|   1 |  MERGE                         | EMP1    |       |       |            |          |
|   2 |   VIEW                         |         |       |       |            |          |
|   3 |    MERGE JOIN                  |         |    14 |  1750 |     7  (29)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| EMP1    |    15 |  1485 |     2   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN           | PK_EMP1 |    15 |       |     1   (0)| 00:00:01 |
|*  6 |     SORT JOIN                  |         |    14 |   364 |     5  (40)| 00:00:01 |
|   7 |      VIEW                      |         |    14 |   364 |     4  (25)| 00:00:01 |
|   8 |       WINDOW SORT              |         |    14 |   140 |     4  (25)| 00:00:01 |
|   9 |        TABLE ACCESS FULL       | EMP     |    14 |   140 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   6 - access("A"."EMPNO"="B"."EMPNO")
       filter("A"."EMPNO"="B"."EMPNO")

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          7  recursive calls
         14  db block gets
         27  consistent gets
          0  physical reads
       3404  redo size
        690  bytes sent via SQL*Net to client
        752  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL>

|

Merge 구문 사용법 : http://docs.oracle.com/database/121/SQLRF/statements_9016.htm#SQLRF01606

(3) 다중 테이블 Insert 활용

Insert First
{code:sql}
– 1. 부서별로 여러번 나누어 처리 --
INSERT INTO dept_10 SELECT * FROM dept WHERE deptno = 10;
INSERT INTO dept_20 SELECT * FROM dept WHERE deptno = 20;
INSERT INTO dept_30 SELECT * FROM dept WHERE deptno = 30;
– 2. 한방에 처리 --
INSERT FIRST
WHEN deptno = 10 THEN INTO dept_10 VALUES(deptno, dname, loc)
WHEN deptno = 20 THEN INTO dept_20 VALUES(deptno, dname, loc)
WHEN deptno = 30 THEN INTO dept_30 VALUES(deptno, dname, loc)
SELECT * FROM dept
;
{code}
  1. HOME
  2. [종료]구루비 DB 스터디
  3. 2016년 상반기 - 오라클 데이터베이스 스터디
  4. 02. 조인 원리와 활용 (06. 스칼라 서브쿼리를 이용한 조인 ~ 08. 고급 조인 테크닉)
  5. 07. 조인을 내포한 DML 튜닝~