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

(기본적으로 계층 쿼리 절을 사용하고, 특별한 루프 처리나 상위 노드에 대한 누적 집계 등이 필요할 경우 선택적으로 재귀 서브 쿼리 팩토링 기능을 사용하는 것이 일반적이다.)

 

[기본 문법]

재귀 서브 쿼리 팩토링은 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인 행을 조회한다.

 

WITH W1 (EMPNO, ENAME, MGR, LV) AS (

SELECT EMPNO, ENAME, MGR, 1 AS LV

FROM EMP

WHERE MGR IS NULL -- START WITH 절

UNION ALL

SELECT C.EMPNO, C.ENAME, C.MGR, P.LV + 1 AS LV

FROM W1 P, EMP C

WHERE C.MGR = P.EMPNO -- CONNECT BY 절

)

SELECT LV, EMPNO, LPAD (' ', LV - 1, ' ') || ENAME AS ENAME, MGR FROM W1;

 

아래 쿼리는 역방향으로 계층을 전개한다.

현재 노드의 EMPNO가 자식 노드의 MGR인 행을 조회한다.

 

WITH W1 (EMPNO, ENAME, MGR, LV) AS (

SELECT EMPNO, ENAME, MGR, 1 AS LV

FROM EMP

WHERE ENAME = 'ADAMS' -- START WITH 절

UNION ALL

SELECT C.EMPNO, C.ENAME, C.MGR, P.LV + 1 AS LV

FROM W1 P, EMP C

WHERE C.EMPNO = P.MGR -- CONNECT BY 절

)

SELECT LV, EMPNO, LPAD(' ', LV - 1, ' ') || ENAME AS ENAME, MGR FROM W1;

 

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

아래 쿼리의 표현식으로 계층 정보를 조회할 수 있다.

 

WITH W1 (EMPNO, ENAME, MGR, LV, EMPNO_P, RT, PT) AS (

SELECT EMPNO, ENAME, MGR

, 1 AS LV -- LEVEL

, NULL AS EMPNO_P -- PRIOR

, ENAME AS RT -- CONNECT_BY_ROOT

, ENAME AS PT -- SYS_CONNECT_BY_PATH

FROM EMP

WHERE MGR IS NULL

UNION ALL

SELECT C.EMPNO, C.ENAME, C.MGR

, P.LV + 1 AS LV -- LEVEL

, P.EMPNO AS EMPNO_P -- PRIOR

, P.RT -- CONNECT_BY_ROOT

, P.RT || ',' || C.ENAME AS PT -- SYS_CONNECT_BY_PATH

FROM W1 P, EMP C

WHERE C.MGR = P.EMPNO)

SEARCH DEPTH FIRST BY EMPNO SET SO

SELECT LV, EMPNO, LPAD (' ', LV - 1, ' ') || ENAME AS ENAME, MGR, EMPNO_P, RT, PT

, CASE WHEN LV - LEAD (LV) OVER (ORDER BY SO) < 0

THEN 0

ELSE 1

END AS LF -- CONNECT_BY_ISLEAF

FROM W1

ORDER BY SO;

 

[계층 정렬]

재귀 서브 쿼리 팩토링은 계층을 정렬하기 위해 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 방식은 너비(BREADTH)를 기준으로 계층을 탐색한다.

2레벨이 모두 반환된 후 3레벨이 반환된다.

 

WITH W1 (EMPNO, ENAME, MGR, LV) AS (

SELECT EMPNO, ENAME, MGR, 1 AS LV

FROM EMP

WHERE MGR IS NULL

UNION ALL

SELECT C.EMPNO, C.ENAME, C.MGR, P.LV + 1 AS LV

FROM W1 P, EMP C

WHERE C.MGR = P.EMPNO)

SEARCH BREADTH FIRST BY EMPNO SET SO

SELECT LV, EMPNO, LPAD (' ', LV - 1, ' ') || ENAME AS ENAME, MGR, SO

FROM W1

ORDER BY SO;

 

아래는 DEPTH 방식을 사용한 쿼리다.

DEPTH 방식은 깊이(DEPTH)를 기준으로 계층을 탐색한다.

계층 쿼리 절과 결과가 동일하다.

 

WITH W1 (EMPNO, ENAME, MGR, LV) AS (

SELECT EMPNO, ENAME, MGR, 1 AS LV

FROM EMP

WHERE MGR IS NULL

UNION ALL

SELECT C.EMPNO, C.ENAME, C.MGR, P.LV + 1 AS LV

FROM W1 P, EMP C

WHERE C.MGR = P.EMPNO)

SEARCH DEPTH FIRST BY EMPNO SET SO

SELECT LV, EMPNO, LPAD (' ', LV - 1, ' ') || ENAME AS ENAME, MGR, SO

FROM W1

ORDER BY SO;

 

[루프 처리]

재귀 서브 쿼리 팩토링은 루프를 처리하기 위해 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 - 루프가 발생하지 않은 경우 반환할 값

 

아래는 CYCLE 절을 사용한 쿼리다. 상위 노드 (LV = 1)에 현재 노드 (LV = 4)의 EMPNO인 7839와 동일한 EMPNO가 존재하기 때문에 루프가 발생한 것으로 인식한다.

계층 쿼리 절과 달리 루프가 발생한 노드까지 반환된다.

IC 열이 1인 행이 루프가 발생한 행이다.

 

WITH W1 (EMPNO, ENAME, MGR, LV) AS (

SELECT EMPNO, ENAME, MGR, 1 AS LV

FROM EMP_L

WHERE EMPNO = 7839

UNION ALL

SELECT C.EMPNO, C.ENAME, C.MGR, P.LV + 1 AS LV

FROM W1 P, EMP_L C

WHERE C.MGR = P.EMPNO)

SEARCH DEPTH FIRST BY EMPNO SET SO

CYCLE EMPNO SET IC TO '1' DEFAULT '0'

SELECT LV, EMPNO, LPAD (' ', LV - 1, ' ') || ENAME AS ENAME, MGR, IC

FROM W1

ORDER BY SO;

 

재귀 서브 쿼리 팩토링은 계층 전개와 무관한 열로도 루프 여부를 확인할 수 있다.

아래 쿼리는 DEPTNO 열로 루프 여부를 확인한다.

SCOTT과 FORD는 상위 노드인 JONES의 DEPTNO가 20이므로 둘 다 루프가 발생한 것으로 인식된다.

 

WITH W1 (EMPNO, ENAME, MGR, DEPTNO, LV) AS (

SELECT EMPNO, ENAME, MGR, DEPTNO, 1 AS LV

FROM EMP

WHERE MGR IS NULL

UNION ALL

SELECT C.EMPNO, C.ENAME, C.MGR, C.DEPTNO, P.LV + 1 AS LV

FROM W1 P, EMP C

WHERE C.MGR = P.EMPNO)

SEARCH DEPTH FIRST BY EMPNO SET SO

CYCLE DEPTNO SET IC TO '1' DEFAULT '0'

SELECT LV, EMPNO, LPAD (' ', LV - 1, ' ') || ENAME AS ENAME, MGR, DPETNO, IC

FROM W1

ORDER BY SO;

 

불친절한 SQL 프로그래밍저자정희락출판디비안(DBian)발매2018.09.10.

 

 

+ Recent posts