[불친절한 SQL 프로그래밍] [16장] 계층 쿼리(2) (WITH, SEARCH, CYCLE)

16.2 재귀 서브 쿼리 팩토링

11.2 버전부터 재귀 서프 쿼리 팩토링 기능을 사용할 수 있다. 재귀 서브 쿼리 팩토링은 ANSI 표준 SQL 문법이다. 계층 쿼리 절보다 복잡하지만 다양한 기능을 사용할 수 있다.

 

16.2.1 기본 문법

재귀 서브 쿼리 팩토링은 WITH절을 사용한다. 재귀 서브 쿼리 팩토링의 WITH 절은 서브 쿼리, SEARCH절, CYCLE절로 구성된다. 

WITH query_name ([c_alias [, c_alias]) AS (subquery) [search_clause][cycle_clause]

서브 쿼리는 UNION ALL 연산자로 구성된다. UNION ALL 연산자의 상단 쿼리가 START WITH 절, 하단 쿼리가 CONNECT BY 절의 역할을 수행한다. 

WITH 절에 정의한 서브 쿼리를 하단 쿼리와 조인함으로써 재귀적으로 조인이 수행되는 방식으로 동작한다.

 

위 쿼리는 순방향으로 계층을 전개한다. 현재 노드의 mgr이 부모 노드의 empno 인 행을 조회한다.

위 쿼리는 역방향으로 계층을 전개한다. 현재 노드의 empno가 자식 노드의 mgr인 행을 조회한다.

재귀 서브 쿼리 팩토링은 계층 정보를 조회하기 위한 연산자와 슈도 칼럼을 제공하지 않는다. 

 

16.2.2 계층 정렬

재귀 서브 쿼리 팩토링은 계층을 정렬하기 위해 SEARCH 절을 제공한다. BREADTH 방식과 DEPTH 방식을 사용할 수 있으며, FIRST BY 뒤에 기술된 c_alias에 따라 행이 정렬된다.

ordering_column은 정렬 순번이 반환될 열을 지정한다.

SEARCH {DEPTH|BREADTH} FIRST BY c_alias [, c_alias] ... SET ordering_column

방식	설명
BREADTH	자식 행을 반환하기 전에 형제 행을 반환 (기본값)
DEPTH	형제 행을 반환하기 전에 자식 행을 반환 (= 계층 쿼리 절)

BREADTH 방식은 너비(breadth)를 기준으로 계층을 탐색한다. 2레벨이 모두 반환된 후 3레벨이 반환된다.

DEPTH 방식은 깊이(depth)를 기준으로 게층을 탐색한다. 계층 쿼리 절과 결과가 동일하다.

16.2.3 루프 처리

재귀 서브 쿼리 팩토링은 루프를 처리하기 위해 CYCLE절을 제공한다. CYCLE 절은 상위 노드에 동일한 c_alias 값이 존재하면 루프가 발생한 것으로 인식한다.

CYCLE c_alias [, c_alias] ... SET cycle_mark_c_alias TO cycle_value DEFAULT no_cycle_value

항목	설명
c_alias [, c_alias]	루프 여부를 확인할 열
cycle_mark_c_alias	루프 여부를 반환할 열
cycle_value	루프가 발생한 경우 반환할 값
no_cycle_value	루프가 발생하지 않은 경우 반환할 값

상위노드(LV=1)에 현재노드(LV=4)의 empno인 7839와 동일한 empno가 존재하기 때문에 루프가 발생한 것으로 인식한다. 계층 쿼리 절과 달리 루프가 발생한 노드까지 반환된다. ic열이 1인 행이 루프가 발생한 것이다.