오라클 계층형 쿼리 응용

나는 최근 오라클의 계층형 쿼리의 사용에 대한 질문을 받았다.

계층형 쿼리에 대해서 많은 개발자들이 어려워하는 것이 사실이다. 아래와 같은 엔터티가 존재할 때

 

많은 개발자들은 일반적으로 아래와 같은 데이터를 구하기 위해 SQL을 구사하는 것에는 큰 어려움이 없을 것이다.

SELECT  부서코드, 부서명, 상위부서코드
FROM   부서

START WITH 상위부서코드 IS NULL

CONNECT BY PRIOR 부서코드 = 상위부서코드

위의 SQL은 최상위 노드인 대표이사로부터 최하위 노드인 여러 지부까지 위의 그림처럼 계층적으로 출력할 때 사용한다. 그러나 조회 조건이 변화되면서 위의 그림처럼 표현하기 위해서는 어떻게 해야 할까?

즉 아래SQL 처럼 조회조건이 ‘국내영업부(1120)’ 인데 전체 조직도를 출력해야 하는 것이다.

SELECT 부서코드, 부서명, 상위부서코드

FROM 부서

START WITH 부서코드 = '1120'

CONNECT BY PRIOR 부서코드 = 상위부서코드

 위와 같은 SQL을 실행하면 아래와 같은 결과가 출력될 것이다. 우리가 원하는 결과가 아니다.

 

우리가 원하는 결과를 얻기 위해서는 아래의 SQL처럼 최상위 노드를 찾아서 위에서 아래로 다시 찾아야 한다.
 이 예제에서는 최상위 노드가 하나여서 최상위 노드를 찾는 것이 의미가 없겠으나, 최상위 노드가 여러 개인 경우에는 반드시 필요한 절차이다.

SELECT 부서코드, 부서명, 상위부서코드

FROM   부서

START WITH 부서코드 = (SELECT 부서코드

                                              FROM  부서

                                             WHERE 상위부서코드 IS NULL

                                             START WITH 부서코드 = '1120'

                                             CONNECT BY PRIOR 상위부서코드 = 부서코드)

CONNECT BY PRIOR 부서코드 = 상위부서코드;

비록 아주 간단한 예제이지만, 많은 개발자들이 어려워하는 부분이라 포스팅 해본다.
이러한 것을 응용하면, 다양한 업무에서 유용하게 사용할 수 있을 것이다.
 

참고로 오라클의 계층형 쿼리에 대해서 몇 가지 설명하면,’

-       START WITH 절은 반복 탐색에 대한 시작지점을 설정하는 것이다. AND 절로 여러 개의 시작조건을 줄 수 있다.

-       CONNECT BY 절은 상위노드 또는 하위노드를 검색하는 조건을 기술하는 절로 역시 AND 조건으로 여러 개의 검색 조건을 줄 수 있다.

-       CONNECT BY 절에 쓰이는 PRIOR 키워드는 현재 읽어둔 레코드를 의미한다. 계층형 질의는 셀프 조인과 유사하다. A라는 테이블을 셀프 조인하는 경우 먼저 읽혀진 레코드와 조인 되어질 또 다른 A라는 테이블이 있을 때 먼저 읽혀진 쪽을 의미하는 키워드가 PRIOR인 것이다. 그러므로 ‘PRIOR 상위부서코드 = 부서코드’ 로 기술하게 되면 상위노드로의 탐색이며, 반대로 ‘PRIOR 부서코드 = 상위부서코드’ 로 기술하게 되면 하위노드로의 탐색이 된다.

-       그리고 계층형 질의문에서의 WHERE절은 탐색을 완료한 후에 데이터를 필터링하는 조건으로서 상황에 따라 불필요한 탐색을 줄 일려면 CONNECT BY 절에 기술하여 주는 것이 성능상 유리하다.

-       이외에 가상 컬럼인 LEVEL을 사용할 수 있으며, 계층형 쿼리에서만 지원되는 유용한 함수들이 존재한다.

아래는 테스트를위한 스크립트 이다.

DROP TABLE 부서 PURGE;
DROP TABLE 실적 PURGE;
CREATE TABLE 부서
( 부서코드 VARCHAR2(4) PRIMARY KEY
 ,부서명 VARCHAR2(30) NOT NULL
 ,상위부서코드 VARCHAR2(4) NULL);
 CREATE INDEX IDX_부서_01 ON 부서 (상위부서코드);

 CREATE TABLE 실적
 ( 년월 VARCHAR2(7) NOT NULL
  ,부서코드 VARCHAR2(4) NOT NULL
  ,금액 NUMBER(10) NOT NULL);
ALTER TABLE 실적 ADD CONSTRAINT PK_실적 PRIMARY KEY (년월, 부서코드);

 INSERT INTO 부서
 SELECT '1000', '대표이사', NULL FROM DUAL UNION ALL
 SELECT '1100', '영업1본부', '1000' FROM DUAL UNION ALL
 SELECT '1200', '영업2본부', '1000' FROM DUAL UNION ALL
 SELECT '1110', '국내영업1부', '1100' FROM DUAL UNION ALL
 SELECT '1120', '국내영업2부', '1100' FROM DUAL UNION ALL
 SELECT '1210', '해외영업1부', '1200' FROM DUAL UNION ALL
 SELECT '1220', '해외영업2부', '1200' FROM DUAL UNION ALL
 SELECT '1111', '수도권지부', '1110' FROM DUAL UNION ALL
 SELECT '1112', '강원지부', '1110' FROM DUAL UNION ALL
 SELECT '1121', '호남지부', '1120' FROM DUAL UNION ALL
 SELECT '1122', '경남지부', '1120' FROM DUAL;

 INSERT INTO 실적
 SELECT '2013.07', '1210', 1000 FROM DUAL UNION ALL
 SELECT '2013.07', '1220', 1000 FROM DUAL UNION ALL
 SELECT '2013.07', '1111', 1000 FROM DUAL UNION ALL
 SELECT '2013.07', '1112', 1000 FROM DUAL UNION ALL
 SELECT '2013.07', '1121', 1000 FROM DUAL UNION ALL
 SELECT '2013.07', '1122', 1000 FROM DUAL;

 COMMIT;