[Oracle] Merge 구문 1

DB에 접속이 안되는 현상이 있다고 연락을 받아 확인하다가,

Merge 구문이 inactive 상태에서 다른 Session 을 block 하고 있는 상태를 확인하게 되었다.

Merge 구문이 실행 된 후 완료 되고 Inactive 상태로 빠지는데,

이 때 다른 Merge 구문을 block을 함으로써 lock 상태가 되었다. 그러면서 자원을 획득하지 못한

Blocked 는 inactive 상태가 되고..이렇게 트리 형태로 증가되는 부분을 확인 하였다.

이것이 왜 그런지는 조금 더 찾아 봐야겠지만 내가 본 현상과 예측은 이와 같다.

그러면 찾아보기 전에 Merge 구문에 대해서 정리부터 해본다.

너무나도 쉽다고 생각할 수 있지만 어차피 언젠가는 merge 구문 사용이 필요할 때를 대비해서 정리해 본다.

쉽게 말해서 있으면 update / 없으면 insert 를 하는 구문이다.

간단한 테스트로 진행 (타 블로그 참조 하였음)

2개의 테이블 생성

create table emp_t (no number(3), name varchar2(10), deptno number(3));

create table emps_s ( no number(3), name varchar2(10), deptno number(3));

테이터 생성

SQL> insert into emps_s values(100, 'AA', 600);

SQL> insert into emps_s values(200, 'B', 20);

SQL> insert into emps_s values(300, 'C', 30);

SQL> insert into emps_s values(210, 'D', 40);

SQL> insert into emps_s values(400,'F',50);

SQL> insert into emp_t values(100,'A',10);

SQL> insert into emp_t values(200,'B',20);

SQL> insert into emp_t values(300,'C',30);

확인

merge 구문실행

SQL> merge into emp_t t using emps_s s on (t.no=s.no)

when matched then 

update set t.name = s.name, t.deptno=s.deptno

when not matched then

insert values(s.no, s.name, s.deptno);

5 rows merged.

두 테이블의 데이터가 동일한 것을 확인 가능

commit 은 필수!!!

단, 조건절 내의 동일한 값이 있을 경우 ORA-30926 가 발생

SQL> merge into emp_t t using emps_s s on (t.no=s.no)

when matched then

update set t.name = s.name, t.deptno=s.deptno

where deptno=777   <-원하는 조건을 추가하여 가능

when not matched then

insert values(s.no, s.name, s.deptno);

플랜을 한번 확인해 보니 아래와 같이 확인이 가능하다

Hash Join 으로 풀리고, 테이블에 대해서 index 가 없으니 당연히 full scan ..

이 부분도 고려하여 index 설계를 잘 하면, merge 구문도 성능 향상을 볼 수 있을 듯 싶다.

아래는 테스트 하다가, index 타는 merge 구문을 캡쳐하여 추가한 것이다.

Index 를 이용하여 merge 구문이 실행 된 것을 확인할 수 있다.

참조 블로그

http://majesty76.tistory.com/37