엑시엄이 보는 DB 세상
BYPASS_UJVC 힌트를 MERGE INTO문으로 변환하자 3 1 11,477

by axiom BYPASS_UJVC MERGE INTO 키보존테이블 ORA-01779 [2014.08.18]


오라클이 Oracle Database 10g의 지원(Support) 중단을 공식 선언한 후 11g로의 업그레이드가 많은 사이트에서 진행되고 있다. 그런데 10g에서 11g로 업그레이드를 진행할 때는 주의해야 할 사항이 있다.

그 중 하나는 DML 시 사용되던 BYPASS_UJVC라는 힌트 기능의 사용이 불가능해졌다는 것이다. 이 글에서는 이에 대해 살펴본다.

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

  • [리스트 1] BYPASS_UJVC 힌트의 이해
  • 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문으로 변경
  • 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문으로 변경해 데이터 크기에 맞는 실행계획을 수립해 보도록 하자.

- 강좌 URL : http://www.gurubee.net/lecture/2795

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 람짱 [2016.08.23 13:32:42]

10g->11g 올릴때 이부분때문에 고생좀 했었죠.ㅎㅎ

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입