Oracle 계층적 쿼리 (Hierarchical Query)

 

Oracle에서는 CONNECT BY 절을 사용하여 계층적 데이터를 조회할 수 있다. 특히, 조직도나 제품 카테고리처럼 부모-자식 관계를 나타내는 데이터를 다룰 때 유용하다. 이번 글에서는 CONNECT BY 절을 활용하여 계층 구조를 형성하는 방법을 예제와 함께 정리하겠다.

1. 계층적 쿼리의 기본 개념

Oracle의 계층적 쿼리는 START WITH 절과 CONNECT BY 절을 조합하여 부모-자식 관계를 탐색하는 방식으로 동작한다.

• START WITH: 계층 구조에서 루트(시작) 노드를 지정
• CONNECT BY: 부모-자식 관계를 정의하는 조건을 설정

2. 예제 테이블과 데이터

다음과 같은 테이블(SQLD_11)이 있다고 가정한다.

COL1	COL2	COL3
A	NULL	1
B	A	2
C	A	3
D	B	4

여기서 COL1은 현재 노드를, COL2는 부모 노드를 의미한다. COL3은 계층 구조와는 직접적인 관련이 없는 값이다.

3. 계층적 쿼리 실행

다음 SQL을 실행하면 COL3 = 4를 가진 행을 시작점으로 하여 계층적으로 부모를 찾아 올라가는 데이터를 조회할 수 있다.

SELECT *
FROM SQLD_11
START WITH COL3 = 4
CONNECT BY COL1 = PRIOR COL2;

실행 과정 분석

1. START WITH COL3 = 4
   • COL3 = 4인 행을 찾는다.
   • 해당하는 행은 (D, B, 4)이므로 D가 루트 노드가 된다.
2. CONNECT BY COL1 = PRIOR COL2
   • COL1 값이 이전(부모) 행의 COL2 값과 일치하는 데이터를 찾는다.
   • D의 COL2 = B이므로 COL1 = B인 행을 찾는다 → (B, A, 2)
   • B의 COL2 = A이므로 COL1 = A인 행을 찾는다 → (A, NULL, 1)

계층 구조 트리

위의 과정을 거치면 다음과 같은 계층 구조가 형성된다.

       (A)
        │
       (B)
        │
       (D)

4. 결과 테이블

쿼리 실행 결과는 다음과 같다.

COL1 COL2 COL3

D	B	4
B	A	2
A	NULL	1

이처럼 COL3 = 4를 가진 D를 기준으로 B → A 순서로 연결되며 부모-자식 관계를 따라가며 데이터를 조회할 수 있다.

5. 추가적인 활용

5.1 LEVEL을 사용한 계층 깊이 출력

SELECT LEVEL, COL1, COL2, COL3
FROM SQLD_11
START WITH COL3 = 4
CONNECT BY COL1 = PRIOR COL2;

• LEVEL을 사용하면 각 행이 트리 구조에서 몇 번째 계층(level)에 속하는지 확인할 수 있다.

5.2 SYS_CONNECT_BY_PATH를 사용한 계층 경로 출력

SELECT COL1, SYS_CONNECT_BY_PATH(COL1, ' -> ') AS PATH
FROM SQLD_11
START WITH COL3 = 4
CONNECT BY COL1 = PRIOR COL2;

• SYS_CONNECT_BY_PATH 함수를 사용하면 계층 구조를 문자열 형태로 쉽게 출력할 수 있다.

6. 마무리

Oracle의 CONNECT BY 절을 활용하면 계층적 데이터를 손쉽게 조회할 수 있다. COL1 = PRIOR COL2라는 관계를 이해하면 조직도, 카테고리 구조, BOM(Bill of Materials) 등의 데이터 분석에 효과적으로 활용할 수 있다.