"SQL MERGE"의 두 판 사이의 차이
DB CAFE
(→MERGE INTO 절) |
|||
(같은 사용자의 중간 판 16개는 보이지 않습니다) | |||
1번째 줄: | 1번째 줄: | ||
− | == MERGE INTO 절 == | + | === MERGE INTO 절 === |
{{틀:타이틀 투명 | {{틀:타이틀 투명 | ||
− | |제목= | + | |보더색=#696969 |
− | # '''(조인) 조건절에 사용한 컬럼'''은 UPDATE 불가 | + | |배경색=#483d8b |
+ | |제목= - 주의사항 | ||
+ | # '''ON(조인) 조건절에 사용한 컬럼'''은 UPDATE 불가 | ||
# '''트리거 발생되지 않음''' | # '''트리거 발생되지 않음''' | ||
}} | }} | ||
− | == MERGE 문법 == | + | === MERGE 문법 === |
+ | https://docs.oracle.com/database/121/SQLRF/img/merge.gif | ||
<source lang=sql> | <source lang=sql> | ||
MERGE [ hint ] | MERGE [ hint ] | ||
-- 1.MERGE 대상 테이블 | -- 1.MERGE 대상 테이블 | ||
INTO [ schema. ] { table | view } [ t_alias ] | INTO [ schema. ] { table | view } [ t_alias ] | ||
+ | |||
-- 2.비교 대상 조회 | -- 2.비교 대상 조회 | ||
USING { [ schema. ] { table | view } | USING { [ schema. ] { table | view } | ||
| subquery | | subquery | ||
} [ t_alias ] | } [ t_alias ] | ||
+ | |||
-- 3.조인 조건 | -- 3.조인 조건 | ||
ON ( condition ) | ON ( condition ) | ||
+ | |||
-- 4.데이터 존재시 -- | -- 4.데이터 존재시 -- | ||
WHEN MATCHED THEN | WHEN MATCHED THEN | ||
− | UPDATE SET column = { expr | DEFAULT } | + | UPDATE SET column = { expr | DEFAULT } |
[, column = { expr | DEFAULT } ]... | [, column = { expr | DEFAULT } ]... | ||
− | [ DELETE where_clause ] | + | [ DELETE where_clause ] |
+ | |||
-- 4.데이터 미존재시 -- | -- 4.데이터 미존재시 -- | ||
WHEN NOT MATCHED THEN | WHEN NOT MATCHED THEN | ||
− | INSERT [ (column [, column ]...) ] | + | INSERT [ (column [, column ]...) ] |
− | VALUES ({ expr [, expr ]... | DEFAULT }) | + | VALUES ({ expr [, expr ]... | DEFAULT }) |
; | ; | ||
</source> | </source> | ||
34번째 줄: | 41번째 줄: | ||
:- WHEN NOT MATCHED : ON 조건절에 맞는 ROW가 없을 때 수행할 내용 (INSERT) | :- WHEN NOT MATCHED : ON 조건절에 맞는 ROW가 없을 때 수행할 내용 (INSERT) | ||
− | == 샘플1 == | + | ==== 샘플1 ==== |
<source lang=sql> | <source lang=sql> | ||
MERGE INTO emp_merge_test m | MERGE INTO emp_merge_test m | ||
− | USING emp e | + | -------------------------------- |
− | ON (m.empno = e.empno) | + | USING emp e |
+ | -------------------------------- | ||
+ | ON (m.empno = e.empno) | ||
+ | -------------------------------- | ||
WHEN MATCHED THEN | WHEN MATCHED THEN | ||
− | + | UPDATE SET m.sal = ROUND(m.sal*1.1) | |
− | + | DELETE WHERE (m.deptno = 20) -- 부서번호 20의 사원정보는 삭제. | |
+ | -------------------------------- | ||
WHEN NOT MATCHED THEN | WHEN NOT MATCHED THEN | ||
− | INSERT (m.empno, m.deptno, m.sal) | + | INSERT (m.empno, m.deptno, m.sal) |
− | VALUES (e.empno, e.deptno, ROUND(e.sal*1.2)); | + | VALUES (e.empno, e.deptno, ROUND(e.sal*1.2)) |
+ | ; | ||
+ | -------------------------------- | ||
COMMIT; | COMMIT; | ||
</source> | </source> | ||
− | == 샘플2 == | + | ==== 샘플2 ==== |
<source lang=sql> | <source lang=sql> | ||
76번째 줄: | 89번째 줄: | ||
</source> | </source> | ||
− | == ORA-00600 에러 발생(12c) == | + | ==== 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() 에서 좋회하는 결과가 중복된 값이 없으면 된다. | ||
+ | |||
+ | ==== ORA-00600 에러 발생(12c) ==== | ||
− | <source> | + | <source lang=shell> |
A SQL MERGE statement fails with next error on sqlplus and in the alert log: | A SQL MERGE statement fails with next error on sqlplus and in the alert log: | ||
ORA-00600: internal error code, arguments: [upsRowVec4] | ORA-00600: internal error code, arguments: [upsRowVec4] | ||
</source> | </source> | ||
− | === 해결 방안 === | + | ===== 해결 방안 ===== |
# 패치 | # 패치 | ||
# alter session 명령 | # alter session 명령 |
2024년 1월 24일 (수) 18:38 기준 최신판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 MERGE INTO 절[편집]
attach_file - 주의사항
- ON(조인) 조건절에 사용한 컬럼은 UPDATE 불가
- 트리거 발생되지 않음
2 MERGE 문법[편집]
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 해결 방안[편집]
- 패치
- alter session 명령
alter session set "_optimizer_eliminate_filtering_join"=false;
or
alter session set optimizer_features_enable='11.1.0.7';