오라클 - 계층형 쿼리 START WITH... CONNECT BY PRIOR

오라클에서는 아주 강력한 계층형 쿼리를 지원한다. 

제목에도 써놨지만 바로 START WITH... 어쩌고 저쩌고 CONNECT BY PRIOR 이다. 

왜 오라클에서는 이라고 써놨냐면 다른 DBMS 에서는 지원하지 않는 듯 하기 때문이다. 

(잘못 알고 있는 걸수도 있는데 -_- 그렇다고 한다.)

하여튼 이 계층형 쿼리는 아주 편리한 쿼리인데 특히나 게시판 + 답글 같은 쿼리를 작성할 때 아주 유용하게 사용할 수 있다. 

기본적인 사용법은 다음 쿼리를 보자.

SELECT	LEVEL, LPAD(EMPLOYEE_ID, LEVEL*3, '.')
FROM	EMPLOYEES
START WITH EMPLOYEE_ID = 100
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;

START WITH 로 시작하는 부분에는 계층이 시작될 제일 첫 노드의 키 값을 지정해주면 된다.

예를 들어 EMPLOYEES 테이블은 KING 이라는 사원만이 유일하게 MANAGER_ID 를 가지고 있지 않은데 

이 경우 KING 사원은 당연히 사장일테고 이 경우 KING 사원이 제일 첫 노드로 지정될 수 있다. 

따라서 EMPLOYEE_ID = 100 부분은 MANAGER_ID IS NULL 로 대체가 가능하다.

CONNECT BY PRIOR 로 시작하는 부분에는 노드의 부모 키값과 이를 참조하는 자식 키값을 JOIN 시켜준다고 생각하면 되는데 

좌측에 기술하는 컬럼이 부모 키값이 되고 우측에 기술하는 컬럼이 자식 키값이 된다. 

이 기술하는 컬럼의 위치는 매우 중요한데 그 이유는 사실 별 거 없다. 

컬럼을 기술하는 위치에 따라서 계층이 반대로 나오기 때문이다.

예를 들자면 아래와 같다.

SELECT	LEVEL, LPAD(EMPLOYEE_ID, LEVEL*3, '.')
FROM	EMPLOYEES
START WITH EMPLOYEE_ID = '100'
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
;
----------------------------------------------------
1	100
2	...101
3	......108
4	.........109
4	.........110
4	.........111
4	.........112
4	.........113

위의 쿼리는 100번의 EMPLOYEE_ID 를 MANAGER_ID 로 갖는 사원들을 찾아 계층형으로 표시하라는 쿼리이다.

따라서 101번의 MANAGER_ID 는 100번이므로 100번의 밑에 표시가 되었고 마찬가지로 101번을 MANAGER_ID 로 갖는 

108번이 그 다음에 표시가 되고 또한 마찬가지로 108번을 MANAGER_ID 로 갖는 109, 110, 111, 112, 113번이 이어서 표시가 되었다.

그럼 위 쿼리의 PRIOR 부분의 부모, 자식 키값의 위치를 변경시켜보자.

SELECT	LEVEL, LPAD(EMPLOYEE_ID, LEVEL*3, '.')
FROM	EMPLOYEES
START WITH EMPLOYEE_ID = '100'
CONNECT BY PRIOR MANAGER_ID = EMPLOYEE_ID
;
----------------------------------------------------
1	100

부모, 자식 키값의 위치를 변경시키면 결과값이 하나만 나오는 것을 볼 수 있는데 이것은 계층이 반대로 생성이 되어 

START WITH EMPLOYEE_ID = '100' 구문으로 인해 다른 계층이 표시될 수 없게 되었기 때문이다.

왜냐하면 두번째 쿼리는 100번의 MANAGER_ID 를 EMPLOYEE_ID 로 갖는 사원들에 대해 계층을 생성하라는 의미인데 

100번의 MANAGER_ID 는 NULL 이므로 해당 EMPLOYEE_ID 를 찾을 수 없어 발생하는 현상인 것이다.

그럼 두번째 쿼리는 아주 쓸모가 없는 것이냐 하면 그것도 아니다. 나는 한명의 부모를 가지고 있고 

그 부모 또한 한명의 부모를 가지고 있기 때문에 역으로 추적을 할 수도 있다.

말로 하면 어려우니 직접 쿼리를 돌려보자. 백문이 불여일견!

SELECT	LEVEL, LPAD(EMPLOYEE_ID, LEVEL*3, '.')
FROM	EMPLOYEES
START WITH EMPLOYEE_ID = '113'
CONNECT BY PRIOR MANAGER_ID = EMPLOYEE_ID
;
----------------------------------------------------
1	113
2	...108
3	......101
4	.........100

위처럼 자식 값을 기준으로 잡게 되면 해당 부모를 거슬러 올라가는 결과를 만들어낼 수 있다. 

즉, 이 쿼리문을 사용할 때는 단 한가지만 주의하면 된다. PRIOR 의 좌측에 오는 컬럼이 기준이 되어 계층을 생성한다. 라는 사실!

그리고 여담이지만 LEVEL 이라는 구문은 CONNECT BY 를 사용할 때만 사용이 가능하다. 

표시되는 결과의 LEVEL 값을 리턴해준다. 끝!