오라클 START WITH로 계층 쿼리 시작하기

 

---

1. 계층 쿼리란?

오라클 데이터베이스에서 계층 쿼리는 데이터가 계층적 구조를 가질 때 이를 탐색하고 조회하는 데 사용됩니다.
대표적인 예로 조직도, 카테고리 구조, 부모-자식 관계 데이터 등을 들 수 있습니다.
계층 쿼리를 작성하려면 CONNECT BY 절과 함께 START WITH을 사용하여 트리 구조의 시작 지점을 지정해야 합니다.

---

2. 계층 쿼리의 핵심 요소

1) START WITH

• 트리 구조의 시작점을 정의합니다.
• 조건을 설정하여 특정 루트 노드부터 탐색을 시작할 수 있습니다.

2) CONNECT BY

• 부모-자식 관계를 정의합니다.
• 주로 자기 참조 관계를 탐색할 때 사용됩니다.

3) PRIOR

• 부모와 자식 간의 관계를 지정하는 데 사용됩니다.
• PRIOR 키워드는 부모와 자식 간의 방향을 정의합니다.

---

3. 계층 쿼리의 기본 문법

SELECT 열1, 열2, ...
FROM 테이블명
START WITH 조건
CONNECT BY PRIOR 부모_열 = 자식_열;

---

4. 계층 쿼리 활용 예제

예제 1: 조직도 조회

데이터 준비

CREATE TABLE EMPLOYEES (
    EMPLOYEE_ID NUMBER PRIMARY KEY,
    NAME VARCHAR2(50),
    MANAGER_ID NUMBER
);

INSERT INTO EMPLOYEES VALUES (1, 'CEO', NULL);
INSERT INTO EMPLOYEES VALUES (2, 'Manager 1', 1);
INSERT INTO EMPLOYEES VALUES (3, 'Manager 2', 1);
INSERT INTO EMPLOYEES VALUES (4, 'Employee A', 2);
INSERT INTO EMPLOYEES VALUES (5, 'Employee B', 2);
INSERT INTO EMPLOYEES VALUES (6, 'Employee C', 3);
INSERT INTO EMPLOYEES VALUES (7, 'Employee D', 3);

계층 쿼리 작성

SELECT EMPLOYEE_ID, NAME, MANAGER_ID
FROM EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;

결과:

EMPLOYEE_ID NAME MANAGER_ID

1	CEO	NULL
2	Manager 1	1
3	Manager 2	1
4	Employee A	2
5	Employee B	2
6	Employee C	3
7	Employee D	3

---

예제 2: 트리 깊이와 경로 출력

SQL 작성

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

결과:

EMPLOYEE_ID NAME MANAGER_ID LEVEL PATH

1	CEO	NULL	1	> CEO
2	Manager 1	1	2	> CEO > Manager 1
3	Manager 2	1	2	> CEO > Manager 2
4	Employee A	2	3	> CEO > Manager 1 > A
5	Employee B	2	3	> CEO > Manager 1 > B
6	Employee C	3	3	> CEO > Manager 2 > C
7	Employee D	3	3	> CEO > Manager 2 > D

• LEVEL: 현재 노드의 트리 깊이.
• SYS_CONNECT_BY_PATH: 루트 노드부터 현재 노드까지의 경로.

---

예제 3: 특정 노드에서 시작하는 계층 쿼리

SQL 작성

SELECT EMPLOYEE_ID, NAME, MANAGER_ID, LEVEL
FROM EMPLOYEES
START WITH EMPLOYEE_ID = 2
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;

결과:

EMPLOYEE_ID NAME MANAGER_ID LEVEL

2	Manager 1	1	1
4	Employee A	2	2
5	Employee B	2	2

• 분석: EMPLOYEE_ID = 2인 노드부터 시작해 그 하위 계층만 탐색합니다.

---

5. 계층 쿼리 활용 시 유의사항

1. START WITH 조건
   • 시작 지점을 명확히 지정하지 않으면 쿼리가 올바르게 작동하지 않을 수 있습니다.
   • 예를 들어, 부모 노드가 없는 루트부터 시작하려면 START WITH MANAGER_ID IS NULL처럼 조건을 설정합니다.
2. 순환 참조 방지
   • 데이터에 순환 참조(부모-자식 관계가 순환 구조로 연결된 경우)가 있으면 무한 루프에 빠질 수 있습니다.
   • 이를 방지하려면 CONNECT_BY_ISCYCLE 조건을 사용해 순환 참조를 탐지합니다.

   SELECT EMPLOYEE_ID, NAME
   FROM EMPLOYEES
   START WITH MANAGER_ID IS NULL
   CONNECT BY NOCYCLE PRIOR EMPLOYEE_ID = MANAGER_ID;
   

3. 성능 고려
   • 데이터가 많을수록 계층 쿼리는 성능에 영향을 줄 수 있습니다.
   • 인덱스를 적절히 설정하고, 불필요한 데이터 처리를 최소화하세요.

---

6. 마치며

START WITH와 CONNECT BY를 활용한 계층 쿼리는 조직도, 트리 구조 탐색, 경로 추적 등 다양한 데이터 모델에서 유용하게 사용됩니다.
적절한 조건 설정과 데이터 구조를 기반으로 계층 쿼리를 작성하면 복잡한 데이터 관계를 효율적으로 분석할 수 있습니다.
위의 예제를 참고하여 실무에 적합한 계층 쿼리를 설계해보세요!