CONNECT_BY_ISCYCLE(중복 참조값 찾기)

[황정식]

● CONNECT_BY_ISCYCLE(중복 참조값 찾기)

: CONNECT_BY_ISCYCLE 역시 계층형 쿼리에서 사용되는 의사컬럼의 한 종류인데, 해당 로우의 항목이 자식노드를 갖고 있는데 동시에 그 자식노드가 해당 로우의 부모노드가 되는지를 판별하는 의사컬럼이다. 즉 중복 참조를 하는 경우 이를 판별하여 중복 참조를 하는 자식 노드가 있을 경우 1을, 없을 경우 0을 반환한다.

예)

 SELECT   item_id, LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || item_name item_names
      FROM   bom
START WITH   item_id = 1005
CONNECT BY   PRIOR item_id = parent_id;

--> 결과,

ITEM_ID     LEVEL     ITEM_NAMES

1005              1          Mother Board
1008              2            RAM
1009              2            CPU
1010              2            그래픽장치
1011              2            기타장치
--> 이 쿼리는 컴퓨터 중 Mother Board와 그에 속한 부품들을 조회한 것이다. 즉 item_id값이 1005인 Mother Board가 최상위 루트노드가 된다. 그런데 데이터를 조작하던중 다음과 같이 실수를 하였을 경우를 가정해보자.

UPDATA bom

     SET  parent_id = 1010

WHERE  item_id = 1005;

--> Mother Board의 상위 품목은 item_id 값이 1002인 본체인데 잘못해서 하위 품목인 그래픽 장치를 상위 품목으로 변경한것이다. 이렇게 되면 데이터는 다음과 같이 수정되어 있을 것이다.

: 이러한 경우는 두 항목의 로우가 서로를 자식노드로 간주하므로 내부적으로 무한루프에 빠지게 되어 오류를 발생시킨다. 

바로 이러한 경우에 CONNECT_BY_ISCYCLE을 사용한다. CONNECT_BY_ISCYCLE은 반드시 CONNECT BY 절에 NOCYCLE이 명시되어있어야 사용이 가능하다. 루프가 발생한 문장의 CONNECT BY절에 NOCYCLE을 명시하면 루프 발생없이 다음과 같이 오류가 발생하지 않는다.

SELECT   item_id, LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || item_name item_names
      FROM   bom
START WITH   item_id = 1005
CONNECT BY   NOCYCLE PRIOR item_id = parent_id;

...

...

5 rows selected

 SELECT   item_id, LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || item_name item_names,
             CONNECT_BY_ISCYCLE cycles
      FROM   bom
START WITH   item_id = 1005
CONNECT BY   NOCYCLE PRIOR item_id = parent_id;

-->결과,

ITEM_ID     LEVEL     ITEM_NAMES     CYCLES

1005             1           Mother Board          0
1008             2             RAM                    0
1009             2             CPU                     0
1010             2             그래픽장치            1
1011             2             기타장치               0
--> 여기에 CONNECT_BY_ISCYCLE 의사컬럼을 사용하면 어느 항목에서 루프가 발생했는지 찾아낼 수 있다.

결과를 보면, CONNECT_BY_ISCYCLE을 사용한 컬럼인 CYCLES의 값이 1인건이 그래픽장치이므로, 그래픽장치에서 루프가 발생했음을 알 수 있다.

--> CONNECT_BY_ISCYCLE 의사컬럼은 CONNECT BY 루프가 발생하는 계층형 쿼리에서 어느 항목에서 루프가 발생하는지 찾기 위해서 사용되는 역할을 하는 의사컬럼이며, 반드시 CONNECT BY 절에 NOCYCLE이 사용되어야 한다.