두개의 Table 을 Join 하여 Update 하기

보통 2개 의 테이블에서 조인을 하여 특정 테이블의 컬럼을 Update 하고 싶을 때, 대부분 SET 절에 SubQuery 형태로 문장을 작성하여, 특정값을 가져오게 하여 Update 하도록 많이 작성을 할 것입니다. 예를 들면 아래와 같은 문장이 되겠죠?

UPDATE table_a a
   SET column_1 = (SELECT column_1
                     FROM table_b b
                    WHERE b.KEY = a.KEY)

 

그럼 위의 문장과 같은 형식으로 table_a 에 두개 이상의 컬럼을 Update 한다고 생각하면,

UPDATE table_a a
   SET column_1 = (SELECT column_1
                     FROM table_b b
                    WHERE b.key = a.key),
       column_2 = (SELECT column_2
                     FROM table_b b
                    WHERE b.key = a.key),
       column_3 = (SELECT column_3
                     FROM table_b b
                    WHERE b.key = a.key)

  와 같은 식의 문장을 생각할 수 있습니다. 하지만, 아래와 같은 문장은 어떻게 될까요?

UPDATE
       (
        SELECT a.column_1 AS a_column_1,
               b.column_1 AS b_column_1
          FROM table_a a,
               table_b b
         WHERE a.key= b.key
       )
   SET a_column_1 = b_column_1

  SQL문을 많이 접해보신 분이라면, 위 문장이 대략 어떤 의미인지 아실 수 있을 것 입니다. 즉, 위 문장과 같이 Updatable Join View 를 생각할 수 있습니다. 물론 이경우 조인되는 2개의 테이블은 반드시 1:1 또는 1:N의 관계여야 하며, Update되는 컬럼의 테이블은 N쪽 집합이어야 합니다. 이것은 1에 해당하는 집합인 부모 table의 조인 컬럼이 UK 또는 PK로 설정되어 있어야 된다는 것입니다. 위의 문장에서 보이는 table_a 와 table_b가 UK 또는 PK 로 설정이 되어 있지 않으면 ORA-01779 cannot modify a column which maps to a non key-preserved table 에러를 방생하며 실행되지 않습니다. 이쯤에서 한번 고민해봐야 할 문제가, 처음에 문제로 던졌던 두개의 Table을 조인하여, 특정 table의 컬럼 하나만 업데이트 할경우엔 Set 절에 Sub-Query 형태로 문장을 작성해도 큰 문제가 되지 않을 듯 합니다. 하지만, 컬럼이 여러개일 경우와 행이 무수히 많은 Table을 Update 할 경우 SET절에서 동일 문장으로 특정 테이블을 반복적으로 실행해야하는 로드가 발생하므로 Update 해야 하는 행이 많을 경우엔 Updatable Join View를 활용 해야만 하는 필요가 있다. 그러나, Updateable Join View 를 활용하기 위해 매번 테이블의 UK 혹은 PK를 생성해야만 해야 하나 라는 필요성에 대해 생각해 보게 되는데, 일반적으로는 UK나 PK Constraint를 설정하기 어려운 것이 현실입니다. 따라서, 이러한 Constraint를 피해서 Updatable Join View를 사용할 수 있도록 하기 위해 준비된 오라클 힌트가 있는데 그것이 바로, BYPASS_UJVC 입니다. 즉, 위에서 소개한 문장에서 UK 혹은 PK가 설정되지 않아 에러가 리턴 될경우 아래와 같이 힌트를 준다면 정상적으로 실행이 될 것입니다.

UPDATE 
       (
        SELECT a.column_1 AS a_column_1,
               b.column_1 AS b_column_1
          FROM table_a a,
               table_b b
         WHERE a.key= b.key
       )
   SET a_column_1 = b_column_1

  컬럼이 여러개 일 경우에도,

UPDATE 
       (
        SELECT a.column_1 AS a_column_1,
               b.column_1 AS b_column_1,
               a.column_2 AS a_column_2,
               b.column_2 AS b_column_2,
               a.column_3 AS a_column_3,
               b.column_3 AS b_column_3
          FROM table_a a,
               table_b b
         WHERE a.key= b.key
       )
   SET a_column_1 = b_column_1,
       a_column_2 = b_column_2,
       a_column_3 = b_column_3

  위와 같이 표현될 수 있을 것 입니다. 이렇게 두개이상의 테이블을 조인하여 Update 할 경우 Updatable Join View 를 활용하여 Update 문을 실행하면, 실행시간도 단축(튜닝)되는 효과를 보실 수 있을 듯 합니다. 마지막으로 위에서 소개한 힌트의 의미를 풀어보자면 bypass_ujvc : Bypass Updateable Join view Constraints 이라는 의미 입니다. 풀어서 적어보니, UJVC 라는 의미가 쏙 들어오지 않나요?