계층형 질의(Hierarchical Query) - Oracle

계층형 질의(Hierarchical Query)란?

데이터베이스에서 트리 또는 계층적 구조로 조직화된 데이터를 조회하는 SQL 질의이다. 이런 유형의 질의는 부모-자식 관계를 갖는 데이터를 처리할 때 특히 유용하며, 주로 조직 구조, 제품 카테고리, 파일 시스템 등 계층적으로 조직된 데이터를 다루는 데 적합하다.

Oracle 계층형 질의

SELECT 컬럼1, 컬럼2, ...
FROM 테이블
START WITH 조건
CONNECT BY [NOCYCLE] PRIOR 자식/부모컬럼 = 부모/자식컬럼;
[ORDER SIBLINGS BY 컬럼1, 컬럼2, ...]

START WITH

• 계층 구조 전개의 시작 위치를 지정하는 구문이다. 즉, 루트 테이터를 지정한다. (엑세스)

CONNECT BY

• 다음에 전개될 자식 데이터를 지정하는 구문이다. (조인)

PRIOR

• CONNECT BY 절에 사용되며, 현재 읽은 컬럼을 지정한다.
• PRIOR 자식 = 부모 형태를 사용하면 계층구조에서 자식 -> 부모 방향으로 데이터를 전개하는 순방향전개를 한다.
• 반대로 PRIOR 부모 = 자식 형태를 사용하면 계층구조에서 부모 -> 자식 방향으로 데이터를 전개하는 역방향전개를 한다.

NOCYCLE

• 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면 이를 가리켜 사이클(Cycle)이 형성되었다고 한다. 이때 사이클이 발생한 데이터는 런타임 오류가 발생한다. 그렇지만 해당 옵션을 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않는다.

ORDER SIBLINGS BY

• 형제 노드(동일 LEVEL) 사이에서 정렬을 수행한다.

WHERE

• 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출한다. (필터링)

가상 컬럼(Pseudo Column)

SQL 쿼리 내에서 계층 구조 정보를 제공하는 특별한 컬럼들이다. 이 컬럼들은 사용자가 데이터베이스에 직접 저장하지 않고, 계층형 질의(CONNECT BY 절을 사용하는 쿼리) 실행 시 동적으로 생성되어 계층적 관계와 관련된 유용한 정보를 제공한다.

가상 컬럼	설명
LEVEL	현재 레코드가 계층 구조에서 어느 레벨에 위치하는지를 정수 값으로 반환한다. 루트 데이터면 1, 그 하위 데이터면 2 이다. 리프(Leaf) 데이터까지 1씩 증가한다.
CONNECT_BY_ISLEAF	전개 과정에서 해당 데이터가 리프 데이터이면 1, 아니면 0
CONNECT_BY_ISCYCLE	현재 노드가 순환 구조에 포함되어 있는지 여부를 나타낸다. 전개 과정에서 자식을 갖는데, 해당 데이터가 조상으로서 존재하면 1, 아니면 0이다. 이 컬럼을 사용하려면 CONNECT BY 절에 NOCYCLE 옵션이 포함되어 있어야 한다.
CONNECT_BY_ROOT	현재 전개할 데이터의 루트 데이터를 표시한다. 단항 연산자이다.
SYS_CONNECT_BY_PATH	지정된 컬럼에 대한 노드의 경로를 문자열로 반환한다. 이 함수는 경로를 추적할 때 유용하며, 예를 들어 특정 직원에 이르는 직급의 연쇄를 표시할 때 사용할 수 있다.

 

 

예시

SELECT last_name, LEVEL, CONNECT_BY_ISLEAF
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER BY LEVEL, last_name;

[ 실행 결과 ] 

last_name	LEVEL	CONNECT_BY_ISLEAF
---		---		---
Smith		1		0
Johnson		2		0
Williams	2		0
Jones		3		1
Brown		3		1
Davis		3		1

 

 

 

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

[ 실행 결과 ] 

LEVEL		사원		관리자		ISLEAF
---		---		---		---
1 		A 				0
2 	 	 B 		A 		1
2 	 	 C 		A 		0
3 		  D		C 		1
3 		  E		C 		1

 

 

 

SELECT 	LEVEL, LPAD(' ', 4 * (LEVEL-1)) || , 사원 사원,
	관리자, CONNECT_BY_ISLEAF ISLEAF
FROM 사원 START WITH 사원 = 'D'
CONNECT BY PRIOR 사원 = 관리자;

[ 실행 결과 ] 

LEVEL		사원		관리자		ISLEAF
---		---		---		---
1 		D 		C		0
2 	 	 C 		A 		0
3 	 	  A		 		1

 

 

 

SELECT 	CONNECT_BY_ROOT, 사원 루트사원,
	SYS_CONNECT_BY_PATH(사원, '/') 경로, 사원, 관리자
FROM 사원
START WITH IS NULL 관리자
CONNECT BY PRIOR 사원 = 관리자

[ 실행 결과 ] 
루트사원		경로		사원		관리자
---		---		---		---
A		/A		A
A		/A/B		B		A
A		/A/C		C		A
A		/A/C/D		D		C
A		/A/C/E		E		C