START WITH ~ CONNECT BY PRIOR ~ (계층구조로 data 가져오기)
IT_DB
2006/09/21 13:54
◈ 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
출처 : Tong – soomwave님의 Oracle통
Comments