[펌]update join view

기본적인 update의 경우 update의 where절에 매칭된 수만큼 set 절이 반복 수행됩니다.

set 절에 update하는 데이터를 상수로 주는 경우는 크게 문제없으나

다른 테이블에 있는 데이터를 가져와 업데이트 할 때는 성능상의 문제를 피할 수 없습니다.

 

일반적인 방법으로

 

UPDATE A SET a1=
(SELECT b.b1 FROM b WHERE a.a2=b.a2)
where exists (select 'x' from b where b2 = a.a2)

 

와 같은 방법을 많이 씁니다만 성능상 엄청난 유익이 있는 다른 방법이 있습니다.. (오라클에서만 가능하죠ㅡㅡ;)

 

update /*+ bypass_ujvc */
(select a.a1 a_a1, b.b1 b_b1 from a, b where a.a2 = b.a2)
set a_a1 = b_b1

 

updatable join vilew 를 사용하시면 성능상 유익이 넘넘 큽니다 ^^

 

참고로 위의 힌트 없이 사용하면 키값의 변경이나 unique 속성의 변화가 실제 없는데도 불구하고

ORA-01779: cannot modify a column which maps to a non key-preserved table

의 에러를 종종 만날 수 있습니다.

 

그러면 무조건 hint를 써야하나? asktom에 답글 중 bypass_ujvc hint 사용에 대한 경고문을 함께 제시합니다.

it is undocumented, it is unsafe, its behaviour could change in
unpredicable ways in the future, it is "magic"

 

업무단에서 key preserved 임을 검증하면서 사용하면 큰 무리는 없을듯..

저도 지난번 site에서 기존 메인프레임에 들어있던 서로 맞지 않는 데이터들을 동기화 하는데 많이 사용하였습니다만 큰 문제는 없었습니다.

성능개선은 끝장납니다. (단적인 예를 들면 7시간 -> 2분 ㅡㅡ;)

 

oracle 7.3의 new feature로 Updatable Join Views (with restrictions) 가 소개되고 있고 8 점대 버전까지도 inline view 내에서 group 함수를 못쓴다던지 제약이 좀 많은걸로 알고 있습니다.

제가 써본 결과 9i에서는 view내에서 오만짓을 하더라도 거의 제약이 없었습니다.

 

대량 update 시에는 maxextents 에 도달해서 에러가 난 경우도 있었던것 같습니다. temporary tablespace나 rollback segment 쪽 관련 파라메터를 미리 충분히 크게 주심이 좋을것 같습니다.. 저같은 경우는 몇시간 넘게 update 하던게 위의 에러나서 다시 몇시간 동안 rollback 했던 기억이 있네요.

참고 : Update statement and Null-values

***********************************************************************
update /*+ bypass_ujvc */
(select a.a1 a_a1,
           b.b1 b_b1
 from a, b where a.a2 = b.a2)
set a_a1 = b_b1
위의 문장과 같이 set절에서 column을 참조할때 테이블alias가 안먹히므로 select절에서 column마다 alias를 지정해야
set절에서 사용가능 (주의)