CONNECT BY 절

Oracle CONNECT BY 계층형 쿼리

오라클(Oracle) 데이터베이스에서 계층형 데이터를 조회할 때 CONNECT BY 절을 사용합니다. 트리 구조, 조직도, BOM 구조 등의 데이터를 SQL 하나로 탐색하고 출력할 수 있는 강력한 기능입니다.

이 글에서는 CONNECT BY, START WITH, LEVEL, CONNECT_BY_ROOT, CONNECT_BY_ISCYCLE, CONNECT_BY_ISLEAF, SYS_CONNECT_BY_PATH 등 계층형 SQL에서 사용하는 핵심 요소들을 예제와 함께 정리합니다.

CONNECT BY 절 구조

절	설명
START WITH	계층 구조의 시작점(루트 노드)을 지정
CONNECT BY	부모-자식 간 연결 조건 정의
PRIOR	연결 방향 지정 (부모 → 자식 또는 자식 → 부모)
ORDER SIBLINGS BY	같은 부모를 가진 노드 간 정렬

예:

SELECT employee_id, manager_id, LEVEL
  FROM employees
 START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY employee_id;

PRIOR 키워드 설명

방식 설명

CONNECT BY PRIOR 자식 = 부모	부모 → 자식 (Top-down)

CONNECT BY 부모 = PRIOR 자식	자식 → 부모 (Bottom-up)

예제:

-- 순방향 전개

CONNECT BY PRIOR empno = mgr

-- 역방향 전개

CONNECT BY empno = PRIOR mgr

LEVEL 가상 컬럼

LEVEL은 계층의 깊이를 나타내는 가상 컬럼입니다. 루트 노드는 1부터 시작하며, 자식으로 갈수록 2, 3, ...으로 증가합니다.

CONNECT_BY_ROOT 함수

현재 행의 루트(시작점) 값을 반환합니다.

SELECT LEVEL, ename, CONNECT_BY_ROOT ename AS root_ename
  FROM emp
 START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

CONNECT_BY_ISCYCLE 가상 컬럼

사이클(순환 참조)을 감지할 수 있는 가상 컬럼입니다. NOCYCLE 옵션과 함께 사용해야 하며, 순환이 감지되면 1을, 아니면 0을 반환합니다.

SELECT empno, CONNECT_BY_ISCYCLE, LEVEL
  FROM emp
 START WITH mgr IS NULL
CONNECT BY NOCYCLE PRIOR empno = mgr;

CONNECT_BY_ISLEAF 가상 컬럼

해당 노드가 리프(leaf, 자식이 없는 노드)인지를 판별합니다.

SELECT empno, CONNECT_BY_ISLEAF
  FROM emp
 START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

SYS_CONNECT_BY_PATH 함수

루트부터 현재 행까지의 경로를 구분자와 함께 문자열로 반환합니다.

SELECT empno,
       SYS_CONNECT_BY_PATH(ename, ' > ') AS path
  FROM emp
 START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

성능 팁

   START WITH 조건 컬럼에는 인덱스가 존재해야 성능 저하를 방지할 수 있습니다.

   LEVEL이 깊어질수록 비용이 증가합니다. 필요하다면 LEVEL <= n 조건을 함께 사용하세요.

   DUAL CONNECT BY 형태는 조인 없이 다량의 반복 데이터를 생성할 수 있으나, 성능 이슈 발생 시 MERGE JOIN을 고려하세요.

예:

SELECT /*+ LEADING(A) USE_MERGE(B) */
       COUNT (*)
  FROM t1 a
     , (SELECT LEVEL lv 
          FROM DUAL 
       CONNECT BY LEVEL <= 100) b
 WHERE b.lv <= a.c1;

실전 예제: 테스트 테이블 생성 및 계층 쿼리

CREATE TABLE t1 (
  parent_c VARCHAR2(1),
  child_c VARCHAR2(1)
);

INSERT INTO t1 VALUES ('a','b');
INSERT INTO t1 VALUES ('b','c');
INSERT INTO t1 VALUES ('a','c');
INSERT INTO t1 VALUES ('c','d');
INSERT INTO t1 VALUES ('c','e');
INSERT INTO t1 VALUES ('e','f');
COMMIT;

순방향 계층 전개 (부모 → 자식):

SELECT parent_c, child_c, LEVEL
  FROM t1
 START WITH parent_c = 'a'
CONNECT BY PRIOR child_c = parent_c;

역방향 계층 전개 (자식 → 부모):

SELECT parent_c, child_c, LEVEL
  FROM t1
 START WITH child_c = 'f'
CONNECT BY child_c = PRIOR parent_c;

날짜 생성 활용 예제

1) 일자 리스트 구하기:

SELECT TO_CHAR(TO_DATE('20240101','YYYYMMDD') + LEVEL - 1, 'YYYY-MM-DD') AS day
  FROM dual
CONNECT BY LEVEL <= TO_DATE('20240110','YYYYMMDD') - TO_DATE('20240101','YYYYMMDD') + 1;

2) 월별 리스트 구하기:

SELECT TO_CHAR(ADD_MONTHS(TO_DATE('202201','YYYYMM'), LEVEL-1), 'YYYY-MM') AS month
  FROM dual
CONNECT BY LEVEL <= 12;

결론

• CONNECT BY는 단순한 계층 쿼리 기능을 넘어, 조직도, 트리구조, BOM, 기간 생성 등 다양한 용도로 사용되는 필수 SQL 기능입니다.
• 위에서 다룬 가상 컬럼과 성능 팁을 익히면 보다 효율적으로 데이터 구조를 탐색할 수 있습니다.