16. 계층쿼리

계층 쿼리를 사용하면 순환 관계를 가진 데이터를 조회할 수 있다. 순환 관계는 노드와 노드의 관계를 통해 만들어진다. 일반적으로 하나의 부모 노드는 다수의 자식 노드를 가질 수 있고, 하나의 자식 노드는 하나의 부모 노드만 가질 수 있다.

노드는 부모 노드와 자식 노드의 존재 여부에 따라 루트 노드, 브랜치 노드, 리프 노드로 구분할 수 있다.

순환 관계는 계층의 깊이에 따라 레벨이 부여 된다. 루트 노드는 레벨이 1이고, 계층이 전개될수록 레벨이 1씩 증가한다.

-- JONES의 자식 노드 조회
SELECT b.empno, b.ename, b.mgr
FROM emp a, emp b
WHERE a.ename = 'JONES'
AND b.mgr = a.empno;

-- JONES의 자식 노드의 자식 노드 조회
SELECT c.empno, c.ename, c.mgr
FROM emp a, emp b, emp c
WHERE a. ename = 'JONES'
AND b.mgr = a.empno
AND c.mgr = b.empno;

-- SMITH의 부모 도느 조회
SELECT b.empno, b.ename, b.mgr
FROM emp a, emp b
WHERE a.ename = 'SMITH'
AND b.empno = a.mgr;

위에서는 깊은 레벨의 노드를 조회하기 위해서 셀프 조인을 반복해야 했다. 오라클에서는 순환 관계를 가진 데이터를 조회할 수 있는 계층 쿼리 절과 재귀 서브 쿼리 팩토링 기능을 제공한다.

16.1 계층 쿼리 절

계층 쿼리 절은 오라클의 전통적인 계층 쿼리 구문이다.

16.1.1 기본 문법

계층 쿼리 절은 WHERE 절 다음에 기술하며, FROM 절이 수행된 후 수행된다. START WITH 절과 CONNECT BY 절로 구성되며, START WITH 절은 생략 가능하다.

• PRIOR : 직전 상위 노드의 값을 반환
• CONNECT_BY_ROOT : 루트 노드의 값을 반환
• LEVEL : 현재 레벨을 반환
• CONNECT_BY_ISLEAF : 리프 노드인 경우 1, 아니면 0을 반환
• CONNECT_BY_ISCYCLE : 루프가 발생한 경우 1, 아니면 0을 반환
• SYS_CONNECT_BY_PATH : 루트 노드에서 현재 노드까지의 column을 char로 구분하여 연결한 값을 반환 column 값에 char가 포함되어 있으면 에러 발생

SELECT LEVEL AS lv, empno, LPAD(' ', LEVEL - 1, ' ') || ename AS ename, mgr, PRIOR empno AS empno_p
FROM emp
START WITH mgr IS NULL  --mgr가 존재하지 않는 행
CONNECT BY mgr = PRIOR empno -- mgr가 부모 노드의 empno인 행

SELECT LEVER AS lv, empno, LPAD(' ', LEVEL - 1, ' ') || ename AS ename, mgr
        , CONNECT_BY_ROOT ename AS rt
        , CONNECT_BY_ISLEAF AS lf
        , SYS_CONNECT_BY_PATH(ename, ',') AS pt
FROM emp
START WITH mgr IS NULL
CONNECT BY mgr = PRIOR empno

16.1.2 동작 원리

계층 쿼리 절은 START WITH 절로 루트 노드를 생성한 후, 결과가 없을 때까지 CONNECT 절을 반복 수행하여 하위 노드를 생성한다.

1. (LEVEL=1) : START WITH 절을 수행하여 루트 노드를 생성한다. > 1단계 수행 결과를 임시테이블에 저장한다.
2. (LEVEL=2) : CONNECT BY 절로 테이블에 저장된 1단계 결과와 emp 테이블을 조인한다.
3. (LEVEL=3) : CONNECT BY 절로 임시 테이블에 저장된 2단계 결과와 emp 테이블을 조인한다. > 수행 결과를 임시테이블에 저장한다.
4. (LEVEL=4) : CONNECT BY 절로 임시 테이블에 저장된 3단계 결과와 emp 테이블을 조인한다.
5. (LEVEL=5) : CONNECT BY 절로 임시 테이블에 저장된 4단계 결과와 emp 테이블을 조인한다.