계층형쿼리

계층형 쿼리란?

계층형 쿼리(HierarchicalQuery)

 : 2차원 형태의 테이블에 저장된 데이터를 계층형 구조로 결과를 반환하는 쿼리

 

쉽게 말해 데이터들의 상하 관계(수직 관계)를 나타낼 수 있는 쿼리이며

오라클만이 가지고 있는 독특하고 강력한 기능이다.

 

그림으로 보면 쉽게 이해할 수 있다.

부서의 계층 관계

이러한 데이터들의 수직 관계를 표현하기 위해 계층형 쿼리를 사용한다.

계층형 쿼리 구조

계층형 쿼리의 구조를 보면 아래와 같다.

SELECT expr1, expr2, ...
  FROM 테이블 
 WHERE 조건
 START WITH[최상위 조건]
CONNECT BY [NOCYCLE][PRIOR 계층형 구조 조건]
 ORDER SIBLINGS BY 컬럼;

하나씩 어떤 역할인지 알아보자.

• START WITH
  • 계층 구조 전개의 루트로 사용될 행을 지정
  • 서브 쿼리를 사용할 수도 있다.
• CONNECT BY [NOCYCLE][PRIOR 계층형 구조 조건]
  • 계층 구조에서 상위계층(부모행)과 하위계층(자식행)을 규정한다.
  • CONNECT BY PRIOR 자식컬럼 = 부모컬럼
    • 부모에서 자식으로 순방향 트리 구성(TOP DOWN)
  • CONNECT BY PRIOR 부모컬럼 = 자식컬럼
    • 자식에서 부모로 역방향 트리 구성(BOTTOM UP)
  • NOCYCLE
    • 무한 루프 방지
    • CYCLE이란 데이터를 전개하면서 이미 나타났던 동일한 데이터가 다시 나타나는 것
    • CYCLE이 발생하면 런타임 오류가 발생
    • NOCYCLE은 CYCLE이 발생한 이후의 데이터는 전개하지 않는다.
  • ORDER SIBLINGS BY
    • 레벨이 같은 형제 노드 사이에저 정렬을 수행한다.
    • 밑에서 예시로 보겠지만 그냥 ORDER BY를 사용할 경우 계층형 구조가 깨져버린다.
    • 계층형 구조까지 보존하면서 정렬을 해야할 때 사용한다.

계층형 쿼리 사용

계층형 쿼리의 구조를 알아보았으니 이제 사용해보자

위에서 보았던 부서의 계층 관계를 출력한다.

SELECT department_id, LPAD(' ' , 3 * (LEVEL-1)) || department_name, LEVEL
   FROM departments
  START WITH parent_id IS NULL
 CONNECT BY PRIOR department_id  = parent_id;

부서의 계층 관계를 출력해보았는데 여기서 정렬을 한번 테스트 해보자

계층형 쿼리 정렬

먼저 일반적으로 사용하는 ORDER BY를 통해 정렬을 해서 결과를 보자.

SELECT department_id, LPAD(' ' , 3 * (LEVEL-1)) || department_name, LEVEL
   FROM departments
  START WITH parent_id IS NULL
 CONNECT BY PRIOR department_id  = parent_id
  ORDER BY department_name;

결과를 보니 엉망진창이다.

계층형 구조가 깨져버린다.

원하는 결과는 부서명으로 정렬됨과 동시에 계층형 구조까지 보존하는 것인데

그럴려면 ORDER SIBLINGS BY절을 사용해야한다.

SELECT department_id, LPAD(' ' , 3 * (LEVEL-1)) || department_name, LEVEL
   FROM departments
  START WITH parent_id IS NULL
 CONNECT BY PRIOR department_id  = parent_id
  ORDER SIBLINGS BY department_name;

ORDER SIBLING BY절은 계층형 구조를 깨지 않고 정렬을 수행한다.

결과를 보면 같은 부모를 가진 로우, 즉 레벨이 같은 형제 로우에 한해서 정렬이 수행되었음을 알 수 있다.

 

계층형 쿼리에 대한 주된 내용은 여기까지 정리하고

다음 글에서 계층형 쿼리에서 제공되는 의사컬럼과 함수에 대해서 간단하게 정리를 해보겠다.