오라클로 ETL(데이터를 모아 옮기고 정리하는 작업)과 데이터마트 일을 하다 보면, 같은 에러를 몇 번이고 다시 만나게 됩니다.
문제는 에러 메시지만 봐서는 원인이 바로 떠오르지 않는 경우가 훨씬 많다는 점입니다. 영어로 된 짧은 한 줄만 보고는 어디부터 손대야 할지 막막할 때가 많습니다.
이 글에서는 실제 업무에서 직접 부딪혔던 오라클 에러 세 가지를 정리했습니다.
각 에러마다 에러 메시지 원문, 어떤 상황에서 났는지, 원인, 해결 방법 순서로 적었습니다. 같은 에러로 검색해서 들어오신 분이라면 "원인" 부분만 빠르게 확인하셔도 됩니다.
이런 에러들은 검색해 보면 한두 줄짜리 짧은 답만 나오는 경우가 많습니다. 급한 마음에 명령어만 따라 치고 넘어가다 보면, 정작 왜 그렇게 하면 풀리는지는 모른 채 지나가기 쉽습니다.
그래서 이 글에서는 해결 명령어만 늘어놓기보다, 오라클이 왜 그 상황을 막았는지까지 하나씩 같이 짚어 보려고 합니다. 원리를 알고 나면 비슷한 에러를 다시 만났을 때 훨씬 빨리 원인을 찾을 수 있습니다.
① ORA-14039: 파티션 인덱스와 유니크 제약 조건이 부딪히는 문제
에러 메시지
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index어떤 상황에서 났나
날짜 기준으로 range 파티션(큰 테이블을 날짜 구간별로 나눠 저장하는 방식)을 걸어 둔 대용량 테이블이 있었습니다. 여기에 몇 개 컬럼을 묶어서 중복을 막는 유니크 제약(UK)을 걸려고 하다가 이 에러를 만났습니다.
파티션 키도 이미 잘 잡혀 있는데 왜 갑자기 안 되는지, 처음에는 이해가 가지 않았습니다.
원인
오라클에서 파티션별로 따로 만들어지는 인덱스(로컬 파티션 인덱스)로 유니크 제약을 걸려면, 유니크 인덱스를 이루는 컬럼 안에 파티션 키 컬럼이 반드시 들어 있어야 합니다.
제가 만들려던 유니크 제약은 업무에서 쓰는 키 컬럼들로만 되어 있었고, 파티션 키로 쓰던 날짜 컬럼(ANAL_DATE)이 빠져 있었던 게 문제였습니다.
파티션 키가 유니크 인덱스 컬럼 목록에 없으면, 파티션 하나하나가 각자 따로 중복을 막아 줄 수가 없습니다. 그래서 오라클이 이런 조합 자체를 처음부터 막아 버립니다. 쉽게 말해, 같은 값이 서로 다른 날짜 파티션에 하나씩 들어가 버리면 각 파티션 안에서는 중복이 아니어서 걸러 낼 방법이 없기 때문입니다.
해결 방법
유니크 인덱스 컬럼 목록에 파티션 키인 날짜 컬럼을 넣는 방법으로 해결했습니다.
CREATE UNIQUE INDEX UX_ANAL_RESULT
ON ANAL_RESULT (REQ_NO, ANAL_ITEM, ANAL_DATE)
LOCAL;업무적으로는 REQ_NO와 ANAL_ITEM 두 개만으로 중복을 막고 싶었습니다. 하지만 파티션 구조를 그대로 유지하려면, 파티션 키까지 포함한 조합으로 "무엇이 중복인지"를 다시 정해야 했습니다.
이 과정에서 기본키(PK)와 유니크 제약(UK)의 차이도 다시 짚어 보게 됐습니다. UK는 NULL 값을 허용한다는 점, 그리고 인덱스를 새로 만든 뒤에는 통계 정보를 갱신해야 오라클의 실행 계획 도구(옵티마이저)가 새 인덱스를 제대로 쓴다는 점도 함께 확인했습니다.
다시 겪지 않으려면
파티션 테이블을 설계하는 단계에서부터, 나중에 어떤 컬럼 조합으로 유니크 제약을 걸 수도 있는지 미리 따져 두는 게 좋습니다.
설계 초기에는 필요 없어 보이던 유니크 제약이, 나중에 데이터가 서로 안 맞는 문제 때문에 필요해지는 경우가 많습니다. 그때 가서 파티션 키와 부딪힌다는 걸 알게 되면, 이미 쌓인 데이터 구조를 다시 손봐야 하는 부담이 생깁니다.
가능하다면 유니크 제약 후보가 될 만한 컬럼 조합에는 처음부터 파티션 키를 함께 넣어서 설계해 두는 편이 훨씬 안전합니다.
② ORA-30926: MERGE 문에서 원본 데이터가 중복된 문제
에러 메시지
ORA-30926: unable to get a stable set of rows in the source tables어떤 상황에서 났나
설비를 자동으로 배정하는 로직을 MERGE 문(있으면 수정하고 없으면 넣는 명령)으로 만들고 있었습니다.
REQ_NO와 ANAL_NO를 묶은 복합 키로 원본(소스)과 대상(타겟)을 짝지으려고 했는데, 실행할 때마다 이 에러가 났습니다.
원인
MERGE 문은 원본 쪽 데이터가 연결 키 기준으로 딱 한 줄씩만 짝지어져야 제대로 움직입니다.
원본 서브쿼리 결과에 같은 키 조합을 가진 줄이 두 개 이상 있으면, 오라클은 어느 줄을 기준으로 수정해야 할지 정할 수 없어서 이 에러를 냅니다. 같은 대상 행을 두 번 고치라는 명령이 들어온 셈이라, 어떤 값이 최종 결과가 될지 보장할 수 없기 때문입니다.
제 경우에는 설비 배정 순위를 매기는 서브쿼리에서, 같은 순위가 여러 건 나올 수 있다는 걸 놓친 게 원인이었습니다.
해결 방법
DENSE_RANK() 윈도우 함수로 순위를 매길 때, 점수가 같을 경우 순서를 가려 줄 컬럼을 하나 더 넣어서 순위가 항상 하나로 정해지도록 서브쿼리를 고쳤습니다.
SELECT REQ_NO, ANAL_NO, EQP_ID,
DENSE_RANK() OVER (
PARTITION BY REQ_NO
ORDER BY PRIORITY_SCORE DESC, EQP_ID ASC
) AS RNK
FROM EQUIPMENT_CANDIDATEORDER BY 절에 EQP_ID처럼 항상 서로 다른 값을 갖는 컬럼을 넣어서, 동점이 생기는 상황 자체를 없앤 것이 핵심이었습니다.
이 에러를 겪은 뒤로는 MERGE 문을 짤 때마다, 원본 서브쿼리를 먼저 따로 실행해서 연결 키 기준으로 중복이 없는지 확인하는 습관이 생겼습니다.
다시 겪지 않으려면
MERGE 문을 쓰기 전에, 원본 서브쿼리만 따로 떼어 내서 연결 키 기준으로 GROUP BY와 HAVING COUNT(*) > 1을 걸어 보면 중복이 있는지 미리 확인할 수 있습니다. 이 확인 단계를 작업 순서에 넣어 두면 좋습니다.
특히 순위나 랭킹 함수로 짝을 정하는 로직을 짤 때는, 동점이 생길 수 있다는 걸 항상 염두에 두고 정렬 기준에 서로 겹치지 않는 값을 가진 컬럼을 넣는 습관을 들이는 게 훨씬 안전합니다.
③ ORA-02299: 유니크 제약을 걸 수 없음 (이미 있는 중복 데이터)
에러 메시지
ORA-02299: cannot validate (schema.constraint_name) - duplicate keys found어떤 상황에서 났나
환경 분석 결과를 저장하는 EAV(속성-값) 구조 테이블이 있었습니다. EAV는 항목 이름과 값을 줄 단위로 쌓는 방식의 테이블 구조입니다. 여기에 7개 차원 컬럼과 분석 항목 컬럼을 합친 조합으로 유니크 제약을 새로 걸려고 했습니다.
그런데 제약을 추가하는 순간 이 에러가 났습니다.
원인
이 에러는 제약을 쓰는 문법이 틀렸다는 뜻이 아닙니다. 이미 테이블에 쌓여 있는 데이터 가운데, 그 컬럼 조합 기준으로 중복된 줄이 있다는 뜻입니다. 오라클은 제약을 걸 때 기존 데이터도 규칙을 지키는지 검사하는데, 여기서 걸린 겁니다.
제약을 걸려고 할 때는 몰랐지만, 예전에 배치 작업 오류 때문에 같은 조합의 데이터가 여러 번 중복으로 들어간 적이 있었습니다.
해결 방법
먼저 중복된 줄을 찾는 쿼리로 실제 중복 데이터를 확인했습니다.
SELECT DIM1, DIM2, DIM3, DIM4, DIM5, DIM6, DIM7, ANAL_ITEM, COUNT(*)
FROM FAB_ENV_ANAL_RESULT
GROUP BY DIM1, DIM2, DIM3, DIM4, DIM5, DIM6, DIM7, ANAL_ITEM
HAVING COUNT(*) > 1;중복된 줄 가운데 어떤 걸 남기고 어떤 걸 지울지 기준을 먼저 정했습니다(가장 최근에 등록된 건을 남긴다는 원칙). 그 기준대로 나머지를 정리하고 나서야 제약을 제대로 걸 수 있었습니다.
이 일을 계기로, 대용량 집계 테이블에는 처음부터 유니크 제약을 걸어 두거나, 적어도 정기적으로 중복 확인 쿼리를 돌리는 절차를 팀 안에 만들어 두게 됐습니다.
다시 겪지 않으려면
배치 작업이 실패하거나 다시 실행되면서 데이터가 중복으로 들어갈 수 있다면, 처음부터 적재 로직을 멱등하게 설계하는 게 근본적인 해결책입니다. 멱등하다는 건 같은 작업을 몇 번을 다시 돌려도 결과가 한 번 돌린 것과 똑같다는 뜻입니다.
다만 이미 운영 중인 테이블이라면, 유니크 제약을 나중에 추가하기 전에 반드시 위와 같은 중복 확인 쿼리를 먼저 돌려 보세요. 그다음 실제 업무 규칙에 맞는 정리 기준을 세우고 데이터를 정리하는 순서를 지키는 게 훨씬 안전합니다.
④ 마치며
정리하면, 이 세 가지 에러는 각각 파티션 설계, MERGE 원본 데이터 확인, 그리고 미리 데이터가 맞는지 점검하는 일이라는 서로 다른 원인에서 생겼습니다. 하지만 공통점이 하나 있었습니다. 모두 "제약이나 문법의 문제가 아니라, 데이터와 설계 구조 자체를 다시 들여다봐야 한다는 신호"였다는 점입니다.
에러 메시지만 검색해서 급하게 넘어가기보다, 오라클이 왜 이 상황을 막고 있는지 원리를 이해하고 넘어가면 비슷한 에러를 다음에 훨씬 빨리 진단할 수 있습니다.
같은 실수를 되풀이하지 않으려면, 에러가 해결된 그 순간에 원인과 해결 과정을 짧게라도 적어 두는 습관이 결국 가장 큰 도움이 됩니다.
시간이 지나면 분명히 겪었던 에러인데도 원인이 가물가물해지는 경우가 많습니다. 그래서 이런 짧은 기록이 나중에 똑같은 시행착오를 반복하지 않게 해 주는 가장 확실한 안전장치가 됩니다.
앞으로도 업무에서 되풀이해서 만나는 에러가 생길 때마다, 이런 식으로 원인과 해결 과정을 정리해서 계속 모아 둘 생각입니다.