UPDATE 여러테이블 조인

MERGE /*+ USE_HASH(A B) */ 

                      INTO TN_NPQ_LAB_QRANT_ITM A

                     USING (

                            SELECT DISTINCT A.LAB_RQST_NO,

                                   A.REC_SEQ,

                                   C.PRDLST_NM,

                                   C.BOT_NM

                             FROM TN_NPQ_LAB_QRANT_ITM A,

                                  TN_NPQ_LAB_QRANT_REQST B,

                                  TN_NPQ_REQST_DETAIL C

                            WHERE A.LAB_RQST_NO = B.LAB_RQST_NO

                              AND B.OFF_CD = C.OFF_CD

                              AND B.REC_NO = C.REC_NO

                              AND A.REC_SEQ = C.REC_SEQ

                              AND C.OFF_CD = '10'

          AND C.REC_NO = '17292334'

                           ) B

                       ON (A.LAB_RQST_NO = B.LAB_RQST_NO AND A.REC_SEQ = B.REC_SEQ)

                       WHEN MATCHED THEN

                        UPDATE

                           SET A.PRDLST_NM = B.PRDLST_NM,

                               A.BOT_NM = B.BOT_NM;

 

/* 다른방법 */

update TB_FAMHS_FCLTY_INFO a

   set a.PARTICIPATION_YEAR =  ( select x.PARTCPTN_YEAR from  TB_ANALYSIS_USER_INFO x where x.USER_ID = a.USER_ID )

 where exists (

        select 'x' from  TB_ANALYSIS_USER_INFO b where b.USER_ID = a.USER_ID

       );

 

 

 UPDATE TB_FAMHS_FCLTY_INFO a

         SET ( FCLTY_SIDO_CODE, FCLTY_SIGUNGU_CODE)      

             = (SELECT FCLTY_SIDO_CODE, FCLTY_SIGUNGU_CODE

                  FROM CBW_SIDO b

                 WHERE a.FCLTY_ID = b.FCLTY_ID || '_01'

                )

     WHERE EXISTS

           (SELECT 1

              FROM CBW_SIDO b

             WHERE a.FCLTY_ID = b.FCLTY_ID || '_01'

            )

       AND USE_AT = 'Y'

       AND FCLTY_SIGUNGU_CODE IS NULL

 

 

BYPASS_UJVC 힌트란 무엇일까? 이에 대해 간단히 설명하면 UPDATE 시에 키보존 테이블에 대해 DML이 불가능한 것을 가능하게 해주는 힌트라고 이해할 수 있다.

• [리스트 1] BYPASS_UJVC 힌트의 이해

• ?

1 2 3 4 5 6 7

UPDATE /*+ BYPASS_UJVC */ ( SELECT TAB1. COL1, TAB1. COL2, TAB1. COL3 , TAB2.COL5, TAB6.COL6   FROM TAB1, TAB2  WHERE TAB1.COL1 = TAB2.COL5 ) SET COL1 2 = COL6

• - 키보존 테이블이란 키 값이 변경되지 않는 테이블을 말한다. 즉, 2개 테이블 조인이 1:M 테이블일 경우 M 테이블은 키보존 테이블이다.
• - UJVC는 Updatable Join View Check의 약자다.

원래 키보존 테이블 이외의 테이블은 DML이 불가능하나 10g까지는 BYPASS_UJVC 힌트 사용 시 DML이 가능해져, 마치 마법과도 같은 힌트였다.

그러나 이 힌트는 결코 마법이 아니며, 사용자가 의도하지 않는 값으로 DML이 될 수 있는 위험한 힌트이기도 하다. 이 힌트의 위험성은 뒤에서 다시 언급될 것이므로 일단 여기까지만 설명하도록 하겠다.

이러한 이유로 11g에서는 사용을 금지하기 위해 아예 지원을 중단했을 것으로 짐작된다.

11g에서는 해당 힌트를 사용하더라도 키보존 테이블 변경 시의 오류코드와 동일한 ORA-01779 오류를 내며 종료한다.

11g에서 BYPASS_UJVC 힌트를 사용한 DML문에 대해서는 두 가지로 변경할 수 있다.

• 1. 서브쿼리 사용으로 변경
• 2. MERGE INTO문 사용으로 변경

버전 업그레이드 시 의외로 많은 개발자들이 MERGE INTO문으로의 변환에 대해 문의하곤 한다. 1번 서브쿼리로의 변경은 예전부터 사용돼 왔던 문법이므로 생략하기로 하고, 이 글에선 2번에 대해서만 설명하도록 하겠다.

MERGE INTO문은 BYPASS_UJVC의 대안이기 이전에 두 가지 DML을 가능하도록 하는 기능이다. 그럼 [그림 1]의 UPDATE문을 MERGE INTO문으로 변경해보자.

MERGE INTO 스크립트에서 MATCHED THEN / WHEN NOT MATCHED THEN 문은 9i에서는 필수 사용이며, 10g부터는 WHEN MATCHED THEN/ WHEN NOT MATCHED THEN 둘 중 하나만의 사용이 가능하다.

USING문에 SELECT 시 DISTINCT를 넣어준 이유는 TAB1 : TAB2 = 1 : M이라는 가정으로 M 테이블을 1 테이블로 변경하여 UPDATE 값으로 만든 데 있다.

만약 UPDATE문에 BYPASS_UJVC 힌트가 들어가 있다면 1 : M일 가능성이 크므로 MERGE INTO문으로 변환해 UPDATE에 적용할 값을 담당자와 확인한 후에 대상 값에 맞게 변환(MAX, MAIN 등)해야 할 것이다.

BYPASS_UJVC로 처리됐었다면 M의 값이 1의 값에 적용될 때 임의의 한 값이 적용돼 UPDATE가 잘못됐을 가능성도 확인해봐야 할 것이다.

• [그림 1] UPDATE문을 MERGE INTO문으로 변경

DML의 MERGE INTO 적용 시 또 하나의 큰 장점은 해시조인이 가능하다는 것이다. 대량 데이터 UPDATE 시 단순 UPDATE문에서 BYPASS_UJVC 힌트 사용 방식으로 변경하고 UPDATE가 굉장히 빨라진 경험이 있다.

이는 옵티마이저가 UPDATE 구문의 실행계획을 해시조인 방식으로 변경했기 때문이다. 이를 잘못 이해하고 BYPASS_UJVC 힌트를 사용하면 DML이 굉장히 빨라지는 힌트로 오해할 수 있으나, BYPASS _UJVC 힌트와 해시조인은 전혀 연관성이 없다.

대용량 데이터를 처리하기 위해 사용하는 해시조인은 빠른 처리 성능을 보장하는 훌륭한 도구이며, 해시조인은 MERGE INTO 구문에서도 수행이 가능하다.

[그림 1]의 스크립트에서 해시조인 힌트로도 유도가 가능하기 때문에 실행계획을 살펴보고 대용량 데이터 적용 시 확인하고 적용하면 보다 개선된 성능을 내게 될 것이다.

BYPASS_UJVC 힌트는 키값의 보존이 되지 않는 치명적 약점을 가지고 있다. 이에 따라 애써 수립한 데이터 아키텍처를 무용지물로 만들 수 있는 위험이 따른다.

현재 운영 중인 오라클 데이터베이스가 11g가 아닐지라도 BYPASS_UJVC 힌트를 사용하는 SQL이 존재한다면 이는 반드시 개선해야 할 사항이다.

또한 DML 구문 처리 시 만족할 만한 성능이 나오지 않아 고민 중이라면 위와 같이 MERGE INTO문으로 변경해 데이터 크기에 맞는 실행계획을 수립해 보도록 하자.