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

by

Oracle의 계층형 쿼리(Hierarchical Query)는 계층적 데이터 구조를 표현하거나 탐색할 때 사용됩니다. 일반적으로 부모-자식 관계를 표현한 데이터(예: 조직도, 제품 카테고리, 파일 디렉토리 구조 등)를 처리할 때 유용합니다.

Oracle에서 계층형 쿼리를 작성할 때는 CONNECT BY 절과 START WITH 절을 사용합니다. 이 기능은 재귀적 관계를 탐색하여 트리 구조를 구성합니다.

주요 키워드와 설명

  1. START WITH
    • 계층 구조의 최상위(루트) 노드를 지정합니다.
    • START WITH 절이 없으면 모든 레코드가 루트 노드로 간주됩니다.
  2. CONNECT BY
    • 부모-자식 관계를 지정합니다.
    • 부모 노드와 자식 노드를 연결하는 조건을 정의합니다.
  3. PRIOR
    • CONNECT BY 절에서 사용되며, 이전 단계(부모)의 값을 참조합니다.
    • PRIOR 키워드는 트리 구조의 방향을 결정합니다.
      • PRIOR <부모 컬럼> = <자식 컬럼>: 부모 → 자식
      • <자식 컬럼> = PRIOR <부모 컬럼>: 자식 → 부모
  4. LEVEL
    • 현재 노드의 계층 수준(깊이)을 나타냅니다.
    • 최상위 노드(루트)는 LEVEL = 1입니다.
  5. SYS_CONNECT_BY_PATH
    • 계층 구조를 따라 루트에서 현재 노드까지의 경로를 문자열로 반환합니다.

기본 테이블 예제

CREATE TABLE EMPLOYEE (
    EMP_ID      NUMBER,         -- 직원 ID
    EMP_NAME    VARCHAR2(50),   -- 직원 이름
    MANAGER_ID  NUMBER          -- 상사의 직원 ID
);

INSERT INTO EMPLOYEE VALUES (1, 'CEO', NULL);
INSERT INTO EMPLOYEE VALUES (2, 'Manager1', 1);
INSERT INTO EMPLOYEE VALUES (3, 'Manager2', 1);
INSERT INTO EMPLOYEE VALUES (4, 'Staff1', 2);
INSERT INTO EMPLOYEE VALUES (5, 'Staff2', 2);
INSERT INTO EMPLOYEE VALUES (6, 'Staff3', 3);

위 데이터를 기준으로 조직 구조는 다음과 같습니다:

CEO
 ├── Manager1
 │     ├── Staff1
 │     └── Staff2
 └── Manager2
       └── Staff3

계층형 쿼리 예제

1. 기본 계층형 데이터 조회

SELECT EMP_ID, EMP_NAME, MANAGER_ID, LEVEL
FROM EMPLOYEE
START WITH MANAGER_ID IS NULL -- 루트 노드 지정 (CEO)
CONNECT BY PRIOR EMP_ID = MANAGER_ID; -- 부모와 자식 관계 정의

결과:

EMP_IDEMP_NAMEMANAGER_IDLEVEL
1CEONULL1
2Manager112
3Manager212
4Staff123
5Staff223
6Staff333
  • **LEVEL**은 현재 노드의 깊이를 나타냅니다.

2. 계층적 경로 확인 (SYS_CONNECT_BY_PATH)

SELECT EMP_ID, EMP_NAME, 
       SYS_CONNECT_BY_PATH(EMP_NAME, ' -> ') AS PATH
FROM EMPLOYEE
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMP_ID = MANAGER_ID;

결과:

EMP_IDEMP_NAMEPATH
1CEO-> CEO
2Manager1-> CEO -> Manager1
4Staff1-> CEO -> Manager1 -> Staff1
5Staff2-> CEO -> Manager1 -> Staff2
3Manager2-> CEO -> Manager2
6Staff3-> CEO -> Manager2 -> Staff3
  • **SYS_CONNECT_BY_PATH**는 루트에서 현재 노드까지의 경로를 문자열로 출력합니다.

3. 특정 레벨만 조회

SELECT EMP_ID, EMP_NAME, LEVEL
FROM EMPLOYEE
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMP_ID = MANAGER_ID
HAVING LEVEL = 3; -- 3단계 레벨의 데이터만 조회

결과:

EMP_IDEMP_NAMELEVEL
4Staff13
5Staff23
6Staff33

4. 역방향 탐색(자식 → 부모)

SELECT EMP_ID, EMP_NAME, MANAGER_ID, LEVEL
FROM EMPLOYEE
START WITH EMP_ID = 6 -- Staff3에서 시작
CONNECT BY MANAGER_ID = PRIOR EMP_ID; -- 자식 → 부모 관계 정의

결과:

EMP_IDEMP_NAMEMANAGER_IDLEVEL
6Staff331
3Manager212
1CEONULL3
  • **CONNECT BY**에서 자식 → 부모 관계를 정의하여 역방향으로 탐색합니다.

5. 루프 방지 (NOCYCLE)

계층형 데이터에 잘못된 관계로 인해 무한 루프가 발생할 수 있습니다. 이를 방지하기 위해 NOCYCLE 옵션을 사용할 수 있습니다.

SELECT EMP_ID, EMP_NAME, LEVEL
FROM EMPLOYEE
START WITH MANAGER_ID IS NULL
CONNECT BY NOCYCLE PRIOR EMP_ID = MANAGER_ID;
  • NOCYCLE을 사용하면 무한 루프가 발생하지 않도록 보호합니다.

실무에서의 활용 예시

1. 조직도 조회

회사 조직도의 부서와 직원을 계층적으로 조회합니다.

SELECT EMP_NAME, LEVEL, SYS_CONNECT_BY_PATH(EMP_NAME, ' -> ') AS ORG_PATH
FROM EMPLOYEE
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMP_ID = MANAGER_ID;

2. 카테고리 트리

전자상거래 사이트에서 상품 카테고리를 트리 구조로 표시합니다.

SELECT CATEGORY_ID, CATEGORY_NAME, LEVEL, SYS_CONNECT_BY_PATH(CATEGORY_NAME, ' > ') AS CATEGORY_PATH
FROM PRODUCT_CATEGORIES
START WITH PARENT_CATEGORY_ID IS NULL
CONNECT BY PRIOR CATEGORY_ID = PARENT_CATEGORY_ID;

3. 파일 디렉토리 구조

파일 시스템에서 디렉토리 구조를 조회합니다.

SELECT FILE_ID, FILE_NAME, SYS_CONNECT_BY_PATH(FILE_NAME, '/') AS FILE_PATH
FROM FILE_SYSTEM
START WITH PARENT_FILE_ID IS NULL
CONNECT BY PRIOR FILE_ID = PARENT_FILE_ID;

결론

Oracle의 계층형 쿼리는 부모-자식 관계를 기반으로 데이터를 탐색하고 표시하는 데 매우 강력합니다. 조직도, 카테고리 트리, 디렉토리 구조와 같은 실무 시나리오에서 활용도가 높으며, START WITH, CONNECT BY, LEVEL 등의 키워드를 적절히 조합하면 복잡한 계층 구조를 쉽게 처리할 수 있습니다.

Leave a Comment