Oracle :: 계층형 구조 (Hierarchical Query)

Oracle 8g부터 지원하며, 트리처럼 가지를 쳐 뻗어 나가는 구조를 계층형 구조라 부르며, 각 단의 상위 계층과 하위 계층의 관계를 오라클에서는 START WITH와 CONNECT BY로 조회가 가능하다.

1. 구문

- START WITH : 최상위 계층이 되는 row를 말한다. 반복 탐색에 대한 시작 지점을 말하며, AND절로 여러개의 시작 조건을 줄 수 있다. 또한 서브 쿼리를 사용할 수 있다.

- CONNECT BY : 계층형 구조가 어떤 식으로 연결되는지를 기술하는 명령어이다. 함께 쓰이는 PRIOR 키워드는 현재의 레코드를 의미한다.  함께 계층 구조로 표현이 가능하다. 서브 쿼리를 사용할 수 없다.

(ex. PRIOR 상위 = 하위 : 상위 노드로의 탐색 / PRIOR 하위 = 상위 : 하위 노드로의 탐색)

* 조건 기술시

1) 일반 쿼리 조건 : 일반 - WHERE 절 / 그룹핑한 결과에 대한 조건 - HAVING

2) 계층 쿼리 조건 : 일반 - WHERE 절 / 계층형 결과에 대한 조건 - CONNECT BY

 

2. 계층형 쿼리를 위해 필요한 3가지

1) 메뉴 순번 (numbering)

2) 해당되는 메뉴의 계층 : 상위 메뉴와 하위 메뉴를 분리함

3) 세부 메뉴의 상위 그룹에 대한 순번 : 조직 구조를 나타내기 위함

3. 계층형 쿼리의 내부 절차

1) JOIN이 있으면 먼저 처리

2) START WITH 절을 참조하여 최상위 계층의 row를 선택

3) CONNECT BY 구문을 따라 계층 관계를 파악

4) JOIN을 제외한 WHERE 절에 해당하는 row를 걸러냄 (row별로 조건에 맞지 않으면 걸러냄)

  (WHERE는 탐색을 완료한 후 데이터를 필터링하는 키워드이므로, 상황에 따라 불필요한 탐색을 줄일 시에는 CONNECT BY 절에 기술하는 것이 성능상 유리함)

3. 예제

1) 테이블 CREATE

각 지부와 그 지부에 따른 실적을 나타내는 컬럼으로 이루어진 DEPT 테이블과(부서) RESULT 테이블(실적)이 있다고 가정한다.

2) 데이터 INSERT

-- DEPT 테이블 PK 추가
CREATE INDEX IDX_DEPT_01 ON DEPT (UPPER_DEPT_CODE);
 
-- RESULT 테이블 PK 추가
ALTER TABLE RESULT ADD CONSTRAINT PK_RESULT PRIMARY KEY (YEAR_DATE, DEPT_CODE);
 
-- DEPT 테이블 DATA INSERT
INSERT INTO DEPT
 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;
 
COMMIT;
 
-- RESULT 테이블 DATA INSERT
INSERT INTO RESULT
 SELECT '2017.11', '1210', 1000 FROM DUAL UNION ALL
 SELECT '2017.11', '1220', 1000 FROM DUAL UNION ALL
 SELECT '2017.11', '1111', 1000 FROM DUAL UNION ALL
 SELECT '2017.11', '1112', 1000 FROM DUAL UNION ALL
 SELECT '2017.11', '1121', 1000 FROM DUAL UNION ALL
 SELECT '2017.11', '1122', 1000 FROM DUAL;
 
COMMIT;

3) 쿼리 조회

SELECT
  LEVEL,
  DEPT_CODE,
  DEPT_NAME,
  UPPER_DEPT_CODE
FROM
  DEPT
START WITH UPPER_DEPT_CODE IS NULL
CONNECT BY PRIOR DEPT_CODE = UPPER_DEPT_CODE
ORDER BY LEVEL;

- 각 DEPT_CODE를 LEVEL로 계층화하여 조회

SELECT
  DEPT_CODE,
  DEPT_NAME,
  UPPER_DEPT_CODE
FROM
  DEPT
START WITH DEPT_CODE = (SELECT DEPT_CODE
                        FROM DEPT
                        WHERE UPPER_DEPT_CODE IS NULL
                        START WITH DEPT_CODE = '1120'
                        CONNECT BY PRIOR UPPER_DEPT_CODE = DEPT_CODE)
CONNECT BY PRIOR DEPT_CODE = UPPER_DEPT_CODE;                        
 

- 최상위 노드 > 각 노드별 하위 계층 조회

4. 오라클 10g부터 계층 구조 쿼리에서 추가되는 기능

1) CONNECT_BY_ROOT : LEVEL이 가장 높은 최상위의 row 정보를 불러오는 키워드

SELECT
  LEVEL,
  CONNECT_BY_ROOT DEPT_CODE "ROOT DEPT_CODE",
  DEPT_CODE,
  DEPT_NAME,
  UPPER_DEPT_CODE
FROM
  DEPT
START WITH DEPT_NAME = '대표이사'
CONNECT BY PRIOR DEPT_CODE = UPPER_DEPT_CODE;

DEPT_NAME = '대표이사'를 기준으로 시작하여, DEPT_CODE (부서)와 UPPER_DEPT_CODE (상위부서코드)의 관계를 계층 구조로 조회했다. 따라서 위 그림에서 적색으로 표시한 부분을 보면 부서들이 모두 최상위 코드인 1000 (대표이사)를 가리키는 것을 확인할 수 있다.

2) CONNECT_BY_ISLEAF : row의 최하위 레벨 여부를 리턴한다. 최하위 레벨이면 1, 최하위 레벨이 아니면 0이다.

SELECT
  LEVEL,
  CONNECT_BY_ISLEAF "LEAF",
  DEPT_CODE,
  DEPT_NAME,
  UPPER_DEPT_CODE
FROM
  DEPT
START WITH DEPT_NAME = '대표이사'
CONNECT BY PRIOR DEPT_CODE = UPPER_DEPT_CODE;

DEPT_NAME = '대표이사'를 기준으로 시작하여, DEPT_CODE (부서)와 UPPER_DEPT_CODE (상위부서코드)의 관계를 계층 구조로 조회했다. 따라서 위 그림에서 적색으로 표시한 부분을 보면 부서들이 최하위에 있는 부서인 경우 1로 표기되고, 최하위에 있지 않은 부서인 경우 0으로 표기된다. (상단 조직도 참고)

3) SYS_CONNECT_BY_PATH : 현재 row까지의 경로를 쉽게 얻어올 수 있는 키워드이다.

SELECT
  LEVEL,
  SYS_CONNECT_BY_PATH(DEPT_NAME, '/') "PATH",
  DEPT_CODE,
  DEPT_NAME,
  UPPER_DEPT_CODE
FROM
  DEPT
START WITH DEPT_NAME = '대표이사'
CONNECT BY PRIOR DEPT_CODE = UPPER_DEPT_CODE;

4) ORDER SIBLING BY : 계층구조 쿼리에서 상관 관계를 유지한 상태로 ORDER BY를 할 수 있는 키워드.

SELECT
  LEVEL,
  DEPT_CODE,
  DEPT_NAME,
  UPPER_DEPT_CODE
FROM
  DEPT
START WITH DEPT_NAME = '대표이사'
CONNECT BY PRIOR DEPT_CODE = UPPER_DEPT_CODE
ORDER SIBLINGS BY DEPT_NAME;

* ORDER BY로 정렬하면 계층구조 관계가 유지되지 않는다.