계층 구조의 조회(Hierarchical Queries)
오라클 데이터베이스 scott 유저의 emp 테이블을 보면 empno와 mgr컬럼이 있다.
mgr 컬럼의 데이터는 해당 사원의 관리자의 empno를 의미 한다.
예를 들어서 아래의 데이터를 보면은..
EMPNO ENAME SAL MGR
------ ------- ------ ------
7369 SMITH 800 7902
7902 FORD 3000 7566
empno 7369사원의 관리자는 7902의 empno를 가진 사원이며
empno 7902사원의 관리자는 7566의 empno를 가진 사원이다.
이런 상위 계층과 하위계층의 관계를 오라클에서는 START WITH와 CONNECT BY를 이용해서 쉽게 가져올 수 있다.
상품의 카테고리(대분류,중분류,소분류...)를 조회 할때 START WITH와 CONNECT BY를 이용하면 트리 구조로 편리하게 조회 할 수 있다.
START WITH
- - 계층 질의의 루트(부모행)로 사용될 행을 지정 한다.
- - 서브쿼리를 사용할 수도 있다.
CONNECT BY
- - 이 절을 이용하여 계층 질의에서 상위계층(부모행)과 하위계층(자식행)의 관계를 규정 한다.
- - 보통 PRIOR 연산자를 많이 사용 한다.
- - 서브쿼리를 사용할 수 없다.
CONNECT BY의 실행순서는 다음과 같다.
- - 첫째 START WITH 절
- - 둘째 CONNECT BY 절
- - 세째 WHERE 절 순서로 풀리게 되어있다.
간단 예제
SCOTT 사용자로 접속하여 테스트를 진행.
아래 예제에서는 LEVEL 예약어를 사용하여 depth를 표현 하고 있다
-- 상위계층과 하위 계층 관계의 순서대로 쿼리해 옴 -- 직업이 PRESIDENT을 기준으로 계층 구조로 조회 한다. SQL> SELECT LEVEL,empno,ename, mgr FROM emp START WITH job = 'PRESIDENT' CONNECT BY PRIOR PRIOR empno = mgr; LEVEL EMPNO ENAME MGR ------ ---------- -------------- ------- 1 7839 KING 2 7566 JONES 7839 3 7788 SCOTT 7566 4 7876 ADAMS 7788 3 7902 FORD 7566 4 7369 SMITH 7902 2 7698 BLAKE 7839 3 7499 ALLEN 7698 3 7521 WARD 7698 3 7654 MARTIN 7698 3 7844 TURNER 7698 3 7900 JAMES 7698 2 7782 CLARK 7839 3 7934 MILLER 7782 -- LEVEL컬럼으로 depth를 알수 있다. -- JONES의 관리자는 KING 이다. -- SCOTT의 관리자는 JONES 이다. -- 예제와 같이 상/하의 계층 구조를 쉽게 조회 할 수 있다.
사원명 계층구조 조회 예제
SQL> SET LINESIZE 100 SQL> SET PAGESIZE 100 SQL> COL ename FORMAT A20 -- 예제1의 결과를 가지고 LPAD함수를 이용해서 ename왼쪽에 공백을 추가 했습니다. SQL> SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno, mgr, job FROM emp START WITH job='PRESIDENT' CONNECT BY PRIOR empno=mgr; ENAME EMPNO MGR JOB -------------------- ------- ------- --------- KING 7839 PRESIDENT JONES 7566 7839 MANAGER SCOTT 7788 7566 ANALYST ADAMS 7876 7788 CLERK FORD 7902 7566 ANALYST SMITH 7369 7902 CLERK BLAKE 7698 7839 MANAGER ALLEN 7499 7698 SALESMAN WARD 7521 7698 SALESMAN MARTIN 7654 7698 SALESMAN TURNER 7844 7698 SALESMAN JAMES 7900 7698 CLERK CLARK 7782 7839 MANAGER MILLER 7934 7782 CLERK
레벨 2까지만 조회하는 예제
SQL> SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno, mgr, job FROM emp START WITH job='PRESIDENT' CONNECT BY PRIOR empno=mgr AND LEVEL <=2; ENAME EMPNO MGR JOB ------------ ------ ------ --------- KING 7839 PRESIDENT JONES 7566 7839 MANAGER BLAKE 7698 7839 MANAGER CLARK 7782 7839 MANAGER
레벨별로 급여 합과 인원수를 구하는 예제
SQL> SELECT LEVEL, SUM(sal) total, COUNT(empno) cnt FROM emp START WITH job='PRESIDENT' CONNECT BY PRIOR empno=mgr GROUP BY LEVEL ORDER BY LEVEL; LEVEL TOTAL CNT ---------- ---------- ---------- 1 5000 1 2 8275 3 3 13850 8 4 1900 2
데이터가 많아질 경우
- 첫째로 풀리는 START WITH job='PRESIDENT' job 컬럼에 index가 생성되어 있지 않는다면 속도를 보장할 수 없다.
- 둘째로 풀리는 CONNECT BY PRIOR empno = mgr 역시 PRIOR 쪽의 컬럼값이 상수가 되기 때문에 MGR컬럼에 index를 생성하여야 CONNECT BY의 속도를 보장할 수 있다.
- 계층구조를 CONNECT BY, START WITH로 풀면 부분범위 처리가 불가능하고 DESC으로 표현하기가 어렵다.
문서에 대하여
- - 작성자 : 김정식 (oramaster _at_ naver.com)
- - 작성일 : 2002년 04월 21일
- - 강좌 URL : http://www.oracleclub.com/lecture/1300
- - 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- - 오라클클럽의 모든 강좌는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
====================================================================================================================
Oracle10g에서 CONNECT BY의 새로운 기능들
오라클팁의 "계층 구조의 조회(Hierarchical Queries)" 강좌에서 CONNECT BY 와 START WITH를 상관관계 쿼리에 대해서 소개를 했었습니다.
Oracle10g 부터 CONNECT BY 절에서 제공하는 CONNECT_BY_ROOT, SYS_CONNECT_BY_PATH, CONNECT_BY_ISLEAF 기능에 대해서 알아보겠습니다.
상관관계 쿼리 예제
아래 예제와 같이 계층구조로 조회 할 수 있습니다.
SQL> SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno
FROM emp
START WITH job='PRESIDENT'
CONNECT BY PRIOR empno=mgr;
ENAME EMPNO MGR JOB
-------------------- ---------- ---------- ---------
KING 7839 PRESIDENT
JONES 7566 7839 MANAGER
SCOTT 7788 7566 ANALYST
ADAMS 7876 7788 CLERK
FORD 7902 7566 ANALYST
SMITH 7369 7902 CLERK
CONNECT_BY_ROOT
상관관계 쿼리에서 LEVEL이 0인 최상위 로우의 정보를 얻어 올 수 있습니다.
SQL> SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno,
CONNECT_BY_ROOT empno "Root empno", level
FROM emp
START WITH job='PRESIDENT'
CONNECT BY PRIOR empno=mgr;
ENAME EMPNO Root empno LEVEL
-------------------- ---------- ---------- ----------
KING 7839 7839 1
JONES 7566 7839 2
SCOTT 7788 7839 3
ADAMS 7876 7839 4
FORD 7902 7839 3
SMITH 7369 7839 4
SYS_CONNECT_BY_PATH
상관관계 쿼리에서 현재 로우 까지의 PATH 정보를 쉽게 얻어 올 수 있습니다.
SQL> COL path FORMAT A40
SQL> SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno,
SYS_CONNECT_BY_PATH(ename, '/') "Path"
FROM emp
START WITH job='PRESIDENT'
CONNECT BY PRIOR empno=mgr;
ENAME EMPNO Path
-------------------- ---------- -------------------------------
KING 7839 /KING
JONES 7566 /KING/JONES
SCOTT 7788 /KING/JONES/SCOTT
ADAMS 7876 /KING/JONES/SCOTT/ADAMS
FORD 7902 /KING/JONES/FORD
SMITH 7369 /KING/JONES/FORD/SMITH
CONNECT_BY_ISLEAF
상관관계 쿼리에서 로우의 최하위 레벨 여부를 반환 합니다.
SQL> SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno,
CONNECT_BY_ISLEAF "leaf", level
FROM emp
START WITH job='PRESIDENT'
CONNECT BY NOCYCLE PRIOR empno=mgr;
ENAME EMPNO leaf LEVEL
-------------------- ---------- ---------- ----------
KING 7839 0 1
JONES 7566 0 2
SCOTT 7788 0 3
ADAMS 7876 1 4
FORD 7902 0 3
SMITH 7369 1 4
문서에 대하여
- - 작성자 : 김정식 (oramaster _at_ naver.com)
- - 작성일 : 2006년 04월 12일
- - 강좌 URL : http://www.oracleclub.com/lecture/1903
- - 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- - 오라클클럽의 모든 강좌는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
'프로그램 > DB' 카테고리의 다른 글
| [Oracle] 여러 row를 한 줄로 출력 - (WMSYS.WM_CONCAT) (0) | 2012.09.18 |
|---|---|
| [MySql] 여러 row를 한 줄로 출력 - (GROUP_CONCAT) (0) | 2012.09.18 |
| [펌] 오라클 페이징 처리 방법 (0) | 2012.06.08 |
| Oracle Procedure - 01 (0) | 2012.03.11 |
| Oracle Function - 01 (0) | 2012.03.11 |