[DB] Oracle 계층 쿼리 사용법 - START WITH CONNECT BY PRIOR 실전 예제

Oracle 계층 쿼리 사용법 - START WITH CONNECT BY PRIOR 실전 예제

Oracle 데이터베이스에서 조직도, 카테고리, 댓글과 대댓글 같은 트리 구조의 데이터를 조회해야 할 때가 있습니다. 이럴 때 사용하는 것이 바로 START WITH CONNECT BY PRIOR 구문입니다.

이 기능은 Oracle에서 제공하는 강력한 계층형 쿼리 도구로, 부모-자식 관계로 연결된 데이터를 한 번의 쿼리로 효과적으로 조회할 수 있게 해줍니다. 계층형 쿼리를 처음 접하는 분들도 쉽게 이해할 수 있도록 기초 개념부터 실전 예제, 성능 최적화 팁까지 상세히 설명드리겠습니다.

목차

1. 계층 쿼리란 무엇인가?

2. 기본 문법과 핵심 키워드

3. 실전 활용 예제

4. 성능 최적화 방법

5. 자주 발생하는 오류 해결법

---

#1. 계층 쿼리란 무엇인가?

Oracle의 계층형 쿼리(Hierarchical Query)는 트리 구조로 저장된 데이터를 효율적으로 조회하는 SQL 기능입니다. 일반적인 SELECT 문으로는 부모-자식 관계를 한 번에 조회하기 어렵지만, 계층 쿼리를 사용하면 간단하게 처리할 수 있습니다.

1) 계층 쿼리가 필요한 경우

실무에서 계층 쿼리를 활용하는 대표적인 사례는 다음과 같습니다.

(1) 조직도 구조

회사의 조직도는 대표이사부터 시작하여 부서장, 팀장, 팀원으로 이어지는 계층 구조를 가지고 있습니다. 각 직원은 상위 관리자를 가지며, 이러한 관계를 데이터베이스에 저장할 때 계층 쿼리가 필수적입니다.

① 대표이사 → 부사장 → 부서장 → 팀장 → 팀원

② 각 직원의 직속 상사와 전체 상위 관리자 조회

③ 특정 부서의 전체 조직 구성원 파악

(2) 제품 카테고리 분류

쇼핑몰의 제품 카테고리는 대분류, 중분류, 소분류로 나뉘며, 각 카테고리는 상위 카테고리를 참조하는 구조입니다.

① 전자제품 → 컴퓨터 → 노트북

② 의류 → 남성의류 → 정장

③ 특정 카테고리의 모든 하위 카테고리 조회

(3) 게시판 댓글 시스템

게시판의 댓글과 대댓글은 무한 깊이의 계층 구조를 가질 수 있습니다. 원댓글에 대한 답글, 그 답글에 대한 또 다른 답글이 연결되는 형태입니다.

. . . . .

2) 계층 쿼리의 장점

장점	설명
쿼리 간소화	재귀 호출 없이 한 번의 SELECT 문으로 모든 계층 데이터 조회 가능
성능 향상	다중 조인이나 서브쿼리 대신 Oracle의 내장 기능 활용으로 처리 속도 개선
가독성 개선	트리 구조를 직관적으로 표현하여 코드 이해도 향상
유지보수 용이	복잡한 로직 없이 SQL로 처리하여 관리 부담 감소

---

#2. 기본 문법과 핵심 키워드

계층 쿼리의 기본 문법을 이해하면 다양한 상황에 응용할 수 있습니다. 핵심 구성 요소를 하나씩 살펴보겠습니다.

1) 기본 구문 구조

SELECT [열 목록]
FROM [테이블명]
WHERE [조건식]
START WITH [시작 조건]
CONNECT BY PRIOR [연결 조건]
ORDER SIBLINGS BY [정렬 조건];

각 절의 역할을 자세히 알아보겠습니다.

(1) START WITH 절

START WITH 절은 계층 구조의 시작점(루트 노드)을 지정합니다. 조직도에서는 대표이사, 카테고리에서는 최상위 카테고리가 시작점이 됩니다.

-- 관리자가 없는 최상위 직원부터 시작
START WITH 관리자ID IS NULL

-- 특정 사원을 루트로 지정
START WITH 사원ID = 100

(2) CONNECT BY PRIOR 절

CONNECT BY PRIOR 절은 부모와 자식 간의 연결 관계를 정의합니다. PRIOR 키워드의 위치에 따라 조회 방향이 결정됩니다.

-- 하향식(Top-down): 부모 → 자식
CONNECT BY PRIOR 자식키 = 부모키

-- 상향식(Bottom-up): 자식 → 부모
CONNECT BY PRIOR 부모키 = 자식키

예를 들어, 조직도에서 특정 직원의 모든 하위 직원을 조회하려면 하향식을, 특정 직원의 상위 관리자들을 조회하려면 상향식을 사용합니다.

(3) ORDER SIBLINGS BY 절

ORDER SIBLINGS BY 절은 같은 레벨(형제 노드)의 데이터를 정렬합니다. 일반 ORDER BY와 달리 계층 구조를 유지한 채 정렬합니다.

-- 같은 레벨 내에서 사원명으로 정렬
ORDER SIBLINGS BY 사원명

. . . . .

2) 주요 함수와 키워드

계층 쿼리에서 유용하게 활용할 수 있는 내장 함수와 가상 열(Pseudocolumn)을 소개합니다.

함수/키워드	설명	활용 예시
LEVEL	현재 행의 계층 레벨 반환 (루트=1)	들여쓰기, 깊이 제한
SYS_CONNECT_BY_PATH	루트부터 현재 노드까지 경로 문자열 생성	카테고리 전체 경로 표시
CONNECT_BY_ROOT	현재 행의 루트 노드 값 반환	최상위 조직 정보 표시
CONNECT_BY_ISLEAF	현재 행이 리프 노드인지 여부 (1=리프, 0=중간 노드)	말단 직원/카테고리 구분
PRIOR	부모 행의 값 참조	부모 정보 조회

(1) LEVEL 활용 예제

LEVEL 값을 이용하면 계층 구조를 시각적으로 표현할 수 있습니다.

SELECT
    LPAD(' ', 4 * (LEVEL-1)) || 사원명 AS 조직도,
    직위,
    LEVEL AS 레벨
FROM 직원
START WITH 관리자ID IS NULL
CONNECT BY PRIOR 사원ID = 관리자ID;

위 쿼리는 LPAD 함수로 레벨에 따라 공백을 추가하여 들여쓰기 효과를 만듭니다. 결과는 다음과 같이 출력됩니다.

조직도            직위        레벨
김대표            대표이사    1
    이부장        부장        2
        박팀장      팀장        3
            최사원    사원        4

(2) SYS_CONNECT_BY_PATH 활용

이 함수는 루트부터 현재 노드까지의 전체 경로를 구분자로 연결하여 보여줍니다.

SELECT
    카테고리명,
    SYS_CONNECT_BY_PATH(카테고리명, ' > ') AS 전체경로
FROM 카테고리
START WITH 상위카테고리ID IS NULL
CONNECT BY PRIOR 카테고리ID = 상위카테고리ID;

결과 예시:

전체경로
-------------------------
> 전자제품
> 전자제품 > 컴퓨터
> 전자제품 > 컴퓨터 > 노트북
> 전자제품 > 컴퓨터 > 데스크탑

---

#3. 실전 활용 예제

실무에서 자주 사용하는 패턴을 중심으로 실전 예제를 살펴보겠습니다.

1) 조직도 전체 조회

회사의 전체 조직 구조를 계층적으로 표시하는 쿼리입니다.

SELECT
    LPAD(' ', 4 * (LEVEL-1)) || 사원명 AS 조직도,
    사원ID,
    관리자ID,
    직위,
    부서명,
    LEVEL AS 레벨
FROM 직원
START WITH 관리자ID IS NULL
CONNECT BY PRIOR 사원ID = 관리자ID
ORDER SIBLINGS BY 사원명;

. . . . .

2) 특정 직원의 하위 조직 조회

특정 관리자 아래의 모든 하위 직원을 조회하는 쿼리입니다.

SELECT
    LPAD(' ', 4 * (LEVEL-1)) || 사원명 AS 조직도,
    직위,
    CONNECT_BY_ROOT 사원명 AS 최상위관리자,
    LEVEL AS 레벨
FROM 직원
START WITH 사원ID = 100 -- 특정 관리자 ID
CONNECT BY PRIOR 사원ID = 관리자ID;

CONNECT_BY_ROOT 함수로 최상위 관리자 정보를 함께 표시할 수 있습니다.

. . . . .

3) 카테고리 계층 구조와 상품 개수

쇼핑몰의 카테고리 구조와 함께 각 카테고리별 상품 개수를 표시하는 쿼리입니다.

SELECT
    LPAD(' ', 2 * (LEVEL-1)) || C.카테고리명 AS 카테고리구조,
    SYS_CONNECT_BY_PATH(C.카테고리명, ' > ') AS 전체경로,
    COUNT(P.상품ID) AS 상품수,
    LEVEL AS 레벨,
    CONNECT_BY_ISLEAF AS 말단여부
FROM 카테고리 C
LEFT JOIN 상품 P ON C.카테고리ID = P.카테고리ID
START WITH C.상위카테고리ID IS NULL
CONNECT BY PRIOR C.카테고리ID = C.상위카테고리ID
GROUP BY
    LEVEL,
    C.카테고리ID,
    C.카테고리명,
    SYS_CONNECT_BY_PATH(C.카테고리명, ' > '),
    CONNECT_BY_ISLEAF
ORDER SIBLINGS BY C.카테고리명;

. . . . .

4) 댓글과 대댓글 표시

게시판의 댓글 계층 구조를 표시하는 쿼리입니다.

SELECT
    댓글ID,
    LPAD(' ', 3 * (LEVEL-1)) || '└ ' || 내용 AS 댓글내용,
    작성자,
    작성일자,
    LEVEL AS 댓글레벨
FROM 댓글
WHERE 게시글ID = 1001 -- 특정 게시글
START WITH 상위댓글ID IS NULL
CONNECT BY PRIOR 댓글ID = 상위댓글ID
ORDER SIBLINGS BY 작성일자;

결과는 다음과 같이 들여쓰기되어 표시됩니다.

댓글내용
----------------------------
좋은 글 감사합니다.
   └ 도움이 되셨다니 기쁩니다.
      └ 추가 질문 있습니다.
질문이 있는데요...
   └ 답변드립니다.

. . . . .

5) 특정 깊이까지만 조회

너무 깊은 계층을 방지하기 위해 특정 레벨까지만 조회하는 쿼리입니다.

SELECT
    LPAD(' ', 4 * (LEVEL-1)) || 사원명 AS 조직도,
    직위,
    LEVEL AS 레벨
FROM 직원
WHERE LEVEL <= 3 -- 3단계까지만 조회
START WITH 관리자ID IS NULL
CONNECT BY PRIOR 사원ID = 관리자ID
ORDER SIBLINGS BY 사원명;

---

#4. 성능 최적화 방법

계층 쿼리는 데이터가 많거나 깊이가 깊어질수록 성능 저하가 발생할 수 있습니다. 효과적인 최적화 방법을 알아보겠습니다.

1) 인덱스 활용

계층 쿼리의 성능을 좌우하는 가장 중요한 요소는 인덱스입니다. CONNECT BY 절에서 사용하는 열에 인덱스를 생성해야 합니다.

(1) 필수 인덱스

-- 자식 → 부모 연결 컬럼에 인덱스
CREATE INDEX idx_emp_manager ON 직원(관리자ID);

-- 부모 → 자식 연결 컬럼에 인덱스
CREATE INDEX idx_emp_id ON 직원(사원ID);

(2) 복합 인덱스 활용

WHERE 조건이나 ORDER SIBLINGS BY와 함께 사용되는 경우 복합 인덱스가 효과적입니다.

-- 관리자ID + 사원명 복합 인덱스
CREATE INDEX idx_emp_mgr_name ON 직원(관리자ID, 사원명);

. . . . .

2) WHERE 절 활용

불필요한 데이터를 사전에 필터링하면 처리 범위가 줄어들어 성능이 개선됩니다.

SELECT
    LPAD(' ', 4 * (LEVEL-1)) || 사원명 AS 조직도,
    직위
FROM 직원
WHERE 퇴사일자 IS NULL -- 재직 중인 직원만
  AND 부서코드 = 'IT' -- 특정 부서만
START WITH 관리자ID IS NULL
CONNECT BY PRIOR 사원ID = 관리자ID;

. . . . .

3) NOCYCLE 키워드 사용

순환 참조가 발생할 수 있는 데이터에서는 NOCYCLE 키워드를 사용하여 무한 루프를 방지해야 합니다.

SELECT
    사원명,
    관리자ID,
    CONNECT_BY_ISCYCLE AS 순환여부 -- 순환 발생 시 1
FROM 직원
START WITH 관리자ID IS NULL
CONNECT BY NOCYCLE PRIOR 사원ID = 관리자ID;

. . . . .

4) 통계 정보 갱신

Oracle 옵티마이저가 최적의 실행 계획을 수립하려면 정확한 통계 정보가 필요합니다.

-- 테이블 통계 수집
EXEC DBMS_STATS.GATHER_TABLE_STATS('스키마명', '직원');

. . . . .

5) 성능 측정 팁

최적화 기법	효과	적용 시점
인덱스 생성	매우 높음	필수
WHERE 절 필터링	높음	조건이 명확할 때
NOCYCLE 사용	중간	순환 참조 가능성이 있을 때
LEVEL 제한	높음	깊이가 깊을 때
통계 정보 갱신	중간	데이터 변경이 많을 때

---

#5. 자주 발생하는 오류 해결법

계층 쿼리 사용 시 자주 발생하는 오류와 해결 방법을 정리했습니다.

1) ORA-01436: CONNECT BY 루프 오류

이 오류는 순환 참조가 발생했을 때 나타납니다. 예를 들어, A의 상사가 B이고 B의 상사가 다시 A인 경우입니다.

(1) 오류 원인

-- 순환 참조 예시
사원ID: 100, 관리자ID: 200
사원ID: 200, 관리자ID: 100 -- 순환!

(2) 해결 방법

SELECT
    사원명,
    CONNECT_BY_ISCYCLE AS 순환여부
FROM 직원
START WITH 관리자ID IS NULL
CONNECT BY NOCYCLE PRIOR 사원ID = 관리자ID;

NOCYCLE 키워드를 추가하면 순환이 감지될 때 해당 경로를 건너뛰고 계속 진행합니다.

. . . . .

2) ORA-30002: SYS_CONNECT_BY_PATH 오류

SYS_CONNECT_BY_PATH 함수 사용 시 구분자가 값 내부에 포함되어 있으면 오류가 발생합니다.

(1) 오류 원인

-- 구분자 '/'가 데이터에 포함된 경우
카테고리명: '남성/여성 공용'
SYS_CONNECT_BY_PATH(카테고리명, '/') -- 오류!

(2) 해결 방법

SELECT
    -- 데이터에 포함되지 않은 구분자 사용
    SYS_CONNECT_BY_PATH(카테고리명, ' > ') AS 경로
FROM 카테고리
START WITH 상위카테고리ID IS NULL
CONNECT BY PRIOR 카테고리ID = 상위카테고리ID;

. . . . .

3) 성능 저하 문제

데이터가 많거나 계층이 깊을 경우 쿼리 실행 시간이 길어지는 문제가 발생할 수 있습니다.

(1) 원인 진단

-- 실행 계획 확인
EXPLAIN PLAN FOR
SELECT * FROM 직원
START WITH 관리자ID IS NULL
CONNECT BY PRIOR 사원ID = 관리자ID;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

(2) 해결 방안

앞서 설명한 성능 최적화 방법을 종합적으로 적용합니다.

① 연결 컬럼에 인덱스 생성

② WHERE 절로 데이터 범위 축소

③ LEVEL 제한으로 깊이 제어

④ 통계 정보 최신화

. . . . .

4) ORDER SIBLINGS BY 무시 현상

일반 ORDER BY를 사용하면 계층 구조가 깨지는 문제가 발생합니다.

(1) 잘못된 예시

SELECT 사원명
FROM 직원
START WITH 관리자ID IS NULL
CONNECT BY PRIOR 사원ID = 관리자ID
ORDER BY 사원명; -- 계층 구조 파괴!

(2) 올바른 방법

SELECT 사원명
FROM 직원
START WITH 관리자ID IS NULL
CONNECT BY PRIOR 사원ID = 관리자ID
ORDER SIBLINGS BY 사원명; -- 계층 유지하며 정렬

---

마무리

Oracle의 START WITH CONNECT BY PRIOR는 계층적 데이터를 효과적으로 다루는 강력한 도구입니다. 조직도, 카테고리, 댓글 시스템 등 실무에서 자주 마주치는 트리 구조 데이터를 간결하고 효율적으로 조회할 수 있습니다.

핵심 포인트를 정리하면 다음과 같습니다.

① START WITH로 시작점을 지정하고 CONNECT BY PRIOR로 연결 관계 정의

② LEVEL, SYS_CONNECT_BY_PATH 등 내장 함수로 계층 정보 활용

③ 인덱스 생성과 WHERE 절 필터링으로 성능 최적화

④ NOCYCLE 키워드로 순환 참조 방지

⑤ ORDER SIBLINGS BY로 계층 구조 유지하며 정렬

처음에는 복잡해 보일 수 있지만, 기본 문법을 이해하고 실전 예제를 따라 해보면 금방 익숙해집니다. 데이터베이스에서 트리 구조를 다루는 프로젝트가 있다면 이 기능을 적극 활용해보시기 바랍니다. 복잡한 비즈니스 로직을 간결한 SQL로 해결할 수 있을 것입니다.

긴 글 읽어주셔서 감사합니다.

끝.