[Oracle] 오라클 계층 쿼리, START WITH... CONNECT BY (feat. SYS_CONNECT_BY_PATH 함수)

순환(RECURSIVE) 관계
부모와 자식의 관계처럼 계층적 구조의 자료를 하나의 테이블 내에 구조화하여 기술
ex) 카테고리 테이블이나 조직도 테이블

7369 사번 SMITH의 매니저는 7902 사번 FORD
→ 7902 FORD의 매니저는 7566 사번 JONES
→ 7566 사번 JONES의 매니저는 7839 KING
▶ 7369 < 7902 < 7566 < 7839

순환(RECURSIVE) 전개
테이블의 순환 관계를 이용하여 부모와 자식 관계의 데이터를 전개하는 것

SELECT LPAD('    ', 2*(LEVEL-1))||EMPNO EMPNO,               MGR,               ENAME,               DEPTNO    FROM EMP  [WHERE EMPNO != '7844'] // 최종 결과에서 EMPNO가 7844인 데이터 제외    START WITH MGR IS NULL  CONNECT BY [NOCYCLE] PRIOR EMPNO = MGR  [ORDER SIBLINGS BY 컬럼명];

- START WITH : 계층 구조의 데이터를 읽어나가는데 있어 시작점을 지정, 서브쿼리 이용 가능
ex) START WITH MGR IN (SELECT ........)
- CONNECT BY : 다음에 읽을 자식 데이터를 지정, 전개를 수행하려는 두 개의 컬럼을 설정하는 조인 조건에 해당
ex) PRIOR 자식컬럼 = 부모컬럼 : 부모 → 자식 방향으로 내려가는 순방향 조회
    PRIOR 부모컬럼 = 자식컬럼 : 자식 → 부모 방향으로 올라가는 역방향 조회
- NOCYCLE : 동일한 데이터를 반복해서 읽는 CYCLE 형성 방지
- ORDER SIBLINGS BY : 형제 노드(동일한 LEVEL)의 데이터 사이에서 정렬 수행
※ 순환 전개에서 ORDER BY 절은 무쓸모

 

 

✔일반적으로 순환 전개에 일반 조인을 추가하면 해당 SQL은 악성 SQL로 변하므로 주의 요망!!

※ SYS_CONNECT_BY_PATH 함수와 함께 사용 가능 (부모로부터 상속받은 모든 데이터 추출)

SELECT LPAD('    ', 2*(LEVEL-1))||유저 유저,               SYS_CONNECT_BY_PATH(권한, ' ') 권한    FROM 권한  CONNECT BY PRIOR 유저 = 부모유저    START WITH 유저 = 'A';

* 성능 보장을 위해 반드시 START WITH 절의 유저 컬럼과 CONNECT BY 절의 부모유저 컬럼에 인덱스가 있어야 함