행위

SQL MERGE

DB CAFE

thumb_up 추천메뉴 바로가기


1 MERGE INTO 절[편집]

 attach_file - 주의사항

  1. ON(조인) 조건절에 사용한 컬럼은 UPDATE 불가
  2. 트리거 발생되지 않음

2 MERGE 문법[편집]

merge.gif

MERGE [ hint ]
-- 1.MERGE 대상 테이블 
 INTO [ schema. ] { table | view } [ t_alias ]

-- 2.비교 대상 조회
USING { [ schema. ] { table | view }
        | subquery 
      } [ t_alias ]

-- 3.조인 조건 
   ON ( condition )

-- 4.데이터 존재시 --
WHEN MATCHED THEN
     UPDATE SET column = { expr | DEFAULT }
           [, column = { expr | DEFAULT } ]...
     [ DELETE where_clause ]

-- 4.데이터 미존재시 --
WHEN NOT MATCHED THEN
     INSERT [ (column [, column ]...) ]
     VALUES ({ expr [, expr ]... | DEFAULT })
;
- INTO : DATA가 UPDATE되거나 INSERT 될 테이블 또는 뷰를 지정.
- USING : 비교할 SOURCE 테이블 또는 뷰나 서브쿼리를 지정, INTO절의 테이블과 동일하거나 다를 수 있다.
- ON : UPDATE나 INSERT를 하게 될 조건으로, 해당 조건을 만족하는 DATA가 있으면 WHEN MATCHED 절을 실행하게 되고, 없으면 WHEN NOT MATCHED 이하를 실행하게 된다.
- WHEN MATCHED : ON 조건절이 TRUE인 ROW에 수행 할 내용 (UPDATE, DELETE포함 될 수 있음)
- WHEN NOT MATCHED : ON 조건절에 맞는 ROW가 없을 때 수행할 내용 (INSERT)

2.1 샘플1[편집]

MERGE INTO emp_merge_test m
--------------------------------
     USING emp e
--------------------------------
        ON (m.empno = e.empno)
--------------------------------
WHEN MATCHED THEN
      UPDATE SET m.sal = ROUND(m.sal*1.1)
      DELETE WHERE (m.deptno = 20)   -- 부서번호 20의 사원정보는 삭제.
--------------------------------
WHEN NOT MATCHED THEN
          INSERT (m.empno, m.deptno, m.sal)
          VALUES (e.empno, e.deptno, ROUND(e.sal*1.2))
;
--------------------------------
COMMIT;

2.2 샘플2[편집]

MERGE INTO TB_TUNE_SQL_STATS T
     USING (SELECT 1 SNAP_ID
                 , SQL_TEXT, SQL_FULLTEXT, SQL_ID, LAST_ACTIVE_TIME, LAST_ACTIVE_CHILD_ADDRESS
                 , PLAN_HASH_VALUE, PARSE_CALLS, DISK_READS, DIRECT_WRITES, BUFFER_GETS
                 , ROWS_PROCESSED, SERIALIZABLE_ABORTS, FETCHES, EXECUTIONS, END_OF_FETCH_COUNT
                 , LOADS, VERSION_COUNT, INVALIDATIONS, CPU_TIME
                 , ELAPSED_TIME, AVG_HARD_PARSE_TIME, APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME
                 , USER_IO_WAIT_TIME, PLSQL_EXEC_TIME, JAVA_EXEC_TIME, SORTS, SHARABLE_MEM, TOTAL_SHARABLE_MEM
                 , TYPECHECK_MEM, IO_INTERCONNECT_BYTES, PHYSICAL_READ_REQUESTS, PHYSICAL_READ_BYTES
                 , PHYSICAL_WRITE_REQUESTS, PHYSICAL_WRITE_BYTES
              FROM V$SQLSTATS 
             WHERE LAST_ACTIVE_TIME BETWEEN SYSDATE-1/24/60*10 AND SYSDATE -- 10분전 ([TODO] SNAPID 시간으로 변경)
          ) S
       ON (T.SQL_ID = S.SQL_ID 
          )
  WHEN MATCHED THEN
    -- SQL_ID가 같으면 업데이트 
   UPDATE SET T.SQL_TEXT = S.SQL_TEXT , T.SQL_FULLTEXT = S.SQL_FULLTEXT , T.LAST_ACTIVE_TIME = S.LAST_ACTIVE_TIME , T.LAST_ACTIVE_CHILD_ADDRESS = S.LAST_ACTIVE_CHILD_ADDRESS , T.PLAN_HASH_VALUE = S.PLAN_HASH_VALUE , T.PARSE_CALLS = S.PARSE_CALLS , T.DISK_READS = S.DISK_READS , T.DIRECT_WRITES = S.DIRECT_WRITES , T.BUFFER_GETS = S.BUFFER_GETS , T.ROWS_PROCESSED = S.ROWS_PROCESSED , T.SERIALIZABLE_ABORTS = S.SERIALIZABLE_ABORTS , T.FETCHES = S.FETCHES , T.EXECUTIONS = S.EXECUTIONS , T.END_OF_FETCH_COUNT = S.END_OF_FETCH_COUNT , T.LOADS = S.LOADS , T.VERSION_COUNT = S.VERSION_COUNT , T.INVALIDATIONS = S.INVALIDATIONS , T.CPU_TIME = S.CPU_TIME , T.ELAPSED_TIME = S.ELAPSED_TIME , T.APPLICATION_WAIT_TIME = S.APPLICATION_WAIT_TIME , T.CONCURRENCY_WAIT_TIME = S.CONCURRENCY_WAIT_TIME , T.USER_IO_WAIT_TIME = S.USER_IO_WAIT_TIME , T.PLSQL_EXEC_TIME = S.PLSQL_EXEC_TIME , T.JAVA_EXEC_TIME = S.JAVA_EXEC_TIME , T.SORTS = S.SORTS 
    WHEN NOT MATCHED THEN 
    -- SQL_ID가 없으면 인서트  
    INSERT (SNAP_ID,SQL_TEXT ,SQL_FULLTEXT ,SQL_ID ,LAST_ACTIVE_TIME ,LAST_ACTIVE_CHILD_ADDRESS ,PLAN_HASH_VALUE ,PARSE_CALLS ,DISK_READS ,DIRECT_WRITES ,BUFFER_GETS ,ROWS_PROCESSED ,SERIALIZABLE_ABORTS ,FETCHES ,EXECUTIONS ,END_OF_FETCH_COUNT ,LOADS ,VERSION_COUNT ,INVALIDATIONS  ,CPU_TIME ,ELAPSED_TIME ,APPLICATION_WAIT_TIME ,CONCURRENCY_WAIT_TIME  ,USER_IO_WAIT_TIME ,PLSQL_EXEC_TIME ,JAVA_EXEC_TIME ,SORTS)
    VALUES (S.SNAP_ID,S.SQL_TEXT ,S.SQL_FULLTEXT ,S.SQL_ID ,S.LAST_ACTIVE_TIME ,S.LAST_ACTIVE_CHILD_ADDRESS ,S.PLAN_HASH_VALUE ,S.PARSE_CALLS ,S.DISK_READS ,S.DIRECT_WRITES ,S.BUFFER_GETS ,S.ROWS_PROCESSED ,S.SERIALIZABLE_ABORTS ,S.FETCHES ,S.EXECUTIONS ,S.END_OF_FETCH_COUNT ,S.LOADS ,S.VERSION_COUNT ,S.INVALIDATIONS  ,S.CPU_TIME ,S.ELAPSED_TIME ,S.APPLICATION_WAIT_TIME ,S.CONCURRENCY_WAIT_TIME  ,S.USER_IO_WAIT_TIME ,S.PLSQL_EXEC_TIME ,S.JAVA_EXEC_TIME ,S.SORTS)
    ;

2.3 ORA-30926 에러[편집]

ORA-30926 : unable to get a stable set of rows in the source tables (원본 테이블의 고정 행 집합을 가져올 수 없습니다.)

  • INSERT 또는 UPDATE 할 때 1개의 레코드를 대상으로 작업이 가능
  • 하지만 SELECT의 결과가 2개 이상이 리턴되었기 때문에 오류 발생
  • INSERT 구문에서 중복이 발생하거나 UPDATE 에 MULTI ROW가 UPDATE되는 경우 오류
  • ON 구문에서 UPDATE되는 ROW가 1개 이상일 경우 오류
  • (해결방법) ON 절의 Join 조건에 USING() 에서 좋회하는 결과가 중복된 값이 없으면 된다.

2.4 ORA-00600 에러 발생(12c)[편집]

A SQL MERGE statement fails with next error on sqlplus and in the alert log:

ORA-00600: internal error code, arguments: [upsRowVec4]
2.4.1 해결 방안[편집]
  1. 패치
  2. alter session 명령
alter session set "_optimizer_eliminate_filtering_join"=false;

or

alter session set optimizer_features_enable='11.1.0.7';