/*+ BYPASS_UJVC */ and updatable views
High volume UPDATE statements with SET sub-queries can be one of hardest SQLs to tune. Consider the following:
UPDATE my_table a
SET col1 = (SELECT col1
FROM my_temp_table
WHERE key = a.key)
WHERE col3 = 'BLORT'
UPDATE my_table a
SET col1 = (SELECT col1
FROM my_temp_table
WHERE key = a.key)
WHERE col3 = 'BLORT'
This SQL will probably perform a full table scan on my_table (not such a problem) and then for every row returned, it will perform an indexed SELECT on my_temp_table to retrieve the new value for col1. This style of execution is synonymous with a Nested Loops join. As discussed elsewhere in this guide, Nested Loops joins are appropriate for small data volumes; large data volumes are better served with Sort-merge and Hash joins.
What is required is the ability to join the two tables with a Hash join and update at the same time. Well it is possible - with an Updateable Join View.
UPDATE (SELECT a.col3, a.col1 AS old_value, b.col1 AS new_value
FROM my_table a, my_temp_table b
WHERE a.col1 = b.col1)
SET old_value = new_value
WHERE col3 = 'BLORT'
The example above shows an update of a dynamic or nested view. It is also possible to update an explicit view that is declared on the database.
Clearly the view cannot contain DISTINCT, GROUP BY, CONNECT BY, aggregate functions, UNION, INTERSECT, or MINUS clauses: simple joins are all that is allowed. There is one other restriction: the view must be key preserved. This means that the join must meet the following restrictions:
The join criteria must be equals (=) joins.
Tables other than the table that is to be updated must be joined on a primary or unique key. If these conditions are violated, then Oracle cannot guarantee that the view will return one row only for each row in the base table. If two or more rows in the secondary table mapped to each row in the base table, then the update would be ambiguous. An attempt to update a non-key-preserved view will result in an Oracle error.
If you are performing a high volume update, but cannot use an Updateable Join View because it would not be key-preserved or would require GROUP BY etc., then try using an intermediate temporary table. Create a temporary table with the same primary key as the base table, and use a SELECT statement to insert the keys and the updated column values. Then use an Updateable Join View to update the base table. Even though this seems like more work, it is still a lot quicker that the traditional Nested Loop style update.
Alternatively, if you are using Oracle 10g or later, use the MERGE statement without a WHEN NOT MATCHED clause
For the very courageous only, there is a way to update a non-key-preserved view. Oracle uses an undocumented hint BYPASS_UJVC (Bypass Updateable Join View Constraints) internally to refresh materialized views. Note that if the join returns multiple rows for each row of the table to be updated, then you will get a non-deterministic result. Also note that since this hint is undocumented, Oracle could remove it without notice, leaving your code invalid and useless. I strongly suggest you use BYPASS_UJVC for once-only updates, and never for permanent production code.
Undocumented HINT (잘못되어도 오라클에서 보장 하지 않음)
일반적으로 특정 테이블을 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 힌트
[orapybubu] 내용 추가
다음과 같은 주의를 주는 이유를 살펴보자.
For the very courageous only, there is a way to update a non-key-preserved view
무턱대고 수정하면 다음과 같은 일이 생길 수 있기 때문에 주의를 주고 있는 것이다.
update /*+ bypass_ujvc */
(select b.dname, a.ename, b.deptno
from emp a, dept b
where a.deptno = b.deptno
and a.empno = '7369')
set deptno=50;
(select b.dname, a.ename, b.deptno
from emp a, dept b
where a.deptno = b.deptno
and a.empno = '7369')
set deptno=50;
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
50 RESEARCH DALLAS -> emp쪽의 20번은 이제 어떻게 할 것인가?
30 SALES CHICAGO
40 OPERATIONS BOSTON
---------- -------------- -------------
10 ACCOUNTING NEW YORK
50 RESEARCH DALLAS -> emp쪽의 20번은 이제 어떻게 할 것인가?
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> rollback;