/*+ BYPASS_UJVC */ : JOIN 조건하에 여러 FIELD UPDATE 처리
변경전 : select 가 여러번 들어 감
UPDATE ZPO1020 A SET
ZZCOST = ( SELECT C.COST1_S FROM ZPOTEMP01 C
WHERE C.MANDT = A.MANDT
AND C.SUPPLYCHAINID = A.SUPPLYCHAINID
AND C.MATNR = SUBSTR(A.MATNR,3,8) ),
ZZMAT = ( SELECT C.COST1_1 FROM ZPOTEMP01 C
WHERE C.MANDT = A.MANDT
AND C.SUPPLYCHAINID = A.SUPPLYCHAINID
AND C.MATNR = SUBSTR(A.MATNR,3,8) ),
ZZHUM = ( SELECT C.COST1_2 FROM ZPOTEMP01 C
WHERE C.MANDT = A.MANDT
AND C.SUPPLYCHAINID = A.SUPPLYCHAINID
AND C.MATNR = SUBSTR(A.MATNR,3,8) ),
ZZEXP = ( SELECT C.COST1_3 FROM ZPOTEMP01 C
WHERE C.MANDT = A.MANDT
AND C.SUPPLYCHAINID = A.SUPPLYCHAINID
AND C.MATNR = SUBSTR(A.MATNR,3,8) )
WHERE A.MANDT = '100'
AND A.SUPPLYCHAINID = 'MLCC'
AND A.PLANWEEK IN ( '200633', '200634' )
AND EXISTS ( SELECT 1 FROM ZPOTEMP01 B
WHERE B.MANDT = A.MANDT
AND B.SUPPLYCHAINID = A.SUPPLYCHAINID
AND B.MATNR = SUBSTR(A.MATNR,3,8))
오라클 권장사항은 아니라고 합니다만 아래처럼 변경 가능 합니다.
(공식적으로 /*+ BYPASS_UJVC */ 안나와 있다고 합니다.)
-> Bypass Updateable Join view Constraints
변경후 : 한 번의 JOIN으로 해결
UPDATE /*+ BYPASS_UJVC */
( SELECT A.ZZCOST A_ZZCOST,
A.ZZMAT A_ZZMAT,
A.ZZHUM A_ZZHUM,
A.ZZEXP A_ZZEXP,
C.COST1_S C_COST1_S,
C.COST1_1 C_COST1_1,
C.COST1_2 C_COST1_2,
C.COST1_3 C_COST1_3
FROM ZPOTEMP01 C, ZPO1020 A
WHERE A.MANDT = '100'
AND A.SUPPLYCHAINID = 'MLCC'
AND A.PLANWEEK IN ('200633', '200634')
AND C.MANDT = A.MANDT
AND C.SUPPLYCHAINID = A.SUPPLYCHAINID
AND C.MATNR = SUBSTR(A.MATNR,3,8) )
SET A_ZZCOST = C_COST1_S,
A_ZZMAT = C_COST1_1,
A_ZZHUM = C_COST1_2,
A_ZZEXP = C_COST1_3
다른샘플...
update /*+ bypass_ujvc */
(
select a.mem_id a_mem_id
, a.email a_email
, a.cphone1 a_cphone1
, a.cphone2 a_cphone2
, a.cphone3 a_cphone3
, b.mem_id b_mem_id
, b.email b_email
, b.cphone1 b_cphone1
, b.cphone2 b_cphone2
, b.cphone3 b_cphone3
from xxxxy a,
member b
where a.mem_id = b.mem_id
)
set b_mem_id = a_mem_id
, b_email = a_email
, b_cphone1 = a_cphone1
, b_cphone2 = a_cphone2
, b_cphone3 = a_cphone3
;
조인 업데이트 쿼리입니다...
일일이 하나씩 업데이트 하기 힘들고 쿼리 라인이 길어질 때
이 쿼리를 사용하면 한번에 업데이트가 됩니다.
우선 파란색의 select 문에서
table들을 제약조건에 맞게 조인한 후에
필요한 컬럼들을(업데이트 하고자 하는 컬럼과 업데이트 할 값을 가지고 있는 컬럼)
다 호출해서... 고유한 alias를 줍니다.
그런 다음에 set 절 다음에
업데이트 하고자하는 컬럼에
조인을 걸어 가져온 고유alias들을 대응시키면
조건에 맞는 컬럼들은 전부 업데이트가 됩니다.
주의해야 할 것은
select 절에서 where 절 다음에 조건을 걸어서
결과를 테스트 해보시고
데이터가 정확하게 나오는지 확인 후에
업데이트를 하시기 바랍니다...
꼭이요... ^^
**** 참조***
Updateable Join 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'
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.
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.