오라클 계층 쿼리(Hierarchical Query)의 사용법

업무를 데이터베이스로 구현을 하다보면 한 테이블 내의 데이터가 계층적 관계를 가지는 경우가 많이 있습니다. 예를 들어보면 코드정보, 부서정보, 사이트의 메뉴 정보 등이 되겠습니다.

 

계층의 단계가 고정 되어 있고, 두 계층 정도라면 테이블을 분리할 수도 있지만, 임의의 단계를 표현해야 한다면 한 테이블에 상위 데이터로의 연결고리를 가지도록 구현을 하게 됩니다. 제품의 BOM(Bill of Material - 부품의 계층 정보) 정보 같은 것이 되겠습니다.

 

이러한 자기참조 구조는 이해하기도 편하고, 깔끔하게 구현이 됩니다. 하지만 한가지 중대한 단점이 있습니다. 관계 데이터베이스는 한 행내의 데이터들 간의 연산은 아주 간단히 처리되지만, 다른 행과 관계되는 연산은 쿼리가 상당히 복잡해 집니다. 복잡한 서브쿼리를 동반하던가, 단계를 알 수 없다면 프로그램을 통해서 원하는 결과를 얻게 됩니다.

 

"그랬었습니다." 이말은 지금은 아니라는 것이겠죠. SQL:1999 표준을 따르는 데이터베이스는 CTE(Common Table Expression) 구문을 지원합니다. 이걸 이용해서 재귀쿼리를 만들 수 있습니다. 현재 대부분의 데이터베이스들이 CTE를 지원하고 있습니다. Oracle의 경우는 11g R2 부터 지원합니다. 그전 버전에서는 Oracle에서만 사용할 수 있는 START WITH ~ CONNECT BY 구문을 사용할 수 있습니다. 이것은 비표준 SQL 입니다. MariaDB인 경우 10.2 부터 CTE를 사용할 수 있습니다.

 

이글에서는 Oracle에서 두 가지 계층 데이터를 조회하는 방법을 알아보겠습니다.

 

먼저 테스트에 사용된 테이블 구조 입니다. 여기서는 "전자정부 표준프레임워크 - Portal Site(Oracle) 템플릿 프로젝트 설치" 에서 사용되는 메뉴 테이블을 대상으로 쿼리를 만들어 보겠습니다.

 

 

키는 MENU_NO 필드이고, 상위 정보는 UPPER_MENU_NO 필드에 가지고 있습니다. 그리고 MENU_ORDR은 자식 데이터의 순서를 가지고 있습니다. 최상위는 데이터가 하나이니까 순서가 1이고, 최상위 바로 아래 레벨의 데이터들이 다시 1부터 순서를 가집니다. 그리고 각각의 데이터들의 바로 아래 자식들의 순서가 다시 1부터 들어가는 방식 입니다.

 

자기참조 구조 로 데이터를 표현하는데 있어서도 외래키(Foreign Key)를 사용할 수 있습니다. 데이터의 무결성을 보장하기 위해서는 외래키를 사용하는 것이 좋을 것입니다. 외래키를 생성할 경우 계층의 최상위 데이터에는 그보다 상위가 없으므로 UPPER_MENU_NO 필드에 입력할 값이 없습니다. 그러므로 이 필드는 NULL이 됩니다. (NULL외에 다른것을 입력하면 에러 입니다.)

 

외래키가 없을 경우 참조 무결성이 강제로 체크되지 않으므로 최상위 데이터의 UPPER_MENU_NO에 NULL이 아닌 다른 값을 입력할 수 있습니다.

 

테스트에 사용된 Portal Site 템플릿의 예를 보면 최상위 데이터가 root 인데, MENU_NO가 0이고, UPPER_MENU_NO도 0으로 들어가 있습니다. 외래키가 없어서 이렇게 데이터가 들어 갈 수 있었던 것입니다.

 

이런 상태에서 키값으로 재귀 쿼리를 수행하게 되면 무한루프에 빠지게 됩니다. 상위가 자기 자신이기 때문입니다. Oracle은 이 경우 루핑이라고 에러를 내고 멈추어 버립니다. START WITH ~ CONNECT BY 구문을 사용하기 위해서는 root 의 UPPER_MENU_NO 를 아래의 이미지에서 처럼 NULL로 변경해야 됩니다.

 

 

첫 번째로 START WITH, CONNECT BY 의 사용법은 알아보겠습니다.

 

  SELECT MENU_NM, PROGRM_FILE_NM, MENU_NO, UPPER_MENU_NO, MENU_ORDR, LEVEL
   FROM LETTNMENUINFO
  WHERE MENU_NO <> 0
  START WITH MENU_NO = 0
CONNECT BY PRIOR MENU_NO = UPPER_MENU_NO
  ORDER SIBLINGS BY MENU_ORDR;

 

START WITH 절은 계층관계가 시작되는 루트 행을 지정하기 위한 구문입니다. 해당 구문은 생략이 가능하지만 생략하게 되면 대상 테이블 내에 존재하는 모든 행을 루트 행으로 간주하고 계층관계를 검색해 결과 행들 중의 일부는 중복 행이 발생할 가능성이 있습니다. 그러므로 CONNECT BY 구문을 사용할 때는 START WITH 절을 사용하는 것이 좋습니다.

 

CONNECT BY 절의 조건은 한 쌍의 행에 대한 부모-자식관계를 정의하기 위한 구문입니다. 해당 구문은 조건 내에 PRIOR 연산자를 이용해 부모 행의 컬럼 값을 지정합니다.

 

ORDER SIBLINGS BY 구문은 계층별로 순서를 부여하는 구문입니다. 그냥 ORDER BY를 사용하게 되면 계층은 고려되지 않고, 전체값만으로 정렬하므로 원하는 결과를 얻을 수 없습니다.

 

WHERE 절은 맨 마지막에 평가됩니다. 여기서는 root 행을 제외하기 위해서 사용되었습니다.

 

두 번째로 CTE(Common Table Expression)을 사용한 예제를 보겠습니다. Oracle 11g r2 부터 사용할 수 있습니다. CTE는 WITH 절을 사용해서 인라인 서브쿼리를 뽑아낸 것처럼 사용할 수 있는데, 재귀적 쿼리도 만들 수 있습니다. 이 기능을 이용해서 계층 데이터를 조회하게 됩니다.

 

WITH MENU (MENU_NM, PROGRM_FILE_NM, MENU_NO, UPPER_MENU_NO, MENU_ORDR, MENU_LEVEL)
AS (
   SELECT  MENU_NM, PROGRM_FILE_NM, MENU_NO, UPPER_MENU_NO, MENU_ORDR, 0 AS MENU_LEVEL
     FROM  LETTNMENUINFO
    WHERE  UPPER_MENU_NO IS NULL
UNION ALL
   SELECT  L.MENU_NM, L.PROGRM_FILE_NM, L.MENU_NO, L.UPPER_MENU_NO, L.MENU_ORDR
        ,  R.MENU_LEVEL + 1 AS MENU_LEVEL
     FROM  MENU R, LETTNMENUINFO L
    WHERE  R.MENU_NO = L.UPPER_MENU_NO
)

SEARCH DEPTH FIRST BY MENU_ORDR SET MENU_ORDER

SELECT MENU_NM, PROGRM_FILE_NM, MENU_NO, UPPER_MENU_NO, MENU_ORDR, MENU_LEVEL
  FROM MENU
 WHERE MENU_NO <> 0;

 

WITH 별칭 (필드 리스트) AS ( 쿼리 ) 부분이 인라인 서브쿼리와 같은 역할을 합니다. 차라리 일시적인 뷰(view)와 같다고 생각하면 되겠습니다.

 

여기서는 AS () 내부에서 UNION ALL 앞쪽은 최상위 데이터이고, 뒤쪽은 FROM MENU R, LETTNMENUINFO L 처럼 자신의 재귀적으로 참조합니다. 그래서 전체 계층구조를 모두 가져오게 되는 것입니다.

 

SEARCH DEPTH FIRST BY MENU_ORDR SET MENU_ORDER 부분은 ORDER SIBLINGS BY MENU_ORDR 와 같은 기능을 하는 구문입니다. 계층별로 정렬이 이루어 집니다.

 

마지막 SELECT 절은 위에서 만들어진 데이터를 조회하는 것입니다. 이부분에서는 WITH 절의 데이터와 다른 테이블을 조인하는 것도 가능합니다.(실제로 그렇게 사용하는 것이 더 일반적입니다.)

 

실행 결과 입니다.

 

 

이것으로 Oracle 에서 CTE와 START WITH ~ CONNECT BY 구문을 사용하여 계층적 데이터를 조회하는 것을 해 보았습니다. 이 구문들이 사용하기 쉬운 형태는 아닙니만, 익숙해지면 그렇게 어렵기만 한것도 아닌것 같습니다.