01 | 계층형 쿼리

계층형 구조

계층형 쿼리_{HierarchicalQuery} 는 2차원 형태의 테이블에 저장된 데이터를 계층형 구조로 결과를 반환하는 쿼리를 말한다. 계층형 구조란 상하 수직 관계의 구조로 사원-대리-과장-부장과 같은 직급, 판매부-영업부 같은 부서 구조, 대학의 학과, 정부 부처 등이 계층형 구조에 속한다. 또한 엑셀과 같은 프로그램의 메뉴 구조, 답변형 게시판뿐만 아니라 이 책의 목차도 이에 포함된다. 예를 들어, 부서 테이블에 있는 부서 정보는 다음과 같이 상위-하위 부서로 나눌 수 있다.

위와 같은 형태로 데이터를 추출하기 위해서 부서 테이블은 부서번호(department_id)와 각 부서에 대한 상위부서번호(parent_id) 컬럼을 갖는 구조로 되어 있다. 그렇다면 이 두 가지 정보를 이용해 [그림 7-1]의 형태로 데이터를 추출하려면 쿼리를 어떤 식으로 작성해야 할까?

그리 쉽지는 않지만 그렇다고 너무 어렵지도 않을 것이라고 생각할 수도 있다. 하지만 실제로 쿼리를 작성해 본다면 만만치 않다는 점을 알게 될 것이다. 그럼, 어떤 식으로 쿼리를 작성할 것인지 차근차근 생각해 보자. 위와 같은 형태로 출력하기 위해 필요한 정보는, ① 부서번호, 부서명, 상위 부서번호, ② 각 부서별 레벨, ③ 계층적 구조로 조회되도록 하기 위한 정렬 순서가 필요하다. 이를 바탕으로 필자가 작성한 쿼리는 다음과 같다.

입력

    SELECT department_id,
           department_name,
           0 AS PARENT_ID,
           1 as levels,
           parent_id || department_id AS sort
      FROM departments
     WHERE parent_id IS NULL
     UNION ALL
    SELECT t2.department_id,
           LPAD(' ' , 3 * (2-1)) || t2.department_name AS department_name,
           t2.parent_id,
           2 AS levels,
           t2.parent_id || t2.department_id AS sort
      FROM departments t1,
           departments t2
     WHERE t1.parent_id is null
       AND t2.parent_id = t1.department_id
     UNION ALL
    SELECT t3.department_id,
           LPAD(' ' , 3 * (3-1)) || t3.department_name AS department_name,
           t3.parent_id,
           3 as levels,
           t2.parent_id || t3.parent_id || t3.department_id as sort
      FROM departments t1,
           departments t2,
           departments t3
     WHERE t1.parent_id IS NULL
       AND t2.parent_id = t1.department_id
       AND t3.parent_id = t2.department_id
     UNION ALL
    SELECT t4.department_id,
           LPAD(' ' , 3 * (4-1)) || t4.department_name as department_name,
           t4.parent_id,
           4 as levels,
           t2.parent_id || t3.parent_id || t4.parent_id || t4.department_id AS sort
      FROM departments t1,
           departments t2,
           departments t3,
           departments t4
     WHERE t1.parent_id IS NULL
       AND t2.parent_id = t1.department_id
       AND t3.parent_id = t2.department_id
       and t4.parent_id = t3.department_id
     ORDER BY sort;

쿼리가 꽤 복잡한데 차근차근 살펴 보자. 위 쿼리는 총 4개의 SELECT문이 UNION ALL로 연결되어 있다. 첫 번째 SELECT문은 가장 상위 부서인 총무기획부(parent_id 값이 NULL), 두 번째 SELECT문은 총무기획부 부서번호(10)을 parent_id 값으로 가지고 있는 부서, 세 번째는 두 번째 쿼리 결과로 나온 각 부서를 parent_id 값으로 가진 부서, 마지막은 세 번째 쿼리 결과로 나온 각 부서를 parent_id 값으로 가진 부서를 추출하고 있다. 각 쿼리에 순차적으로 레벨을 1에서 4까지 주었고, 이를 바탕으로 SORT(상위 부서에서 자신의 부서까지 부서번호를 연결)란 가상의 컬럼을 만들어 이를 기준으로 정렬하였다. 원하는 결과는 나왔지만 위 쿼리에는 몇 가지 문제점이 있다.

첫째, 현 부서 테이블의 계층 구조는 총 4레벨이지만, 레벨이 더 많아질 때마다 SELECT 문을 만들어 레벨 수 만큼 UNION ALL로 연결해야 한다.

둘째, 레벨 수 자체를 단순하게 직접 코딩했다(하드코딩).

셋째, 쿼리가 너무 복잡해 작성한 사람조차도 이해하기가 힘들다. 특히 정렬 기준인 SORT 컬럼은, 레벨 순으로 상위 부서ID를 ‘||’ 연산자로 연결해 만들었는데, 이 역시 계층 구조가 깊어질수록 점점 늘어나게 된다.

계층 구조가 4레벨로 고정되면 모르겠지만 시간이 지날수록 부서가 추가되거나 삭제되고 기존 부서의 계층 구조가 변동된다면 위 쿼리를 매번 바꿔야 하는데 이는 너무 비효율적이다. 이를 개선하고 개발자가 좀더 쉽게 계층형 구조를 표현할 수 있는 방법이 바로 계층형 쿼리이며, 이는 오라클만이 가진 독특한 기능 중에 하나다.