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;
10. 날짜 생성 활용 예제
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 기능입니다. 위에서 다룬 가상 컬럼과 성능 팁을 익히면 보다 효율적으로 데이터 구조를 탐색할 수 있습니다.