순환관계의 전개 - SYS_CONNECT_BY_PATH

이번 포스팅에서는 순환관계의 전개 방식에 대해 알아보도록 하겠습니다. 순환 관계에서 전개 부분에서 조건이 누락되거나 할 경우 인덱스를 활용할 수 없어 Full Scan이 발생할 수도 있습니다. 그리고 원하는데로 결과가 나오지 않는 경우도 다반사입니다.

 

순전개(상위에서 하위로 찾기)

이 방식은 일반적인 전개 방식으로 START WITH 시작 지점부터 아래로 전개해 나가는 방식입니다. 아래 예시를 보시면 '홍길동'이라는 직원이 있는 매장부터 해서 아래로 내려가게 됩니다. 

• CONNECT BY PRIOR 절 : 하위 = 상위

SELECT ORG_NM, UP_ORG_NO
FROM   TB_ORG
CONNECT BY PRIOR ORG_NO = UP_ORG_NO
START WITH MANG_NO = (SELECT EMP_NO 
                      FROM TB_EMP 
                      WHERE EMP_NM = '홍길동'
                      )
;
 

 

결과

일산매장  O0000000000003

 

역전개(하위에서 상위로 찾기)

역전개는 순전개와 반대입니다. START WITH 시작 지점부터 위로 찾아가면서 전개하는 방식입니다. 예시를 보시면 '홍길동' 직원이 있는 매장부터 위로 올라가며 찾아 줍니다. 

• CONNECT BY PRIOR 절 : 상위 = 하위

SELECT ORG_NM, UP_ORG_NO
FROM   TB_ORG
CONNECT BY PRIOR  UP_ORG_NO = ORG_NO 
START WITH MANG_NO = (SELECT EMP_NO 
                      FROM TB_EMP 
                      WHERE EMP_NM = '홍길동'
                      )
;
 

 

결과 

일산매장  O0000000000003 경기지점  O0000000000001 본사

 

순환관계 들여쓰기

순환관계를 표현할 때 좀더 보기 좋게 표현하는 방식은 LPAD를 이용하는 방법입니다. 아래와 같은 SQL로 구현이 가능합니다. 결과를 보시면 Level의 크기만큼 들여쓰기가 되는 것을 알 수 있습니다. 

 

SELECT ORG_NM, LPAD(' ', 2*LEVEL)||UP_ORG_NO UP_ORG_NO, LEVEL
FROM   TB_ORG
CONNECT BY PRIOR ORG_NO = UP_ORG_NO
START WITH MANG_NO = 'E0000000000001'
;
 

 

결과

ORG_NM   UP_ORG_NO                 LEVEL 본사                                                      1 서울지점      O0000000000001            2 논현매장        O0000000000002          3 노원매장        O0000000000002          3 목동매장        O0000000000002          3 신촌매장        O0000000000002          3 천호매장        O0000000000002          3 경기지점      O0000000000001            2

 

순환관계 SYS_CONNECT_BY_PATH

순환관계를 출력할 때 LPAD를 이용한 들여쓰기 말고 오라클에서 제공하는 함수로 표현할 수도 있습니다. 바로 SYS_CONNECT_BY_PATH 함수입니다. 이 함수는 구분자로 구분해서 상위부터 하위까지 표현됩니다. 각 레벨에 맞는 경로를 보여줍니다.

 

SELECT ORG_NM , (SELECT EMP_NM FROM TB_EMP WHERE EMP_NO = MANG_NO)
     , SYS_CONNECT_BY_PATH(ORG_NM,'/') PATH
FROM   TB_ORG
CONNECT BY PRIOR ORG_NO = UP_ORG_NO
START WITH MANG_NO = 'E0000000000001'
;
 

 

결과

ORG_NM      MANG_NM    PATH 본사                  박경철       /본사 서울지점           박수철       /본사/서울지점 논현매장           장희주       /본사/서울지점/논현매장 노원매장           조경모       /본사/서울지점/노원매장 목동매장           김희철       /본사/서울지점/목동매장 신촌매장           임원이       /본사/서울지점/신촌매장 천호매장           홍길동       /본사/서울지점/천호매장

 

고려 사항

순환관계를 전개하다 보면 몇가지 고려 사항이 있습니다. 상위 또는 하위 조인을 할 수 있는 컬럼이 필수적으로 필요합니다. 조인이나 WHERE 조건에 있는 컬럼은 인덱스를 고려해야 합니다.