[불친절한 SQL 프로그래밍] [16장] 계층 쿼리(1) (START WITH, CONNECT BY)

16.1 계층 쿼리 절

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

 

16.1.1 기본 문법

계층 쿼리 절은 WHERE 절 다음에 기술하며, FROM절이 수행된 후 수행된다. 

START WITH절과 CONNECT BY 절로 구성되며, START WITH 절이 수행된 후 CONNECT BY 절이 수행된다.

START WITH절은 생략이 가능하다.

[START WITH condition] CONNECT BY [NOCYCLE] condition

절	설명
START WITH 절	루트 노드를 생성하며 1번만 수행
CONNECT BY 절	루트 노드의 하위 노드를 생성하며 조회 결과가 없을 때까지 반복 수행

유형	항목	설명
연산자	PRIOR	직전 상위 노드의 값을 반환
	CONNECT_BY_ROOT	루트 노드의 값을 반환
슈도 칼럼	LEVEL	현재 레벨을 반환
	CONNECT_BY_ISLEAF	리프 노드인 경우 1, 아니면 0을 반환
	CONNECT_BY_ISCYCLE	루프가 발생한 경우 1, 아니면 0을 반환
함수	SYS_CONNECT_BY_PATH	루트 노드에서 현재 노드까지의 경로를 반환

START WITH 절로 mgr이 존재하지 않는 행을 조회하고, CONNECT BY절로 현재 노드의 mgr이 직전 상위 노드의 empno인 행을 반복해서 조회한다. ename 열은 LEVEL 슈도 칼럼과 LPAD 함수를 사용하여 계층의 레벨에 따라 값을 들여쓰기했다.

 

 

16.1.2 동작 원리

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

 

1단계 (LEVEL = 1)

START WITH절을 수행하여 루트 노드를 생성한다. START WITH절은 WHERE절과 유사하게 동작한다.

여기서 WHERE 절이 START WITH mgr IS NULL과 유사하게 동작

다음 예제를 위해 1단계 수행결과를 임시 테이블에 저장

 

2단계 (LEVE = 2)

CONNECT BY절로 임시 테이블에 저장된 1단계 결과와 emp테이블을 조인한다. CONNECT BY절도 WHERE절과 유사하게 동작한다.

WHERE절의 c.mgr = p.empno 가 CONNECT BY mgr = PRIOR empno와 유사하게 동작한다.

다음 예제를 위해 2단계 수행결과를 임시 테이블에 저장

 

3단계 (LEVEL = 3)

CONNECT BY 절로 임시 테이블에 저장된 2단계 결과와 emp 테이블을 조인한다.

다음 예제를 위해 3단계 수행 결과를 임시 테이블에 저장

 

4단계 (LEVEL = 4)

CONNECT BY 절로 임시 테이블에 저장된 3단계 결과와 emp 테이블을 조인한다.

다음 예제를 위해 4단계 수행 결과를 임시 테이블에 저장

 

5단계 (LEVEL = 5)

CONNECT BY 절로 임시테이블에 저장된 4단계 결과와 emp 테이블을 조인한다.

결과가 반환되지 않으면 CONNECT BY 절의 수행을 멈추고 수행 결과가 저장된 임시 테이블을 조회하여 결과를 반환한다.

 

16.1.3 전개 방향

순환 관계는 순방향 또는 역방향으로 전개할 수 있다. 순방향 전개와 역방향 전개는 데이터 모델 상의 전개 방향이 반대일 뿐 동작 원리는 동일하다.

	전개 방향	START WITH 절	CONNECT BY 절
순방향 전개	부모 -> 자식	부모 노드 조회	PK에 PRIOR 기술
역방향 전개	자식 -> 부모	자식 노드 조회	FK에 PRIOR 기술

순방향 전개는 START WITH 절로 부모 노드를 조회하고, CONNECT BY 절을 통해 자식 노드로 계층을 전개한다. PK(empno)에 PRIOR 연산자를 기술하여 현재 노드의 mgr가 부모 노드의 empno인 행을 조회한다.

순방향으로 계층 전개. 

 

역방향 전개는 START WITH절로 자식 노드를 조회하고, CONNECT BY 절을 통해 부모 노드로 계층을 전개한다. FK(mgr)에 PRIOR 연산자를 기술하여 현재 노드의 empno가 자식 노드의 mgr인 행을 조회한다.

역방향으로 계층 전개.

 

16.1.4 계층 정렬

계층 쿼리 절은 형제 노드의 행을 정렬하기 위해 SIBLINGS 키워드를 제공한다.

 

계층 쿼리 절에 ORDER BY 절을 사용하면 계층 구조와 무관하게 행이 정렬된다.

ORDER BY 절에 SIBLINGS 키워드를 사용하면 형제 노드 내에서만 행이 정렬되기 때문에 계층 구조를 유지한 상태로 행을 정렬할 수 있다. 

CLARK, BLAKE, JONES 는 형제 노드다. sal 순서로 행이 정렬된다. 

BLAKE의 하위 노드인 JAMES ~ ALLEN도 sal 순서로 행이 정렬된다.

 

16.1.5 루프 처리

부모 노드가 현재 노드의 자식 노드로 연결되면 루프(loop)가 발생한다. 계층 쿼리 절은 루프를 처리하기 위해 NOCYCLE 키워드와 CONNECT_BY_ISCYCLE 슈도 칼럼을 제공한다.

 

CONNECT BY 절에 NOCYCLE 키워드를 기술하면 루프가 발생한 노드를 전개하지 않는다.