ORACLE 계층형 쿼리(Hierarchical Query)란? (CONNECT BY, START WITH, LEVEL, SYS_CONNECT_BY_PATH, PRIOR)

Oracle의 계층형 쿼리는 데이터베이스 테이블에 계층 구조(Hierarchy)가 있는 데이터를 처리할 때 사용됩니다. 예를 들어, 직원 테이블에서 상사와 부하 직원 관계, 제품 카테고리의 상위/하위 관계 등을 계층적으로 표현할 수 있습니다.

Oracle에서는 계층형 쿼리를 작성하기 위해 CONNECT BY 및 START WITH 절을 사용하며, LEVEL, SYS_CONNECT_BY_PATH 같은 함수로 계층 정보를 조작할 수 있습니다.

---

주요 키워드 및 기능

1. CONNECT BY
   • 부모-자식 관계를 정의합니다.
   • 연결 조건을 지정하여 계층 구조를 설정합니다.
2. START WITH
   • 계층 쿼리를 시작할 루트 노드를 정의합니다.
3. LEVEL
   • 계층의 깊이를 나타내는 가상 열로, 루트는 LEVEL=1입니다.
4. SYS_CONNECT_BY_PATH
   • 계층 구조에서 각 노드의 경로를 문자열로 반환합니다.
5. PRIOR
   • 부모-자식 관계를 설정할 때 사용되며, 부모나 자식 중 하나를 명시합니다.

---

간단한 테이블 예시

1. 샘플 테이블: EMPLOYEES

EMPLOYEE_IDEMPLOYEE_NAMEMANAGER_ID

1	CEO	NULL
2	Manager 1	1
3	Manager 2	1
4	Employee 1	2
5	Employee 2	2
6	Employee 3	3

• EMPLOYEE_ID는 직원의 고유 ID입니다.
• MANAGER_ID는 해당 직원의 상사의 ID를 나타냅니다.

---

계층형 쿼리 작성 및 예제

1. 직원 계층 출력

SELECT 
    LEVEL AS HIERARCHY_LEVEL,
    EMPLOYEE_NAME,
    EMPLOYEE_ID,
    MANAGER_ID
FROM 
    EMPLOYEES
START WITH 
    MANAGER_ID IS NULL -- 루트 노드 (CEO)
CONNECT BY 
    PRIOR EMPLOYEE_ID = MANAGER_ID; -- 부모-자식 관계 정의

 

결과:

HIERARCHY_LEVELEMPLOYEE_NAMEEMPLOYEE_IDMANAGER_ID

1	CEO	1	NULL
2	Manager 1	2	1
2	Manager 2	3	1
3	Employee 1	4	2
3	Employee 2	5	2
3	Employee 3	6	3

• LEVEL은 계층의 깊이를 나타냅니다.
• CEO가 루트 노드(LEVEL 1)로, 하위 계층으로 내려갈수록 LEVEL 값이 증가합니다.

---

2. 경로를 포함한 계층 구조

SYS_CONNECT_BY_PATH를 사용하여 각 직원의 경로를 출력합니다.

 

SELECT 
    EMPLOYEE_NAME,
    SYS_CONNECT_BY_PATH(EMPLOYEE_NAME, ' -> ') AS PATH
FROM 
    EMPLOYEES
START WITH 
    MANAGER_ID IS NULL
CONNECT BY 
    PRIOR EMPLOYEE_ID = MANAGER_ID;

 

결과:

EMPLOYEE_NAMEPATH

CEO	-> CEO
Manager 1	-> CEO -> Manager 1
Manager 2	-> CEO -> Manager 2
Employee 1	-> CEO -> Manager 1 -> Employee 1
Employee 2	-> CEO -> Manager 1 -> Employee 2
Employee 3	-> CEO -> Manager 2 -> Employee 3

---

3. 특정 하위 계층만 조회

LEVEL을 사용하여 특정 계층만 필터링할 수 있습니다.

• 예: 2단계 계층(Manager)만 조회.

SELECT 
    EMPLOYEE_NAME,
    LEVEL AS HIERARCHY_LEVEL
FROM 
    EMPLOYEES
START WITH 
    MANAGER_ID IS NULL
CONNECT BY 
    PRIOR EMPLOYEE_ID = MANAGER_ID
WHERE 
    LEVEL = 2; -- 특정 계층 필터링

 

결과:

EMPLOYEE_NAMEHIERARCHY_LEVEL

Manager 1	2
Manager 2	2

---

4. 리프 노드(말단 직원) 조회

리프 노드(하위에 자식이 없는 노드)만 조회하려면 CONNECT_BY_ISLEAF를 사용합니다.

 

SELECT 
    EMPLOYEE_NAME,
    CONNECT_BY_ISLEAF AS IS_LEAF
FROM 
    EMPLOYEES
START WITH 
    MANAGER_ID IS NULL
CONNECT BY 
    PRIOR EMPLOYEE_ID = MANAGER_ID
HAVING 
    CONNECT_BY_ISLEAF = 1; -- 리프 노드만 필터링

 

결과:

EMPLOYEE_NAMEIS_LEAF

Employee 1	1
Employee 2	1
Employee 3	1

---

5. 역순 계층 구조 (부모에서 자식 순서 반대)

SELECT 
    EMPLOYEE_NAME,
    LEVEL
FROM 
    EMPLOYEES
START WITH 
    EMPLOYEE_ID = 4 -- 특정 직원(예: Employee 1)을 기준으로 시작
CONNECT BY 
    PRIOR MANAGER_ID = EMPLOYEE_ID; -- 부모-자식 역순 관계

 

결과:

EMPLOYEE_NAMELEVEL

Employee 1	1
Manager 1	2
CEO	3

---

활용 팁

1. 복잡한 계층 구조 데이터: 직원 조직도, 제품 카테고리, 부서 구조 등을 표현하는 데 유용합니다.
2. 재귀적 데이터 처리: 계층 관계를 따라 올라가거나 내려가는 재귀적 데이터 처리를 효율적으로 수행할 수 있습니다.
3. 시각화 준비: 경로나 깊이를 기반으로 트리 구조를 시각화하는 데 사용할 수 있습니다.

Oracle 계층형 쿼리는 데이터를 트리 구조로 다룰 수 있는 강력한 도구입니다. 실무에서의 다양한 활용 방안을 익히면 더욱 효율적으로 데이터를 처리할 수 있습니다! 😊