START WITH, CONNECT BY, PRIOR

◈ START WITH
 
 - 계층 질의의 루트(부모행)로 사용될 행을 지정 합니다..
 - 서브쿼리를 사용할 수도 있습니다.
 
 
◈ CONNECT BY
 
 - 이 절을 이용하여 계층 질의에서 상위계층(부모행)과 하위계층(자식행)의 관계를 규정 합니다.
 - 보통 PRIOR 연산자를 많이 사용 합니다..
 - 서브쿼리를 사용할 수 없습니다..
 - PRIOR
   PRIOR 이 붙는 column 이 가져온 row 의 column을 의미한다. 즉 상위에 존재할 데이타가 되

   게 된다. 어느쪽에 붙느냐 잘 따져 본다.
  
 
◈ CONNECT BY의 실행순서는 다음과 같습니다.
 
 - 첫째 START WITH절
 - 둘째 CONNECT BY 절
 - 세째 WHERE 절 순서로 풀리게 되어있습니다.

 

◈ SYNTEX
  
   SELECT
   FROM
   START WITH
   CONNECT BY PRIOR
   AND
   ORDER SIBLINGS BY

 

   or

  

   SELECT
   FROM
   WHERE
   START WITH
   CONNECT BY PRIOR
   ORDER SIBLINGS BY
  
◈ 이용
   1) 쇼핑목 카테고리 관계 - 대분류, 중분류, 소분류 등을 트리 구조로
   2) 게시판 에서 일반글 과 답글과의 관계 등을 트리 구조로    
  
◈ 데이터가 많아질 경우....
 
 - 첫째로 풀리는 START WITH job='PRESIDENT' job 컬럼에 index가 생성되어 있지 않는다면
    속도를 보장할 수 없습니다.
 
 - 그리고 둘째로 풀리는 CONNECT BY PRIOR empno = mgr 역시 PRIOR 쪽의 컬럼값이 상수가
   되기 때문에 MGR컬럼에 index를 생성하여야 CONNECT BY의 속도를 보장할 수 있습니다.
  
 - 계층구조를 CONNECT BY, START WITH로 풀면 부분범위 처리가 불가능하고 Desc으로
   표현하기가 어렵 습니다.
  

--------------- 설명
-- 아래 예제 1
1) job 이 president 인 row 을 가져온다.
2) 가져온 row 에서 prior 이 붙은 comumn 의 데이타를 가져온다. 여긴선 empno 다.
3) PRIOR empno = mgr  empno 을 mgr 로 사용하는 row 을 가져온다. 기존의 row 상위, 비교해서 가져온 row 하위에 있게 된다.
4) 이제 두번째로 가져온 row 에서 PRIOR empno = mgr 을 실행시킨다.
5) 이런 과정이 연속으로 반복되면서 최종적으로 가져온 data 는 트리 구조를 이루게 된다.(계층구조)
6) LEVEL 은 depth 을 의미한다.

7) empno = PRIOR mgr 한다면 가져온 row 의 mgr을 기준으로 비교하여 data을 가져오게 된다.
   예제 2 참조

 
---- 예제 1
SELECT                  LEVEL,empno,ename, mgr, job  --  LEVEL 은 depth 을 의미한다.
FROM                     emp
START WITH             job = 'PRESIDENT'   -- 직업이 PRESIDENT를 기준으로
CONNECT BY PRIOR empno = mgr -- 사원(empno)과 관리자(mgr)의  관계를 계층

 

-- level 을 공백으로 찍어 본다.

SELECT                  LPAD(' ', 4*(LEVEL-1)) || ename ename,LEVEL, empno, mgr, job 
FROM                     emp
START WITH             job = 'PRESIDENT'   -- 직업이 PRESIDENT를 기준으로
CONNECT BY PRIOR empno = mgr    -- 사원(empno)과 관리자(mgr)의  관계를 계층

-- 결과치
ENAME         LEVEL EMPNO MGR     JOB
KING             1 7839      PRESIDENT
    JONES         2 7566 7839 MANAGER
        SCOTT     3 7788 7566 ANALYST
            ADAMS 4 7876 7788 CLERK
        FORD     3 7902 7566 ANALYST
            SMITH 4 7369 7902 CLERK
        JJS         3 9000 7566 ANALIST
    BLAKE         2 7698 7839 MANAGER
        ALLEN     3 7499 7698 SALESMAN
        WARD     3 7521 7698 SALESMAN
        MARTIN     3 7654 7698 SALESMAN
        TURNER     3 7844 7698 SALESMAN
        JAMES     3 7900 7698 CLERK
    CLARK         2 7782 7839 MANAGER
        MILLER     3 7934 7782 CLERK

----예제 2 - PRIOR 위치 변경
SELECT           LPAD(' ', 4*(LEVEL-1)) || ename ename,LEVEL, empno, mgr, job  -- level 을 공백으로 찍어 본다.
FROM             emp
START WITH       job = 'CLERK'   -- 직업이 CLERK를 기준으로
CONNECT BY empno = PRIOR mgr -- 사원(empno)과 관리자(mgr)의  관계를 계층   

-- 결과치
ENAME             LEVEL EMPNO MGR     JOB
SMITH             1     7369 7902 CLERK
    FORD         2     7902 7566 ANALYST
        JONES     3     7566 7839 MANAGER
            KING 4     7839      PRESIDENT
ADAMS             1     7876 7788 CLERK
    SCOTT         2     7788 7566 ANALYST
        JONES     3     7566 7839 MANAGER
            KING 4     7839      PRESIDENT
JAMES             1     7900 7698 CLERK
    BLAKE         2     7698 7839 MANAGER
        KING     3     7839      PRESIDENT
MILLER             1     7934 7782 CLERK
    CLARK         2     7782 7839 MANAGER
        KING     3     7839      PRESIDENT

---- 예제 3: 조건 절 사용
-- 1) WHERE  절 사용    
SELECT           LPAD(' ', 4*(LEVEL-1)) || ename ename,LEVEL, empno, mgr, job  -- level 을 공백으로 찍어 본다.
FROM             emp
WHERE     ename LIKE '%K%'
START WITH       job = 'PRESIDENT'   -- 직업이 PRESIDENT를 기준으로
CONNECT BY PRIOR empno = mgr -- 사원(empno)과 관리자(mgr)의  관계를 계층

-- 2) CONNECT BY PRIOR 아래에 AND 사용
SELECT           LPAD(' ', 4*(LEVEL-1)) || ename ename,LEVEL, empno, mgr, job  -- level 을 공백으로 찍어 본다.
FROM             emp
START WITH       job = 'PRESIDENT'   -- 직업이 PRESIDENT를 기준으로
CONNECT BY PRIOR empno = mgr -- 사원(empno)과 관리자(mgr)의  관계를 계층
AND      ename LIKE '%K%'

-- 3) LEVEL 조건 사용
SELECT           LPAD(' ', 4*(LEVEL-1)) || ename ename, empno, mgr, job
FROM             emp
START WITH       job='PRESIDENT'
CONNECT BY PRIOR empno =mgr
AND              LEVEL <= 2

 

---- 예제4 :  각 label별로 급여의 합과 인원수를 구하는 예제
 
SELECT           LEVEL, SUM(sal) salTotal,COUNT(empno) empnCnt
FROM             emp
START WITH       job='PRESIDENT'
CONNECT BY PRIOR empno=mgr     
GROUP BY         LEVEL
ORDER BY         LEVEL

-- 결과치 
     LEVEL   salTotal    empnCnt
---------- ---------- ----------
         1       5000          1
         2       8275          3
         3      13850          8
         4       1900          2

[출처] START WITH ~ CONNECT BY PRIOR ~ (계층구조로 data 가져오기)|작성자 전력질주