Oracle ORA-01436 오류 원인과 해결 방법 완벽 가이드

ORA-01436
2026년 07월 14일 | DBMS Error 가이드

이 글에서 다루는 내용

ORA-01436 에러의 원인 분석, 해결 SQL, 예방 방법을 실무 관점에서 정리합니다.

ORA-01436 CONNECT BY loop in user data 는?

ORA-01436 에러는 Oracle의 계층형 쿼리(Hierarchical Query)에서 CONNECT BY 절을 사용할 때, 데이터 내에 순환 참조(Circular Reference)가 존재할 경우 발생하는 에러입니다. 쉽게 말해, 부모-자식 관계를 따라 트리를 탐색하는 도중 A→B→C→A처럼 다시 시작점으로 돌아오는 사이클이 발생했을 때 Oracle이 이를 감지하고 오류를 던지는 것입니다. 주로 조직도, BOM(Bill of Materials), 카테고리 계층 구조 등 재귀적 관계를 표현하는 테이블에서 자주 목격되며, 데이터 입력 오류나 설계 결함이 주된 원인입니다.

주요 발생 원인

1. 데이터 내 순환 참조(Circular Reference) 존재

가장 흔한 원인으로, 테이블 내 부모-자식 관계 데이터가 서로를 참조하는 순환 구조를 이루는 경우입니다. 예를 들어 사원 테이블에서 A의 상사가 B이고, B의 상사가 다시 A로 설정된 경우 CONNECT BY로 트리를 순회하면 무한 루프에 빠지게 됩니다. 이는 데이터 입력 단계에서 무결성 검사가 충분하지 않을 때 발생하며, 대용량 데이터 마이그레이션 이후 특히 자주 발생합니다.

2. 자기 자신을 부모로 참조하는 레코드(Self-Reference)

특정 레코드가 자기 자신을 부모 노드로 참조하는 경우입니다. 예를 들어 EMPLOYEE_ID = 100인 사원의 MANAGER_ID도 100으로 저장되어 있다면, 해당 행 자체가 루프를 형성합니다. 이는 가장 단순하면서도 찾기 쉬운 형태의 순환 참조이며, 루트 노드를 잘못 설정하거나 배치 작업 중 실수로 발생하는 경우가 많습니다.

3. NOCYCLE 옵션 미사용 및 루프 감지 로직 부재

Oracle 10g 이상에서는 CONNECT BY NOCYCLE 옵션을 통해 루프를 자동으로 건너뛸 수 있음에도 불구하고, 이를 적용하지 않고 기존 쿼리를 그대로 사용하는 경우입니다. 또한 CONNECT_BY_ISCYCLE 가상 열(Pseudo Column)을 활용하면 루프를 형성하는 레코드를 사전에 식별할 수 있는데, 이러한 방어 로직 없이 쿼리를 실행하면 데이터 품질 문제가 있을 때마다 에러가 반복됩니다.

해결 방법

방법 1: NOCYCLE 옵션으로 즉시 우회

가장 빠른 임시 해결책은 CONNECT BY 절에 NOCYCLE 키워드를 추가하는 것입니다. 이렇게 하면 Oracle이 루프를 감지하는 즉시 해당 경로를 건너뛰고 쿼리를 정상 완료합니다.

-- NOCYCLE 적용 전 (ORA-01436 발생)
SELECT employee_id, manager_id, last_name, LEVEL
FROM   employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

-- NOCYCLE 적용 후 (루프 우회)
SELECT employee_id, manager_id, last_name, LEVEL,
       CONNECT_BY_ISCYCLE AS is_cycle  -- 1이면 해당 행이 루프 형성
FROM   employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR employee_id = manager_id;

방법 2: 순환 참조 데이터 식별 및 수정

CONNECT_BY_ISCYCLE 가상 열을 활용하여 문제 데이터를 식별하고, 실제 데이터를 수정합니다.

-- 루프를 형성하는 레코드 식별
SELECT employee_id, manager_id, last_name, CONNECT_BY_ISCYCLE AS is_cycle
FROM   employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR employee_id = manager_id
WHERE  CONNECT_BY_ISCYCLE = 1;

-- 자기 자신을 참조하는 레코드 찾기 (Self-Reference)
SELECT employee_id, manager_id, last_name
FROM   employees
WHERE  employee_id = manager_id;

-- 문제 데이터 수정 예시 (올바른 manager_id로 업데이트)
UPDATE employees
SET    manager_id = 101  -- 올바른 상위 관리자 ID로 변경
WHERE  employee_id = 100
AND    manager_id  = 100;  -- 자기 자신을 참조하던 레코드

COMMIT;

방법 3: 재귀 CTE(WITH절)를 사용한 루프 탐지

Oracle 11g R2 이상에서는 재귀 CTE를 활용하여 계층 경로를 추적하고, 루프가 발생하는 정확한 경로를 파악할 수 있습니다.

-- 재귀 CTE로 순환 경로 전체 추적
WITH hierarchy_check (employee_id, manager_id, path, is_cycle) AS (
    -- Anchor: 루트 노드
    SELECT employee_id,
           manager_id,
           TO_CHAR(employee_id) AS path,
           0 AS is_cycle
    FROM   employees
    WHERE  manager_id IS NULL

    UNION ALL

    -- Recursive: 자식 노드 탐색
    SELECT e.employee_id,
           e.manager_id,
           h.path || ' -> ' || TO_CHAR(e.employee_id),
           CASE
               WHEN INSTR(h.path, TO_CHAR(e.employee_id)) > 0 THEN 1
               ELSE 0
           END AS is_cycle
    FROM   employees e
    JOIN   hierarchy_check h ON e.manager_id = h.employee_id
    WHERE  h.is_cycle = 0  -- 이미 루프가 감지된 경로는 더 이상 탐색하지 않음
)
SELECT employee_id, manager_id, path
FROM   hierarchy_check
WHERE  is_cycle = 1;

방법 4: 데이터 입력 시 제약 조건 추가

데이터 삽입 단계에서 자기 참조를 원천 차단하는 CHECK 제약 조건을 추가합니다.

-- 자기 자신을 부모로 참조하지 못하도록 CHECK 제약 추가
ALTER TABLE employees
ADD CONSTRAINT chk_no_self_ref
CHECK (employee_id <> manager_id);

예방 방법

1. 데이터 입력/수정 시 순환 참조 사전 검증 프로시저 도입

계층 데이터를 INSERT하거나 UPDATE할 때마다 호출되는 검증 프로시저 또는 트리거를 구현하는 것이 Best Practice입니다. 아래와 같이 DML 전에 루프 여부를 먼저 확인하고, 문제가 있을 경우 예외를 발생시켜 잘못된 데이터가 테이블에 저장되지 않도록 합니다.

-- 순환 참조 검증 프로시저 예시
CREATE OR REPLACE PROCEDURE validate_hierarchy(
    p_employee_id IN employees.employee_id%TYPE,
    p_manager_id  IN employees.manager_id%TYPE
) AS
    v_count NUMBER;
BEGIN
    -- 변경 후 순환 참조 발생 여부 사전 확인
    SELECT COUNT(*)
    INTO   v_count
    FROM   employees
    START WITH employee_id = p_manager_id
    CONNECT BY NOCYCLE PRIOR manager_id = employee_id
    WHERE  employee_id = p_employee_id;

    IF v_count > 0 THEN
        RAISE_APPLICATION_ERROR(-20001,
            'Circular reference detected: Employee ' ||
            p_employee_id || ' -> Manager ' || p_manager_id);
    END IF;
END validate_hierarchy;
/

2. 주기적인 데이터 품질 점검 쿼리 스케줄링

운영 환경에서는 배치 작업이나 대량 데이터 이관 후 계층 데이터의 무결성이 깨질 가능성이 항상 존재합니다. DBMS_SCHEDULER 또는 외부 스케줄러(크론잡 등)를 활용하여 아래와 같은 점검 쿼리를 정기적(일 1회 이상)으로 실행하고, 이상 발견 시 담당자에게 알림을 보내는 체계를 구축하는 것을 강력히 권고합니다.

-- 주기적 순환 참조 점검 쿼리 (배치 스케줄에 등록)
SELECT employee_id, manager_id,
       CONNECT_BY_ISCYCLE AS cycle_flag,
       SYS_CONNECT_BY_PATH(employee_id, '/') AS path
FROM   employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR employee_id = manager_id
HAVING CONNECT_BY_ISCYCLE = 1;
-- 결과가 1건 이상이면 알림 발송 로직 연동