⑤ CONNECT_BY_ISCYCLE

눈치챘는지는 모르겠지만 오라클의 계층형 쿼리는 루프(반복) 알고리즘을 사용한다. 계층형 구조나 레벨은 테이블에 있는 데이터에 따라 동적으로 변경되므로, 내부적으로는 루프를 돌며 자식 노드를 찾아간다. 루프 알고리즘에서 주의할 점은 조건을 잘못 주면 무한루프를 타게 된다는 점인데, 계층형 쿼리에서도 부모-자식 간의 관계를 정의하는 값이 잘못 입력되면 무한루프를 타고 오류가 발생한다.

예를 들어, 생산팀(170)의 부모 부서는 구매/생산부(30)인데, 구매/생산부의 parent_id 값을 생산부로 바꾸면 두 부서가 상호 참조가 되어 무한루프가 발생할 것이다. 직접 확인해 보자.

입력

    UPDATE departments
       SET parent_id = 170
     WHERE department_id = 30;

    SELECT department_id, LPAD(' ' , 3 * (LEVEL-1)) || department_name, LEVEL,
           parent_id
      FROM departments
      START WITH department_id = 30
    CONNECT BY PRIOR department_id  = parent_id;

결과

    SQL 오류: ORA-01436: CONNECT BY의 루프가 발생되었습니다.

“CONNECT BY의 루프가 발생”되었다는 것은 무한루프가 일어났다는 의미다. 이때는 루프가 발생된 원인을 찾아 잘못된 데이터를 수정해야 하는데, 이를 위해서는 먼저 CONNECT BY 절에 NOCYCLE을 추가하고 SELECT 절에 CONNECT_BY_ISCYCLE 의사 컬럼을 사용해 찾을 수 있다. CONNECT_BY_ISCYCLE은 다음과 같이 현재 로우가 자식을 갖고 있는데 동시에 그 자식 로우가 부모 로우이면 1을, 그렇지 않으면 0을 반환한다.

입력

    SELECT department_id, LPAD(' ' , 3 * (LEVEL-1)) || department_name AS depname, LEVEL,
           CONNECT_BY_ISCYCLE IsLoop,
           parent_id
      FROM departments
      START WITH department_id = 30
    CONNECT BY NOCYCLE PRIOR department_id  = parent_id;

생산팀의 부모는 구매/생산부고, 구매/생산부의 부모가 다시 생산팀이 되어 IsLOOP 컬럼 값이 1이 된 것이다. 이런 식으로 오류의 원인이 되는 데이터를 찾아 내어 수정하면 된다.