[ORACLE] START WITH, CONNECT BY - 계층적쿼리

원글 URL : http://modami.tistory.com/4
원글 URL : http://stillrabbit.blogspot.com/2008/01/oracle-start-with-connect-by-prior.html

START WITH와 CONNECT BY 는 오라클에서만 가능한 함수로서 데이터를 계층구조로 가져올 수 있다.

◈ START WITH
    - 계층 질의의 루트(부모행)로 사용될 행을 지정 합니다..
    - 서브쿼리를 사용할 수도 있습니다.

◈ CONNECT BY
    - 이 절을 이용하여 계층 질의에서 상위계층(부모행)과 하위계층(자식행)의 관계를 규정 합니다.
    - 보통 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으로 표현하기가 어렵 습니다.

◈ 예제

SELECT A.BBSSEQ, LPAD(' ', LEVEL * 4) ||  A.title
 FROM (
   SELECT *
   FROM KBBS02MT
   WHERE SITECD = 'ART'
   AND BBSCD='AR2'
 ) A
-- START WITH UppBBsSeq IN (select UppBBsSeq='0' or UppBBsSeq='1')
START WITH UppBBsSeq = '0'    //일종의 WHERE절. UppBBsSeq가 0인 레코드부터 계층적 쿼리 수행
CONNECT BY PRIOR BbsSeq = UppBbsSeq
//CONNECT BY - 각각의 행들이 어떻게 연결되어야 하는지(JOIN) 정보를 작성한다.
//PRIOR - BbsSeq를 참조하는 UppBbsSeq 컬럼이 속한 레코드를 모두 찾아라.(하위seq를 찾을 수 있다) 

◈ LEVEL 키워드 - 오라클에서 실행되는 쿼리내 ROWNUM과 같은 가상컬럼이다.

SELECT A.BBSSEQ, LPAD(' ', LEVEL * 4) ||  A.title
 FROM (
   SELECT *
   FROM KBBS02MT
   WHERE SITECD = 'ART'
   AND BBSCD='AR2'
 ) A
START WITH UppBBsSeq = '0' 
CONNECT BY PRIOR BbsSeq = UppBbsSeq AND LEVEL < 3     //CONNECT BY 절내에 위치