by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

1. MERGE 구문의 구성요소 알기




1. MERGE 구문의 구성요소 알기

MERGE
UPDATE,INSERT,DELETE 의 MULTIPLE OPERATION 0N 을 지원하기 위한 DML 구문으로,Source Table 에서 추출한 데이터를 Target Table 에 트랜잭션을 처리하는 형태 입니다.

1. UPSERT (UPDATE , INSERT)
UPSERT 구문이란 UPDATE 와 INSERT 를 처리하는 프로그램 로직을 처리 가능하도록 구현한 것이다.

2. Only UPDATE (UPDATABLE) JOIN VIEW 대체
UPDATE 시 SET 절에 서브쿼리가존재해 UPDATE 건수만큼 반복 수행하여 성능 문제가 발생할 경우, 성능개선 방법 JOIN VIEW을 사용

  • Oracle 10g 이전 버전에서 대량 데이터를 으로 UPDATABLE 사용하고, 10G 부터 UPDATABLE JOIN VIEW 보다 MERGE 구문을 많이 사용한다.

UPSERT 와 Only UPDATE 의 차이점

1. MERGE 구문은 병렬처리가 가능할 뿐 아니라 UPDATABLE JOIN VIEW 보다 성능적인 측면에서 효율적이다.
2. MERGE 구문 작성시 제약사항이 많지 않아 쉽게 작성할 수 있는 장점

<1.쿼리>
UPDATE emp a
SET ename = (SELECT dname FROM dept b WHERE a.deptno = b.deptno)
WHERE a.empno > 0 ;

  1. WHERE절에 매칭된 수만큼 SET절이 반복 수행되어 성능상의 문제가 발생

<2.쿼리>
UPDATE /*+ BYPASS UJVC */
(
SELECT b.dname , a.ename , a.deptn。
FROM emp a , dept b
WHERE a.deptno = b.deptno
AND a.empno > 0
)
SET ename = dname ;

  1. 업데이트 대상을 한번 찾고 난 다음 업데이트를 진행하여 위에 1번 쿼리보다 성능이 좋아짐.
  2. 힌트 없이 실행시 아래와 같은 에러가 발생되면 힌트를 추가하여 실행.
    ORA-01779: cannot modify a column which maps to a non key-preserved table
    -> bypass_ujvc hint를 사용 권장하지는 않는다.

<3.쿼리>
ALTER SESSION ENABLE PARALLEL DML;

MERGE /*+ PARALLEL(A2) USE_HASH(B) */ INTO emp a
USING (
SELECT / *+ FULL(C) PARALLEL(C2) */
c .dname , c.deptn。
FROM dept c
) b
ON (
a . deptno = b.deptno and a . empno > 0
WHEN MATCHED THEN
UPDATE SET a . ename = b . dname ;

  1. 수행할 대상 데이터를 1번 추줄하고, 트랜잭션 처리할 데이터인지 1회 체크하여, UPDATE 또는 INSERT 를 처리하여 성능이 좋아짐.

MERGE 구문을 이해하기 위해 구문의 구성요소에 대해 설명에 필요한 테이블을 생성
< MERGE T1 >

  1. 생성요건
  • 테이블 건수는 100,000 로우
  • 컬럼 C1 은 값의 종류가 100,000 가지이며, Unique함. (값의 범위 1 ~ 1,000,000)
  • 컬럼 C2 는 값의 종류가 26 가지이며 알파엣임.
  • 컬럼 C3 은 값의 종류가 100,000 가지이며 Unique함. (값의 범위 100,000 ~ 199,999)

. 테이블 생성
create table merge_t1
as
select level as c1 , chr(65+mod(level, 26)) as c2 , level+99999 as c3
from DUAL
connect by level <= 100000 ;

. 각 컬럼에 인텍스 생성 및 통계정보 수집
create index merge_t1_idx_01 on merge_t1 ( c1 ) ;

exec
dbms_stats.gather_table_stats(ownname=> 'SCOTT' ,tabname=> 'merge_t1', cascade=>TRUE ,
estimate percent=>100) ;

< MERGE T2 >

  1. 생성요건
  • 테이블 건수는 500,000 로우
  • 컬럼 C1 은 값의 종류가 500,000 가지이며, Unique함. (값의 범위 1 ~ 1,000,000)
  • 컬럼 C2 는 값의 종류가 26 가지이며 알파뱃임 .
  • 컬럼 C3 은 값의 종류가 10 가지이며 NULL 데이터 존재. (값의 범위 1~9, null)

. 테이블 생성
create table merge_t2
as
select level as c1 , chr (65+mod(level, 26)) as c2,
decode(mod(level, 10) , 0, null , mod(level , 10)) as c3
from DUAL
connect by level <= 500000 ;

!! 각 컬럼에 인텍 스 생성 및 통계정보 수집
create index merge_t2_idx_01 on merge_t2 ( cl ) ;

exec
dbms_stats.gather_table_stats(ownname => 'SCOTT' , tabname=> 'merge_t2' ,cascade=>TRUE ,
estimate_percent=>100)

MERGE 구문의 구성요소 알기

INTO절
INTO 절은 크게 두 가지 역할을 담당한다.
1. Target Table 을 정의 할 수 있다.
Target Table 이란 UPDATE , DELETE , INSERT을 수행할 대상 테이블 1개를 정의
2. 힌트 구문을 적용할 수 있다는 것이다.
힌트 구문은 조인 순서힌트, 조인 방식힌트, 테이블 엑세스 유형 힌트 등 대부분의 힌트를 적용 할 수 있다.

  1. 조인 순서 힌트
    /*+ LEADING(TABLE_A .. ) */ 파라미터로 명시된 테이블의 순서대로 조인하도록 유도
    /*+ ORDERED */ FROM절에 명시된 테이블의 순대로 조인하도록 유도
  1. 조인 방법 힌트
    /*+ USE_NL(TABLE_A .. ) */ 옵티마이저가 NESTED LOOP JOIN을 사용하도록 유도
    /*+ USE_NL_WITH_INDEX(TABLE INDEX) */ 인덱스를 사용해서 NESTED LOOP JOIN을 사용하도록 유도
    /*+ USE_MERGE(TABLE_A .. ) */ 옵티마이저가 SORT MERGE JOIN 을 사용하도록 유도
    /*+ USE_HASH(TABLE_A .. ) */ 옵티마이저가 HASH JOIN을 사용하도록 유도
  1. 테이블 엑세스 유형 힌트
    /*+ INDEX(테이블_이름, 인덱스_이름) */ SQL이 엑세스할 인데스를 설정하는 힌트 오름차순(_ASC)
    /*+ INDEX_DESC(테이블_이름, 인덱스_이름) */ SQL이 엑세스할 인데스를 설정하는 힌트 내림차순
    /*+ INDEX_FFS(테이블_이름, 인덱스_이름) */ 빠른 인덱스 전체 스캔 수행 / 다중 블록 I/O / 미정렬
    /*+ PARALLEL_INDEX(테이블_이름, 인덱스_이름,프로세스_개수) */ 여러 개의 프로세스를 통한 빠른 인덱스 전체 스캔 수행 / 다중 블록 I/O / 미정렬
    /*+ AND_EQUALS(인덱스_이름, 인덱스_이름) */ WHERE 조건에 두 개의 동일(=) 조건이 설정되고 각 동일 조건에 사용된 컬럼에 인덱스가 존재 할 경우 사용
    /*+ INDEX_JOIN(테이블_이름, 인덱스_이름) */ 2개 이상의 인덱스를 엑세스하여 테이블에 대한 랜덤 엑세스 없이 원하는 결과 데이터를 추출할 수 있는 경우
    /*+ INDEX_SS(테이블_이름, 인덱스_이름) */ 인덱스 스킵 스캔 유도 / DESC힌트 존재

HERGE /*+ LEADING(ST) USE_NL(ST TT) INDEX(TT) */ INTO MERGE_T1 tt
USING (
SELECT c1 , c2 , c3
FROM MERGE_T2
WHERE c1 >= 99990
AND c1 <= 100090
) st
ON ( tt.c1 = st.c1 )
WHEN MATCHED THEN
UPDATE SET tt.c2 = st.c2 , tt.c3 = st.c3
DELETE WHERE (tt.c2 = 'A')
WHEN NOT MATCHED THEN
INSERT ( tt.c1, tt.c2 , tt.c3) VALUES (st.c1 , st.c2, st.c3)
WHERE (st.c2 = 'A' ) ;

MERGE 구문에서는 DML 까지 병렬처리를 수행할 수 있는데, PARALLEL DML 을 적용하기 위해서는 세션에서 PARALLEL DML 이 지원되어야 병렬처리가 가능하다.

ALTER SESSION ENABLE PARALLEL DML; ---> PARALLEL DML 을 수행할 수 있도록 설정

HERGE /+ LEADING(ST) USE_NL(ST TT) INDEX(TT) PARALLEL(TT 4)/ INTO MERGE_T1 tt
USING (
SELECT c1 , c2 , c3
FROM MERGE_T2
WHERE c1 >= 99990
AND c1 <= 100090
) st
ON ( tt.cl = st.cl )
WHEN MATCHED THEN
UPDATE SET tt.c2 = st.c2 , tt.c3 = st.c3
DELETE WHERE (tt.c2 = 'A')
WHEN NOT MATCHED THEN
INSERT ( tt.c1, tt.c2 , tt.c3) VALUES (st.c1 , st.c2, st.c3)
WHERE (st.c2 = 'A' ) ;

USING절
Source Table 을 지정하고,Target Table에 UPDATE , INSERT 를 수행할 대상 데이터를 추출하며, USING 절에서도 INTO 절과 마찬가지로 힌트 구문을 적용할 수 있다.
주의할 점 : 추출 데이터 컬럼 중 ON 절에서 Target Table과 조인 할 컬럼의 값은 반드시 Unique 해야 한다.

ON절
ON 절은 Target Table의 데이터 중 Source Table에서 추줄된 데이터와 일치하는 데이터인지 체크하여 일치(WHEN MATCHED THEN) 과 불일치(WHEN NOT MATCHED THEN) 구분하여 수행한다.

1. ON (Target_Table.Column = Source_Table.Column) -> JOIN Condition
2. WHEN MATCHED THEN -> UPDATE or UPDATE & DELETE
3. WHEN NOT MATCHED THEN -> INSERT

  1. 2,3 번의 순서는 변경되어도 무방하다.

[2. MERGE 구문으로 처리되는 데이터 이해하기]

MERGE INTO MERGE_T1 tt
USING (
SELECT c1 , c2 , c3
FROM MERGE_T2
WHERE c1 >= 99990
AND c1 <= 100090
) st
ON ( tt.c1 = st.c1 )
WHEN MATCHED THEN
UPDATE SET tt.c2 = st.c2, tt.c3 = st.c3
DELETE WHERE (tt.c2 = 'A')
WHEN NOT MATCHED THEN
INSERT (tt.c1 , tt. c2, tt.c3) VALUES (st.c1 , st.c2, st.c3)
WHERE (st.c2 = 'A')

COMMIT ; – Merge 구문은 트랜잭션을 반영하기 위해서는 COMMIT 을 수행해야 한다.

  1. MERGE 구문을 통해 데이터가 어떻게 처리 되는 지에 대해서 자세히 알아 보도록 하자.

1. MERGE 수행 전
Source Table(MERGE T2)추출된 데이터 중 Target Table(MERGE_T1)에 존재하는 데이터를 비교

  • MERGE 수행 전 데이터를 비교하여 UPDATE / DELETE / INSERT 되는 데이터를 확인해 보도록 하자.
  1. (ON (tt.c1 = st.c1) Target Table에 존재하는 데이터
    SELECT COUNT
    FROM (
    SELECT c1
    ,c2
    ,c3
    FROM MERGE T2
    WHERE c1 >= 99990
    AND c1 <= 100000
    ) st ,
    WHERE T1 tt
    tt.c1 = st.c1;

COUNT
--------
11 ---> 총 11 건이 UPDATE / DELETE / INSERT 대상 데이터임.

  1. MERGE 구문의 UPDATE 시 체크 조건
    SELECT COUNT
    FROM MERGE T2
    WHERE c1 >= 99990
    AND c1 <= 100000

COUNT
--------
11

  1. MERGE 구문의 DELETE 시 체크 조건
    SELECT COUNT
    FROM MERGE T1
    WHERE c1 >= 99990
    AND c1 <= 100000
    AND c2 = 'A' ;

COUNT
--------
1

  1. merge 구문의 INSERT시 체크 조건
    SELECT COUNT(c2)
    FROM MERGE T2
    WHERE c1 > 100000
    AND c1 <= 100090
    AND c2='A' ;
    COUNT
    --------
    3

2. MERGE 수행 후

MERGE 수행전
WHEN MATCHED THEN ON절의 tt.c1=st.c1 조건으로 추출된 데이터는 총 11건이고, c1 컬럼의 값은 99990 ~ 100000
WHEN NOT MATCHED THEN ON절의 tt.c1=st.c1 조건의 대상건이 아닌 데이터가 MERGE_T2에 있을시 INSERT 3건

WHEN MATCHED THEN
페이지 127(110) 그림 넣기

  1. MERGE_T1.C1 = 99996 데이터가 존재하지 않고 나머지 데이터는 C3 컬럼의 데이터가 변경 및 삭제 되어 있음을 확인

WHEN NOT MATCHED THEN
페이지 130(113) 그림 넣기

  1. MERGE구문 수행 전 MERGE_T1 테이블은 C1값이 100,000까지의 테이만 있었으므로 데이터 건수은 3건이다.

[3. MERGE 구문 작성 시 발생할 수 있는 에러와 해결방법 알아보기]
MERGE 구문 작성할 떄 발생하는 에러는 컬럼의 성격에 의해 발생하는 경우가 많다.
MERGE_T1 과 MERGE_T2의 컬럼 정보를 살펴 보자

  1. MERGE_T1
  • 전체 건수 10만건
  • C1컬럼 과 C3 컬럼의 DISTINCT VALUE가 10만으로 UNIQUE한 값
    그림 130페이지 확인
  1. MERGE_T2
  • 전체 건수 50만건
  • C1컬럼의 DISTINCT VALUE가 50만으로 UNIQUE한 값

TARGET TABLE과 SOURCE TABLE의 조건은 1:1이어야 한다.
ON절에 조인 조건으로 사용할 컬럼은 UNIQUE한 값이어야 피할 수 있다.
아래의 에러 발생 예를 통하여 내용을 확인하고 해결방법을 찾아보자

에러 발생 예[1].중복 테이터 생성으로 ON절의 MERGE_T1, MERGE_T2 조인 처리가 1:N인 경우

MERGE INTO MERGE_T1 tt
USING (
SELECT c1, c2, c3
FROM MERGE_T2 , (SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 2)
WHERE c1 >= 99990
AND c1 <= 100090
) st
ON ( tt.c1 = st.c1 )
WHEN MATCHED THEN
UPDATE SET tt.c2 = st.c2, tt.c3 = st.c3
DELETE WHERE (tt.c2 = 'A' )
WHEN NOT MATCHED THEN
INSERT (tt.c1 , tt.c2 , tt.c3) VALUES (st.c1, st.c2 , st.c3)
WHERE (st.c2 = 'A' );

ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables

  1. 원본 테이블의 고정 행 집합을 가져올 수 없습니다.

처리 : Source Table에서 데이터 추출시 DISTINCT 나 GROUP BY처리 필요

!! DISTINCT를 추가하여 해결
MERGE INTO MERGE_T1 tt
USING (
SELECT DISTINCT c1, c2, c3
FROM MERGE_T2 , (SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 2)
WHERE c1 >= 99990
AND c1 <= 100090
) st
ON ( tt.c1 = st.c1 )
WHEN MATCHED THEN
UPDATE SET tt.c2 = st.c2, tt.c3 = st.c3
DELETE WHERE (tt.c2 = 'A' )
WHEN NOT MATCHED THEN
INSERT (tt.c1 , tt.c2 , tt.c3) VALUES (st.c1, st.c2 , st.c3)
WHERE (st.c2 = 'A' );

에러 발생 예[2].ON 절의 MERGE_Tl, MERGE_T2 조인 처리가 N:N 인 경우
MERGE INTO MERGE_T1 tt
USING (
SELECT c1, c2, c3
FROM MERGE_T2
WHERE c1 >= 99990
AND c1 <= 100090
) st
ON ( tt.c2 = st.c2 )
WHEN MATCHED THEN
UPDATE SET tt.c3 = st.c3
DELETE WHERE (tt.c2 = 'A' )
WHEN NOT MATCHED THEN
INSERT (tt.c1 , tt.c2 , tt.c3) VALUES (st.c1, st.c2 , st.c3)
WHERE (st.c2 = 'A' );

ERROR at line 1:
ORA- 30926: unable to get a stab1e set of rows in the source tables

  1. 원본 테이블의 고정 행 집합을 가져올 수 없습니다.

처리 : ON절의 조인연결 컬럼 확인 후 변경

MERGE INTO MERGE_Tl tt
USING (
SELECT c1, c2, c3
FROM MERGE_T2
WHERE c1 >= 99990
AND c1 <= 100090
) st
ON ( tt.c1 = st.c1 )
WHEN MATCHED THEN
UPDATE SET tt.c3 = st.c3
DELETE WHERE (tt.c2 = 'A' )
WHEN NOT MATCHED THEN
INSERT (tt.c1 , tt.c2 , tt.c3) VALUES (st.c1, st.c2 , st.c3)
WHERE (st.c2 = 'A' );

UPDATE 컬럼은 ON절에 사용할 수 없다.
MERGE 구문을 작성하다 보면,ON절 (조인 연결)에서 사용한 컬럼을 UPDATE 까지 수행해야 할 경우가 있다.
ON절에 사용한 컬럼이 Primary Key컬럼 (또는, Unique 데이터를 가진 컬럼)이라면, 에러 없이 수행하기 위해서 Oracle 이 제공하는 ROWID를 이용한다.

ON절 사용한 컬럼을 UPDATE시 에러 발생
MERGE INTO MERGE_T1 tt
USING (
SELECT c1, c2 , c3
FROM MERGE_T2
WHERE c1 >= 99990
AND c1 <= 100090
) st
ON ( tt.c1 = st.c1 )
WHEN MATCHED THEN
UPDATE SET tt.c1 = st.c1, tt.c2 = st.c2, tt.c3 = st.c3
DELETE WHERE (tt.c2 = 'A' )
WHEN NOT MATCHED THEN
INSERT (tt.c1 , tt.c2 , tt.c3) VALUES (st.c1 , st.c2 , st.c3)
WHERE (st.c2 = 'A' );

ERROR at line 9:
ORA-38104 : Columns referenced in the ON 절 cannot be updated : "TT"."C1"

  1. ON절에 사용되는 컬럼을 UPDATE 할 수 없다.

처리 : ON절에 C1컬럼 대신 ROWID를 추출하여 C1컬럼을 대체하여 수행

MERGE INTO MERGE_T1 tt
USING (
SELECT st.c1 , st.c2 , st.c3 , tt.ROWID as rid
FROM MERGE_T2 st, MERGE_Tl tt
WHERE st.c1 >= 99990
AND st.c1 <= 100090
AND st.c1 = tt . c1
) st
ON ( tt.ROWID = st.rid )
WHEN MATCHED THEN
UPDATE SET tt.c1 = st.c1 , tt.c2 = st.c2, tt.c3 = st.c3
DELETE WHERE (tt.c2 = 'A')
WHEN NOT MATCHED THEN
INSERT (tt.c1 , tt.c2 , tt.c3 ) VALUES (st.c1 , st.c2 , st.c3 )
WHERE (st.c2 = 'A');

[4. MERGE 구문은 다양한 형태의 DML을 지원한다]
MERGE 구문은 10g 이후부터 제약이 많이 사라져 거의 모든 형태의 DML 을 지원한다.
ON절에 WHEN MATCHED THEN , WHEN NOT MATCHED THEN절을 이용하여 총 3가지 유형의 트랜잭션 처리
. WHEN MATCHED THEN -> UPDATE & DELETE
. WHEN NOT MATCHED THEN -> INSERT

MERGE 구문을 이용한 트랜잭션 유형별 수행 결과
135(118) 그림 넣기

[5. MERGE 구문을 성능 문제에 활용하자]

136(119) 그림 넣기

CURSOR(Source Table)에서 데이터를 추줄하고, 추줄된 데이터 건수 만큼 FOR 문을 반복 수행하여, UPDATE & INSERT 구문을 수행하는데,
이러한 패턴의 프로그램은 보통 많은 데이터를 처리하는 배치 프로그램에서 사용한다.
추출되는 데이터 건수에 의해 결정되는 경우가 대부분인데 많은 데이터를 처리시 MERGE 구문을 활용하면, 수행시간을 크게 단축 시킬 수 있다.

MERGE 구문은 Oracle9i까지 UPSERT(UPDATE+INSERT)구문으로만 사용되었으나, lOg 부터는 Only UPDATE, Only INSER, UPSERT 등 다양한 패턴의, DML을 처리할 수 있도록 MERGE구문의 활용폭이 넓어져,
위와 같은 패터의 프로그램 성능을 개선하기 위해 사용되고 있다.
단,CASE(3)의 경우는 물리 파일을 읽어서 UPDATE 이나 INSERT 를 반복 수행하는 프로그램으로,Oracle DW 튜닝을 위해서 9i에 새로 나온 기능인 EXTERNAL TABLE 과 MERGE 구문을 함께 이용하면 성능을개선할수있다.

  1. EXTERNAL TABLE
  • 데이터베이스 내의 일반 테이블처럼 사용이 가능한 가상의 테이블로써 실제 위치와 저장공간은 데이터베이스 외부에 존재
  • 외부데이터를 처리 작업을 위해 데이터베이스 내부에 로딩할 필요 없이 데이터베이스 내부에서 직접 연결하여 쿼리 과정을 가능
  • 읽기만 가능하며, 데이터베이스의 뷰와 같이 작동하기 때문에 인덱스의 생성은 불가능

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.