'bypass_ujvc'에 해당되는 글 1건
- 2011.05.25 [Oracle] JOIN UPDATE (bypass_ujvc 힌트)
Undocumented HIT (잘못되어도 오라클에서 보장 하지 않음)
일반적으로 특정 테이블을 Update하기 위해서는 WHERE절에 EXISTS 또는 IN 등의 Sub-Query로 조인 조건을 만족하는 Row를 먼저 Check하고, 조건을 만족하는 Row에 대하여 SET 절에서 필요한 데이터를 검색하여 Update하는 것이 보통이다.
이 때, Update 해야 하는 Row가 많을 경우 WHERE절이나 SET절에서 테이블을 반복적으로 Random 액세스해야 하는 부담이 발생하므로 처리 범위가 넓은 Update문의 경우에는 'Updatable Join View'를 활용할 필요가 있다.
이 때, 조인되는 2개의 테이블은 반드시 1:1 또는 1:M의 관계여야 하며, Update되는 컬럼의 테이블은 M쪽 집합이어야 한다.
이것은 1쪽 집합인 Parent table의 조인 컬럼이 UK 또는 PK로 설정되어 있어야 함을 의미한다.
이 조건을 만족하지 못하는 Updatable Join View는 에러를 Return하며 실행되지 않는다.
(ORA-01779 cannot modify a column which maps to a non key-preserved table)
그러나, 일반적으로 View 또는 2개 이상의 테이블을 조인한 집합을 엑세스하는 경우가 많으므로 위의 UK나 PK Constraint를 설정하기 어려운 것이 현실이다.
따라서, 이러한 Constraint를 피해서 Updatable Join View를 사용할 수 있도록 BYPASS_UJVC 라는 힌트를 사용하여 튜닝할 수 있다.
-----------------------------
▣ TEST 1 환경
Constraint :
DETP.DEPTNO CONSTRAINT PK_DEPT PRIMARY KEY
EMP.EMPNO CONSTRAINT PK_EMP PRIMARY KEY
EMP.DEPTNO CONSTRAINT FK_DEPTNO REFERENCES DEPT
-----------------------------
▣ VIEW 생성
create or replace view empdept_v
as
select x.empno, x.ename, x.job, y.dname, y.deptno
from emp x, dept y
where x.deptno = y.deptno;
View created.
▣ UPDATABLE JOIN VIEW 내용
SQL> select * from empdept_v;
EMPNO ENAME JOB DNAME DEPTNO
---------- ---------- --------- -------------- ----------
7369 SMITH CLERK RESEARCH 20
7499 ALLEN SALESMAN SALES 30
7521 WARD SALESMAN SALES 30
7566 JONES MANAGER RESEARCH 20
7654 MARTIN SALESMAN SALES 30
7698 BLAKE MANAGER SALES 30
7782 CLARK MANAGER ACCOUNTING 10
7788 SCOTT ANALYST RESEARCH 20
7839 KING PRESIDENT ACCOUNTING 10
7844 TURNER SALESMAN SALES 30
7876 ADAMS CLERK RESEARCH 20
7900 JAMES CLERK SALES 30
7902 FORD ANALYST RESEARCH 20
7934 MILLER CLERK ACCOUNTING 10
14 rows selected.
▣ 1쪽 컬럼 갱신
update empdept_v
set dname = 'AP_TUNNING'
where empno = '7369';
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table
▣ 1쪽 컬럼 갱신 WITH bypass_ujvc HIT
update /*+ bypass_ujvc */
empdept_v
set dname = 'AP_TUNNING'
where empno = '7369';
1 row updated.
▣ 변경 내역 확인
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 AP_TUNNING DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from empdept_v;
EMPNO ENAME JOB DNAME DEPTNO
---------- ---------- --------- -------------- ----------
7369 SMITH CLERK AP_TUNNING 20
7499 ALLEN SALESMAN SALES 30
7521 WARD SALESMAN SALES 30
7566 JONES MANAGER AP_TUNNING 20
7654 MARTIN SALESMAN SALES 30
7698 BLAKE MANAGER SALES 30
7782 CLARK MANAGER ACCOUNTING 10
7788 SCOTT ANALYST AP_TUNNING 20
7839 KING PRESIDENT ACCOUNTING 10
7844 TURNER SALESMAN SALES 30
7876 ADAMS CLERK AP_TUNNING 20
7900 JAMES CLERK SALES 30
7902 FORD ANALYST AP_TUNNING 20
7934 MILLER CLERK ACCOUNTING 10
14 rows selected.
-----------------------------
▣ TEST 2 환경
제약조건 : Constraint 없음.
-----------------------------
▣ 의미상 M쪽 집합을 갱신함.
update
(select b.dname, a.ename
from emp a,
dept b
where a.deptno = b.deptno
and a.empno = '7369')
set ename = 'KIMDOL';
ERROR at line 5:
ORA-01779: cannot modify a column which maps to a non key-preserved table
--> Constraint가 없으므로 oracle은 M쪽 집합인지 알지 못한다.
▣ 의미상 M쪽 집합을 갱신, With bypass_ujvc HIT
update /*+ bypass_ujvc */
(select b.dname, a.ename
from emp a,
dept b
where a.deptno = b.deptno
and a.empno = '7369')
set dname = 'KIMDOL';
1 row updated.
[출처] bypass_ujvc 힌트|작성자 리원아빠
[출처] bypass_ujvc 힌트|작성자 리원아빠
'Database / Sql' 카테고리의 다른 글
| 문자열 결합 그룹핑 (0) | 2011.08.30 |
|---|---|
| [Oracle] JOIN UPDATE (0) | 2011.05.25 |
| [MSSQL] WHERE 절에 CASE문 추가 (0) | 2011.03.21 |
| [MSSQL] 날짜 함수 (0) | 2010.11.30 |
| [Oracle] 9i 이상에서 컬럼명 변경 (0) | 2010.11.11 |
Prev
Rss Feed