[DB] START WITH CONNECT BY PRIOR 사용법

START WITH CONNECT BY PRIOR 사용법 - Oracle 계층 쿼리 완벽 가이드

안녕하세요.

이번 포스팅은 Oracle 데이터베이스에서 계층적 데이터를 효과적으로 다루는 강력한 도구인 START WITH CONNECT BY PRIOR에 대해 상세히 알아보겠습니다. 계층형 쿼리가 생소하신 분들도 쉽게 이해할 수 있도록 기초부터 실전 예제까지 단계별로 설명해 드리겠습니다.

---

목차

1. START WITH CONNECT BY PRIOR란?
2. 기본 문법과 구조
3. 주요 키워드와 함수
4. 실전 활용 예제
5. 성능 최적화 팁
6. 자주 발생하는 오류와 해결 방법

 

#1. START WITH CONNECT BY PRIOR란?

Oracle 데이터베이스의 계층형 쿼리(Hierarchical Query)는 트리 구조의 데이터를 쉽게 조회할 수 있게 해주는 강력한 기능입니다. 조직도, 제품 카테고리, 댓글과 대댓글 같은 부모-자식 관계가 있는 데이터를 효과적으로 처리할 수 있습니다.

 

#2. 기본 문법과 구조

기본적인 계층 쿼리의 구조는 다음과 같습니다:

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

각 구성 요소를 살펴보겠습니다:

• START WITH: 계층 구조의 루트(최상위) 노드를 지정합니다.
• CONNECT BY PRIOR: 부모-자식 관계를 정의합니다.
• ORDER SIBLINGS BY: 같은 레벨의 노드를 정렬합니다.

 

#3. 주요 키워드와 함수

계층 쿼리에서 활용할 수 있는 유용한 함수들입니다:

• LEVEL: 현재 행의 계층 레벨을 반환합니다(루트는 1).
• SYS_CONNECT_BY_PATH: 루트에서 현재 노드까지의 경로를 문자열로 반환합니다.
• CONNECT_BY_ROOT: 현재 행의 루트 노드 값을 반환합니다.
• CONNECT_BY_ISLEAF: 현재 행이 리프 노드(자식이 없는 노드)인지 여부를 반환합니다.

 

#4. 실전 활용 예제

예제 1: 직원-관리자 계층 구조 조회

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

예제 2: 카테고리 계층 구조

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

예제 3: 댓글과 대댓글 표시

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

 

#5. 성능 최적화 팁

계층 쿼리의 성능을 향상시키기 위한 팁입니다:

1. 인덱스 활용: 연결 조건에 사용되는 열에 인덱스를 생성하세요.
2. WHERE 절 활용: 불필요한 데이터는 WHERE 절로 미리 필터링하세요.
3. NOCYCLE 키워드: 순환 참조 방지를 위해 NOCYCLE 키워드를 사용하세요.
4. CONNECT_BY_ROOT 활용: 루트 노드 정보를 효율적으로 조회하세요.

 

#6. 자주 발생하는 오류와 해결 방법

ORA-01436: CONNECT BY 루프 발견

순환 참조가 발생했을 때 나타나는 오류입니다. NOCYCLE 키워드를 추가하여 해결할 수 있습니다:

CONNECT BY NOCYCLE PRIOR 사원ID = 관리자ID

성능 저하 문제

계층이 깊거나 데이터가 많을 경우 성능 저하가 발생할 수 있습니다. 이럴 때는 인덱스 최적화와 함께 WHERE 절을 적극 활용하세요.

---

마무리

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

여러분도 이 글에서 배운 내용을 실제 프로젝트에 적용해보시기 바랍니다.

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

끝.