계층형 쿼리(Hierarchical Query) 개념 및 실행 순서 상세 정리

Oracle 계층형 쿼리(Hierarchical Query)란?

테이블에 계층적인 순서로 표현해야 하는 데이터가 존재하는 경우 데이터를 조회하기 위해서 계층형 질의(Hierarchical Query)를 사용한다. 계층형 데이터란 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말한다. 예를 들어, 사원 테이블에서는 사원들 사이에 상위 사원(관리자 or 직속상사)과 하위 사원 관계가 존재하고 조직 테이블에서는 조직들 사이에 상위 조직과 하위 조직 관계가 존재한다. 엔티티를 순환관계 데이터 모델로 설계할 경우 계층형 데이터가 발생한다. 순환관계 데이터 모델의 예로는 조직, 사원, 메뉴 등이 있다.

그리고 이러한 계층형 데이터를 기본적인 SQL문을 사용하여 계층 관계를 표현하는것은 불가능하며, 재귀 PL/SQL 루틴을 사용해야만 표현이 가능하다. 하지만 재귀 PL/SQL은 개발과 처리 과정에서 다소 많은 시간이 필요로 한다는 단점이 있으며, 변경사항이 있을 때 다른 저장 프로시저를 만들거나 보다 복잡하게 변경해야 한다는 점도 무시할 수 없다. 이에 오라클에서는 connect by라는 확장된 Select 구문을 지원한다.

 

 

계층형 쿼리(Hierarchical Query) 수행 순서

실행 순서	Query 문장	상세 내용
6	select [Alias명]	*기본 SELECT문의 실행 순서는 'SELECT문장 순서'를 클릭하여 확인!
1	From 테이블명
5	Where 조건절	모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터를 필터링한다.
2	START WITH 조건식	계층 구조 전개의 시작 위치(루트)를 지정하는 구문.
3	CONNECT BY [PRIOR, NOCYCLE]  조건식 AND 조건식	다음에 전개될 자식 데이터를 지정. 자식 데이터는 CONNECT BY에 주어진 조건을 만족해야 함. *prior키워드: 바로 직전에 출력된 레코드(행)를 의미. ①PRIOR 자식 = 부모 :  계층 구조가 부모 데이터에서 자식 데이터(부모 → 자식), 방향으로 전개되는 순방향 전개를 함. ②PRIOR 부모 = 자식 :  계층 구조가 자식 데이터에서 부모 데이터(자식 → 부모), 방향으로 전개되는 역방향 전개를 함. ③NOCYCLE : 데이터를 전개하면서 이미 나타났던 동일한 데이터가 다시 나타난다면 이것을 가리켜 사이클(Cycle)이 형성되었다고 한다. 사이클이 발생하면 런타임 오류가 발생함. 하지만 NOCYCLE을 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않는다.
4	Order siblings by 칼럼명, 칼럼명, ….	형제 노드(동일한 LEVEL) 사이에서 정렬을 수행함.

 

 

계층형 쿼리 사용 시, Oracle에서 제공되는 가상 칼럼(Pseudo Column) 및 함수

아래 표는 Oracle에서 제공되며, 계층형 질의 사용 시 유용한 가상칼럼(LEVEL 등) 및 함수들이다.

구분	Query 문장	실행 순서
가상 칼럼	LEVEL	Root데이터이면 1, 그 하위 데이터이면 2. Leaf데이터까지 1씩 증가.
	CONNECT_BY_ISREAF 칼럼명	전개 과정에서 해당 데이터가 Leaf데이터이면 1, 그렇지 않으면 0.
	CONNECT_BY_ISCYCLE 칼럼명	전개 과정에서 자식을 갖는데, 해당 데이터가 조상으로서도 존재하면 1, 아니면 0. CYCLE옵션을 사용할 때만 사용 가능하다.
함수	SYS_CONNECY_BY_PATH (칼럼명, 경로분리자)	루트데이터부터 현재 전개할 데이터까지의 경로를 표시한다.
	CONNECT_BY_ROOT	현재 전개할 데이터의 루트 데이터를 표시.

 

 

계층형 쿼리와 PRIOR 해석 & 사용법

SELECT LEVEL, LPAD(' ', 4 * (LEVEL-1)) || 사원 사원, 직속상사, CONNECT_BY_ISLEAF ISLEAF 
FROM 사원 
START WITH 직속상사 IS NULL
CONNECT BY PRIOR 사원 = 직속상사;

connect by 절은 각 행이 어떻게 연결되는지를 Oracle에게 알려주는 역할을 한다. 즉 계층 구조 내에서 각 행의 관계를 설정하는 것이다. 현재 행과 다른 행은 PRIOR라는 키워드를 통해 구별된다. Prior키워드는 상위 행(바로 직전에 출력된 행)을 참조하는 키워드로, 위 예제에서는 다음과 같이 사용되었다. CONNECT BY PRIOR 사원 = 직속상사; 이는 "방금 전 행의 사원 값이 현재 행의 직속상사 값인 행을 모두 찾아라!"라는 의미이다. 쉽게 말하면, 방금전에 살펴본 사원이 현재 사원의 상사가 되는 방식으로 출력하라는 것이다. 추가로 CONNECT BY 사원 = PRIOR 직속상사;(== CONNECT BY PRIOR 직속상사 = 사원;) 와 같이 PRIOR키워드를 "=" 기호를 사이에 두고 반대편으로 옮길 경우, 결과는 Tree를 거슬러 내려가는 것(순방향 전개)이 아니라, 반대의 의미인 거슬러 올라가는 방식(역방향 전개)으로 리턴된다.

순방향 전개

위 그림은 LEVEL칼럼이 사용된 순방향 계층형 질의에 대한 논리적인 실행 모습이다. 그림을 보면 순방향 전개 시, A는 루트 데이터이기 때문에 레벨이 1이다. A의 하위 데이터인 B, C는 레벨이 2이다. 그리고 C의 하위 데이터인 D, E는 레벨이 3이다. 리프 데이터는 B, D, E가 된다.

역방향 전개

반대로 역방향 계층형 전개 시에는 D가 루트 데이터로 레벨 1이 되며, 상위 레벨의 데이터가 역순의 레벨을 갖는다.