h3.(1) 수정 가능 조인 뷰 활용
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)));
1 행이 갱신되었습니다.
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 70 | 330 (3)| 00:00:04 |
| 1 | UPDATE | 고객 | | | | |
|* 2 | HASH JOIN RIGHT SEMI| | 1 | 70 | 138 (3)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | 거래 | 21600 | 464K| 65 (4)| 00:00:01 |
| 4 | TABLE ACCESS FULL | 고객 | 99880 | 4681K| 72 (2)| 00:00:01 |
| 5 | SORT AGGREGATE | | 1 | 22 | | |
|* 6 | TABLE ACCESS FULL | 거래 | 216 | 4752 | 64 (2)| 00:00:01 |
| 7 | SORT AGGREGATE | | 1 | 22 | | |
|* 8 | TABLE ACCESS FULL | 거래 | 216 | 4752 | 64 (2)| 00:00:01 |
| 9 | SORT AGGREGATE | | 1 | 35 | | |
|* 10 | TABLE ACCESS FULL | 거래 | 216 | 7560 | 64 (2)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("고객번호"="C"."고객번호")
3 - 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
조인 뷰 란 ?
update /*+ bypass_ujvc */
(
select /*+ ordered use_hash(c)*/
c.최종거래일시, c.최근거래횟수, c.최근거래금액
, t.거래일시, t.거래횟수, t.거래금액
from (select /*+ index(a idx_거래_01 )*/
고객번호, max(거래일시) 거래일시, count(*) 거래횟수, sum(거래금액) 거래금액
from 거래 a
where 거래일시 >= trunc( add_months( sysdate, -1))
group by 고객번호
)t
, 고객 c
where c.고객번호 = t.고객번호
)
set 최종거래일시 = 거래일시
, 최근거래횟수 = 거래횟수
, 최근거래금액 = 거래금액;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 21600 | 2025K| | 486 (2)| 00:00:06 |
| 1 | UPDATE | 고객 | | | | | |
|* 2 | HASH JOIN | | 21600 | 2025K| 1272K| 486 (2)| 00:00:06 |
| 3 | VIEW | | 21600 | 1012K| | 67 (6)| 00:00:01 |
| 4 | SORT GROUP BY | | 21600 | 738K| | 67 (6)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| 거래 | 21600 | 738K| | 66 (5)| 00:00:01 |
| 6 | TABLE ACCESS FULL | 고객 | 99880 | 4681K| | 72 (2)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C"."고객번호"="T"."고객번호")
5 - filter("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)))
Note
-----
- dynamic sampling used for this statement
-- test를 위한 table 생성
SQL> CREATE TABLE EMP AS SELECT * FROM SCOTT.EMP;
테이블이 생성되었습니다.
SQL> CREATE TABLE DEPT AS SELECT * FROM SCOTT.DEPT;
테이블이 생성되었습니다.
SQL> CREATE 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> 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> SELECT * FROM DEPT;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80/12/17 800 80 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 625 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1525 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7788 SCOTT ANALYST 7566 87/03/20 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 150 30
7876 ADAMS CLERK 7788 87/05/23 1100 110 20
7900 JAMES CLERK 7698 81/12/03 950 95 30
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 130 10
14 개의 행이 선택되었습니다.
-- update , delete , insert 시 error 발생
UPDATE EMP_DEPT_VIEW SET LOC = 'SEOUL' WHERE JOB = 'CLERK';
*
1행에 오류:
ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다
UPDATE EMP_DEPT_VIEW SET COMM = NVL(COMM, 0) +(SAL * 0.1 ) WHERE SAL <= 1500;
*
1행에 오류:
ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다
DELETE FROM EMP_DEPT_VIEW WHERE JOB = 'CLERK';
*
1행에 오류:
ORA-01752: 뷰으로 부터 정확하게 하나의 키-보전된 테이블 없이 삭제할 수 없습니다
INSERT INTO EMP_DEPT_VIEW (DEPTNO, DNAME, LOC) VALUES(50,'TEST','TEST');
*
1행에 오류:
ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다
error의 원인:
dept table에 unique index 생성이 되어있지 않아 생긴 error!
옵티마이저 입장에서 어느 쪽이 1집합인지 알 수 없기 때문에 error발생
해결 방안 :
1쪽 PK 또는 unique index를 생성한다.
ALTER TABLE DEPT ADD CONSTRAINT DEPT_PK PRIMARY KEY(DEPTNO);
테이블이 변경되었습니다.
UPDATE EMP_DEPT_VIEW SET COMM = NVL(COMM, 0) +(SAL * 0.1 ) WHERE SAL <= 1500;
7 행이 갱신되었습니다.
키 보존 테이블이란?
SQL> ALTER TABLE DEPT ADD AVG_SAL NUMBER(7,2);
테이블이 변경되었습니다.
SQL> 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;
SET D_AVG_SAL = E_AVG_SAL
*
6행에 오류:
ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다
1. dept table에 평균 급여를 저장할 컬럼 추가
2. emp table에서 집계한 값을 반영 하려하자 error 발생
이유 : 옵티마이저가 불필요한 제약을 가한 것!!
해결 방안 : /*+ bypass_ujvc */ 힌트를 이용하여 옵티마이저에게 Updatable Join View Check를 생략하게한다.
(update를 위해 참조하는 집합이 Unique할때만 사용!!)
update /*+ bypass_ujvc */
(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;
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