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© 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(*)

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

COUNT(*)

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

COUNT(*)

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

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