/*+ 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

 

**** 참조***

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.