[Oracle] 계층형 쿼리, 의사컬럼

최근 특정 부서의 상위 부서 목록을 긁어오는 쿼리를 봤는데

계층형 쿼리라는것을 사용해서 아주 쉽게 가져오는것을 봤다.

 

예전에 교육받는중에 팀프로젝트 진행할때 비슷한 기능을 구현해본 경험이 있었는데

반대로 하위 부서 목록을 긁어오는 기능이었다.

 

그땐 그냥 부서 리스트를 depth순으로 전부 뽑아온 다음

(상위부서~ 하위부서 순서)

하위부서 목록을 담을 array를 하나 만든 다음

for문을 돌면서 해당 부서의 상위부서의 id가 하위부서목록 array에 존재하면 (in 을 썼던거같다)

하위부서 목록에 추가하는 방식으로 구현을 했었다.

 

그런데 계층형 쿼리를 사용하면 이런 방식이 아니라 그냥 쿼리레벨에서 간단하게 가져올수 있었다.

 

계층형 쿼리란

계층형 쿼리란 테이블에 계층형 데이터가 존재하는 경우 이를 조회하기 위한 쿼리이다.

계층형 데이터는 말그대로 상하관계의, 계층구조를 가지는 데이터로 대표적으로 부서 목록 등이 있다.

 

계층형 쿼리 사용법

상위부서 목록 출력하기(역방향)

SELECT *
	FROM USR_DEPT
	START WITH DEPT_SEQ = #{dept_seq}
	CONNECT BY PRIOR PARNT_DEPT_SEQ = DEPT_SEQ
	ORDER BY LEVEL

 

결과를 보면

1. 현재부서

2. 상위부서

3. 상위부서의 상위부서

순서로 나왔다.

 

하위부서 목록 출력하기(순방향)

SELECT *
	FROM USR_DEPT
	START WITH DEPT_SEQ = #{dept_seq}
	CONNECT BY PRIOR DEPT_SEQ = PARNT_DEPT_SEQ
	ORDER BY LEVEL

 

반대로 하위부서 목록을 출력하는 방법은 간단하다. PRIOR를 자식앞에 붙여주면 된다.

 

이러면 결과값은 다음과 같다

 

1. 현재부서

2. 하위부서1

3. 하위부서2

4. 하위부서3

 

PRIOR를 자식 앞에 붙여서 상위 -> 하위 방향으로 진행하는 것을 순방향 계층형 쿼리라고 하고

PRIOR를 부모 앞에 붙여서 하위 -> 상위 방향으로 진행하는 것을 역방향 계층형 쿼리라고 한다.

 

의사컬럼(LEVEL)

해당 코드들은 부서번호가 dept_seq인 부서의 모든 상위부서를 LEVEL(의사컬럼)에 따라 오름차순(디폴트) 출력한다.

 

의사컬럼은 실제 테이블에는 없지만 인위적으로 추가한 가짜 컬럼이다.

 

종류로는 계층형 쿼리에서 사용하는  CONNECT_BY_ISCYCLE, CONNECT_BY_ISLEAF, LEVEL

 

시퀸스에서 사용하는 NEXTVAL , CURRVAL

 

쿼리에서 반환되는 각 로우들에 대한 순서값을 나타내는 ROWNUM, ROWID가 있다.

 

여기서 사용한 LEVEL은 계층 쿼리 결과에서 각 행의 깊이를 나타낸다고 한다.

 

의사컬럼 LEVEL 말고 다른 정렬방식도 있는데

 

SIBLINGS라는 키워드다

ORDER SIBLINGS BY DEPT_SEQ

 

이와같은 방식도 존재한다. 

 

참고로 SIBLINGS는 의사컬럼이 아니라고 한다.

 

ORDER BY LEVEL VS ORDER SIBLINGS BY DEPT_SEQ 차이

그럼 마지막으로 두 방식의 차이를 알아보면

 

            1

      2           3

  4     5     6     7

 

이런 형태의 계층구조를 가진 부서 트리가 있다고 가정해 보자

 

ORDER BY LEVEL은 계층구조의 깊이에 따라 출력하기 때문에

1 2 3 4 5 6 7 의 순서로 출력된다.

 

반면 ORDER SIBLINGS BY DEPT_LVL 라고 출력한다면

현재 선택된 노드와 같은 부모를 가진 다른 노드들을 DEPT_SEQ을 기준으로 정렬하기 때문에
(말그대로 같은 부모를 가진, 같은 레벨의 자식들을 정렬하는 기준으로, 4와 5를, 6과 7을 정렬할 방법을 선택하는것)

1 2 4 5 3 6 7의 순서로 출력하게 된다

 

정리하면 ORDER BY LEVEL은 DFS(깊이우선탐색), ORDER SIBLINGS BY DEPT_SEQ는 BFS(너비우선탐색)과 같다.

DFS - Depth First Search

BFS - Breadth First Search

 

다음부터 계층형 쿼리를 사용하면 굳이 불필요한 자바코드 대신 쿼리레벨에서 훨씬 간단하고 빠르게 처리할수 있겠다.