MERGE는 하나의 문장으로 갱신과 삽입을 함께 처리할 수 있어서 동기화 배치에 자주 쓰인다. 그런데 잘 돌던 문장이 어느 날 이 에러를 뱉는 경우가 있다.
ORA-30926: unable to get a stable set of rows in the source tables메시지만 보면 무엇이 불안정하다는 건지 감이 잘 오지 않는다. 하지만 이 에러의 원인은 사실상 하나뿐이고, 그래서 진단 경로도 짧다.
에러가 말하는 것
MERGE는 ON 조건으로 타깃과 소스를 매칭한 뒤, 매칭된 행에 WHEN MATCHED 동작을 적용한다. 이때 타깃의 한 행에 소스의 두 행 이상이 매칭되면 오라클은 둘 중 어느 값으로 갱신해야 할지 결정할 수 없다.
어느 쪽을 골라도 틀리지 않지만 실행할 때마다 결과가 달라질 수 있다. 오라클은 그런 비결정적 동작을 허용하지 않고 문장 전체를 중단시킨다. 메시지의 stable set of rows는 이 뜻이다. 데이터가 깨졌다는 게 아니라 소스가 키 기준으로 유일하지 않다는 신고다.
재현해 보기
다음은 설명을 위한 최소 예시다.
create table t_target (id number primary key, amt number);
create table t_source (id number, amt number);
insert into t_target values (1, 100);
insert into t_source values (1, 10);
insert into t_source values (1, 20);
commit;소스에 id=1이 두 건 있다. 여기에 MERGE를 걸면 바로 에러가 난다.
merge into t_target t
using t_source s
on (t.id = s.id)
when matched then update set t.amt = s.amt
when not matched then insert (id, amt) values (s.id, s.amt);반대로 소스에 id=1이 한 건만 있으면 아무 문제 없이 돈다. 즉 문장이 잘못된 게 아니라 그날의 데이터가 조건을 깬 것이다. 어제까지 멀쩡하던 배치가 갑자기 죽는 이유가 여기 있다.
진단
가장 먼저 볼 것은 소스를 ON 절의 키로 묶었을 때 중복이 있는지다.
select id, count(*)
from t_source
group by id
having count(*) > 1;실제 배치에서는 USING 절이 단순 테이블이 아니라 여러 테이블을 조인한 서브쿼리인 경우가 많다. 이때는 원본 테이블에 중복이 없어도 조인 과정에서 행이 불어나는 경우가 흔하다. 그래서 테이블이 아니라 USING에 들어간 집합 자체를 그대로 떼어 내서 확인해야 한다.
select k, count(*)
from (
select a.ord_no k, b.amt
from orders a
join order_item b on b.ord_no = a.ord_no
where a.stat = 'N'
) group by k
having count(*) > 1;주문 한 건에 품목이 여러 개면 조인 결과는 당연히 여러 행이 된다. 이 집합을 주문번호 하나로 MERGE하려 했으니 에러가 나는 게 정상이다.
드물지만 타깃 쪽 문제일 수도 있다. 타깃 테이블에 기본키나 유일 인덱스가 없고 ON 조건 컬럼에 중복이 있으면 같은 에러가 난다. 소스에 중복이 없는데도 에러가 계속된다면 타깃도 같은 방식으로 확인한다.
해결 패턴
원인이 소스의 중복이므로, 해결은 소스를 키 기준으로 유일하게 만드는 것이다. 어떤 방식을 쓸지는 업무 규칙이 정한다.
1. 집계로 합친다. 여러 행을 합산하거나 최대값을 취하는 게 맞는 경우다.
merge into t_target t
using (select id, sum(amt) amt from t_source group by id) s
on (t.id = s.id)
when matched then update set t.amt = s.amt
when not matched then insert (id, amt) values (s.id, s.amt);2. 우선순위 규칙으로 한 행만 고른다. 가장 최근 값이나 특정 상태의 행만 반영해야 하는 경우다.
merge into t_target t
using (
select id, amt from (
select id, amt, row_number() over (partition by id order by upd_dt desc, seq desc) rn
from t_source
) where rn = 1
) s
on (t.id = s.id)
when matched then update set t.amt = s.amt
when not matched then insert (id, amt) values (s.id, s.amt);order by에 컬럼을 하나만 쓰면 값이 같을 때 순서가 다시 비결정적이 된다. 위처럼 동점을 깨는 컬럼을 하나 더 두는 편이 안전하다.
3. ON 조건을 보강한다. 업무상 키가 복합인데 일부만 ON에 넣은 경우다. 이때는 소스를 가공할 게 아니라 조건을 바로잡아야 한다. 집계로 덮어버리면 에러는 사라지지만 데이터가 조용히 뭉개진다.
같이 걸리는 에러 하나
MERGE를 고치다 보면 다음 에러를 만나는 경우가 있다.
ORA-38104: Columns referenced in the ON Clause cannot be updatedON 절에 쓴 컬럼은 WHEN MATCHED의 UPDATE 대상이 될 수 없다는 뜻이다. 매칭 기준을 갱신하면 매칭 자체가 무너지기 때문이다. 키 컬럼을 바꿔야 하는 상황이라면 그건 MERGE가 아니라 DELETE 후 INSERT로 풀어야 하는 일이다.
정리
ORA-30926은 문법이나 권한 문제가 아니라 데이터 문제다. 따라서 문장을 아무리 들여다봐도 답이 안 나오고, USING 집합을 키로 묶어 세어 보는 순간 대부분 바로 드러난다.
그리고 예방이 어렵지 않다. MERGE를 새로 만들 때 USING 집합이 ON 키 기준으로 유일하다는 것을 한 번 확인해 두면 된다. 지금은 유일하더라도 나중에 데이터가 늘면서 깨질 수 있는 구조인지까지 보면 더 좋다. 이 에러는 대부분 처음부터 존재했지만 데이터가 아직 그 조건을 안 만들었을 뿐인 경우다.