조직도, 카테고리, 댓글 답글, 부품 구성표처럼 하나의 테이블에서 부모와 자식 관계를 표현하는 데이터가 많습니다. 단순 조인만 반복하면 깊이가 바뀔 때마다 SQL을 수정해야 하고 순환 데이터가 있으면 조회가 끝나지 않을 수 있습니다. 오라클의 계층형 쿼리와 오라클·Microsoft SQL Server의 재귀 CTE를 사용하면 깊이를 미리 알 수 없는 트리와 그래프를 SQL에서 탐색할 수 있습니다.
인접 목록 모델 이해하기
가장 흔한 구조는 각 행에 자신의 ID와 parent_id를 저장하는 인접 목록입니다. 이해하기 쉽고 노드 이동도 비교적 단순하지만 전체 하위 트리를 조회하려면 재귀가 필요합니다. 루트는 parent_id가 NULL인 행으로 표현할 수 있으나 업무에 따라 가상 루트 값을 사용할 수도 있습니다. 외래 키로 부모 존재를 보장하고 자기 자신을 부모로 지정하지 못하도록 검증합니다.
오라클 CONNECT BY 기본 구조
오라클은 START WITH에서 시작 행을 정하고 CONNECT BY PRIOR id = parent_id처럼 부모·자식 연결 조건을 정의할 수 있습니다. LEVEL 의사 열로 깊이를 얻고, SYS_CONNECT_BY_PATH로 루트부터 현재 노드까지 경로를 만들 수 있습니다. PRIOR의 위치가 반대면 위로 탐색하거나 예상과 다른 방향으로 연결되므로 작은 데이터로 결과를 확인해야 합니다.
재귀 CTE의 기준 행과 재귀 행
SQL Server와 최신 오라클에서 재귀 CTE는 시작 행을 반환하는 앵커 쿼리와 이전 결과를 다시 테이블에 연결하는 재귀 쿼리를 UNION ALL로 결합합니다. 각 단계에서 depth를 1씩 늘리고 경로 값을 이어 붙일 수 있습니다. UNION 대신 UNION ALL을 주로 사용하는 이유는 중복 제거 정렬 비용을 피하고 탐색 관계를 명확히 하기 위해서지만, 데이터 중복과 순환은 별도로 제어해야 합니다.
작성 체크포인트
- 앵커가 필요한 루트만 선택하는지 확인합니다.
- 부모와 자식 조인 방향을 작은 예제로 검증합니다.
- 깊이 상한과 순환 감지 방식을 정합니다.
- 같은 부모 아래의 출력 순서 기준을 명시합니다.
- 경로 문자열의 길이와 자료형을 충분히 잡습니다.
순환 데이터 방어하기
A의 부모가 B이고 B의 부모가 다시 A라면 재귀가 반복됩니다. 오라클 CONNECT BY에서는 NOCYCLE과 관련 의사 열로 순환을 표시할 수 있습니다. 재귀 CTE에서는 방문한 ID를 경로에 보관해 다시 등장하는지 검사하거나 깊이 상한을 둡니다. SQL Server의 MAXRECURSION 옵션은 무한 실행을 막는 안전망이지만 데이터 오류를 고치는 수단은 아닙니다.
핵심 원칙: 최대 재귀 깊이 설정은 장애 방지 장치이고, 부모 변경 시 순환을 막는 업무 검증과 데이터 제약은 별도로 필요합니다.
트리 순서와 표시 경로 만들기
계층 결과는 SQL이 반환한 우연한 순서에 기대면 안 됩니다. 같은 부모 아래에서는 표시 순서 열과 고유 ID로 정렬합니다. 전체 트리를 깊이 우선으로 보여 주려면 각 단계의 정렬키를 고정 길이로 이어 붙인 경로 정렬값을 만들 수 있습니다. 문자열 경로는 구분자가 ID 값에 포함되지 않는지 확인하고, 사용자에게 보여 주는 이름 경로와 내부 식별자 경로를 분리합니다.
하위 노드 집계와 중복 계산
각 부서의 모든 하위 인원 수나 부품의 총 필요 수량을 계산할 수 있습니다. 그래프에서 하나의 노드가 여러 부모로 연결될 수 있다면 같은 노드가 여러 경로로 등장하는 것이 맞는지, 한 번만 집계해야 하는지 업무 규칙을 먼저 정합니다. 부품 구성표에서는 경로마다 수량을 곱해야 할 수 있어 단순 COUNT가 올바르지 않습니다.
성능을 위한 인덱스와 범위 제한
아래 방향 탐색이 많다면 parent_id 인덱스가 중요하고 특정 노드 조회를 위해 ID의 고유 인덱스가 필요합니다. 루트 전체를 매번 펼치지 말고 필요한 하위 트리부터 시작합니다. 깊은 트리를 자주 조회하고 변경이 드물다면 경로 열, 중첩 집합, 클로저 테이블 같은 다른 모델을 검토할 수 있지만 쓰기 복잡도와 저장 공간이 늘어납니다.
오라클과 SQL Server 이식성
CONNECT BY는 오라클에 특화돼 있고 재귀 CTE는 두 제품에서 비교적 공통된 구조를 제공하지만 자료형 추론과 문자열 결합, 재귀 제한 문법은 다릅니다. 특히 앵커의 문자열 길이가 짧게 추론되면 SQL Server 재귀 단계에서 경로가 잘릴 수 있어 명시적인 CAST가 필요합니다. 날짜와 ID 자료형도 양쪽에서 동일한 의미로 매핑되는지 확인합니다.
애플리케이션 연동 주의점
결과를 JSON 트리로 만들 때 SQL 결과 전체를 메모리에 담기보다 데이터 크기 상한을 정합니다. 사용자가 선택한 루트 ID는 바인드 변수로 전달하고, 정렬 열 이름은 허용 목록으로 제한합니다. 권한 있는 노드만 보여 줘야 한다면 루트 접근 권한뿐 아니라 재귀 결과의 각 행에 정책이 올바르게 적용되는지 검증합니다.
테스트 순서
- 루트 하나와 자식이 없는 노드를 조회합니다.
- 여러 단계와 형제 순서를 확인합니다.
- 부모가 없는 고아 행을 처리합니다.
- 자기 참조와 두 노드 순환을 주입해 방어를 시험합니다.
- 최대 깊이와 경로 길이 경계를 검증합니다.
- 대량 트리에서 실행 계획과 읽기량을 측정합니다.
- 오라클과 SQL Server 결과의 깊이·경로·순서를 비교합니다.
계층형 SQL은 문법보다 데이터 모델과 순환 정책이 더 중요합니다. 탐색 방향, 중복 의미, 정렬과 최대 깊이를 먼저 정의하고 엔진별 문법으로 옮기면 조직도와 카테고리 같은 구조를 안전하게 다룰 수 있습니다.
한 줄 요약: 계층형 쿼리는 앵커와 탐색 방향, 안정적인 순서, 순환 방어를 명확히 하고 parent_id 인덱스로 뒷받침해야 합니다.