[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 힌트|작성자 리원아빠