행위

SQL CONNECT BY

DB CAFE

thumb_up 추천메뉴 바로가기


1 CONNECT_BY[편집]

WHERE 절 
: 데이터를 가져온 뒤 마지막으로 조건절에 맞게 정리
START WITH 절
: 어떤 데이터로 계층구조를 지정하는지 지정
CONNECT BY 절
: 각 행들의 연결 관계를 설정
   PRIOR : CONNECT BY 절에 사용되며 PRIOR에 지정된 컬럼이 맞은편 컬럼을 찾아갑니다.
           CONNECT BY PRIOR 자식 컬럼 = 부모 컬럼        
                      : 부모 → 자식 순방향 전개
                      : 이전 행의 자식컬럼 값이 현재 행의 부모컬럼 값인 행을 찾아라
           CONNECT BY PRIOR 부모 컬럼 = 자식 컬럼 
                      : 자식 → 부모 역방향 전개
                      : 이전 행의 부모컬럼 값이 현재 행의 자녀컬럼 값인것을 찾어라 

ORDER SIBLINGS : 계층형 쿼리에서 정렬을 수행

  • START WITH 는 가장 처음에 데이터를 거르는 플랜을 타게 되고, 따라서 이 컬럼에는 인덱스가 걸려있어야 성능을 보장받습니다.
  • CONNECT BY 절의 결과에는 LEVEL 이라는 컬럼이 있으며, 이는 계층의 깊이를 의미합니다.
  • 계층 전개(LEVEL)가 깊을수록 많은 시간이 소요될 수 있다.
  • dual connect by 성능 문제 발생시 머지조인을 검토 하라
SELECT /*+ LEADING(A) USE_MERGE(B) */
       COUNT (*)
  FROM t1 a
     , (SELECT LEVEL AS lv 
          FROM DUAL 
       CONNECT BY LEVEL <= 100) b
 WHERE b.lv <= a.c1;

1.1 CONNECT_BY_ROOT[편집]

계층형 쿼리에서 최상위 노드를 찾고자 할 경우

SELECT LEVEL           
     , LPAD(' ' ,2*(LEVEL-1)) || NAME AS NAME                
     , CONNECT_BY_ROOT NAME AS ROOT_NAME                 FROM  TEST_TABLE_ONE            
WHERE LEVEL>=2                
START WITH PP_LEVEL IS NULL               
CONNECT BY PRIOR P_LEVEL=PP_LEVEL

1.2 CONNECT_BY_ISCYCLE 가상컬럼[편집]

  1. CONNECT_BY_ISCYCLE 역시 계층형 쿼리에서 사용되는 가상 컬럼의 한 종류
  2. 해당 로우의 항목이 자식노드를 갖고 있는데 동시에 그 자식노드가 해당 로우의 부모노드가 되는지를 판별하는 가상 컬럼이다.
  3. 즉 중복 참조를 하는 경우 이를 판별하여 중복 참조를 하는 자식 노드가 있을 경우 1을, 없을 경우 0을 반환한다.

The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which is also its ancestor. Otherwise it returns 0.

You can specify CONNECT_BY_ISCYCLE only if you have specified the NOCYCLE parameter of the CONNECT BY clause. NOCYCLE enables Oracle to return the results of a query that would otherwise fail because of a CONNECT BY loop in the data.

SELECT   item_id, LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || item_name item_names
      FROM   bom
START WITH   item_id = 1005
CONNECT BY   PRIOR item_id = parent_id;

1.3 CONNECT_BY_ISLEAF 가상컬럼[편집]

계층형 쿼리에서 해당하는 로우가 자식노드가 있는지 없는지 여부를 체크

  • 자식노드가 있을 경우 0 , 자식노드가 없을 경우 1
SELECT last_name "Employee", CONNECT_BY_ISLEAF "IsLeaf",
       LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
  FROM employees
  WHERE LEVEL <= 3 AND department_id = 80
  START WITH employee_id = 100
  CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4
  ORDER BY "Employee", "IsLeaf";

1.4 LEVEL 가상컬럼[편집]

For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root row, 2 for a child of a root, and so on. A root row is the highest row within an inverted tree. A child row is any nonroot row. A parent row is any row that has children. A leaf row is any row without children. Figure 3-1 shows the nodes of an inverted tree with their LEVEL values.

sqlrf001.gif

1.5 SYS_CONNECT_BY_PATH()[편집]

계층적인 쿼리를 실행할 때 루트에서 끝 노드까지 전체 경로를 분리 문자로 구분해 줌.

형식 : sys_connect_by_path( <열>,<분리문자> )

select lpad(' ',(level-1)*2,' ')||직원 직원
      , 직급
      , Sys_connect_by_path(직원,'-') 경로
  from 직원
 start with 직원 = '개똥'
connect by 직속상사 = prior 직원 
order siblings by 직원

1.6 테스트[편집]

create table t1
(
     parent_c varchar2(1)
    ,child_c varchar2(1)
);
insert into t1
select 'a','b' from dual
union all 
select 'b','c' from dual
union all 
select 'a','c' from dual
union all 
select 'c','d' from dual
union all 
select 'c','e' from dual
union all 
select 'e','f' from dual;
commit;

[테스트 데이터 조회]

select parent_c as p, child_c as c 
from t1;

[실행 결과]

P  C
-- --
a  b
b  c
a  c
c  d
c  e
e  f

[부모→자식 순방향 전개 계층형 쿼리 예제]

select parent_c as p, child_c as c, level 
from t1
start with parent_c = 'a'
connect by prior child_c = parent_c;

[실행 결과]

P  C       LEVEL
-- -- ----------
a  b           1 <- 첫번째 부모행을 시작으로 자식 탐색
b  c           2
c  d           3
c  e           3
e  f           4 <- 첫번째 부모행의 자식 탐색 종료
a  c           1 <- 두번째 부모행의 자식 탐색 시작
c  d           2
c  e           2
e  f           3 <- 두번째 부모행의 자식 탐색 종료


[자식 → 부모 역방향 전개 계층형 쿼리 예제]

select parent_c as p, child_c as c, level 
from t1
start with child_c = 'f'
connect by child_c = prior parent_c;

[실행 결과]

P  C       LEVEL
-- -- ----------
e  f           1 <- 첫번째 자식행을 시작으로 부모 탐색
c  e           2
a  c           3
b  c           3
a  b           4 <- 첫번째 자식행의 부모 탐색 종료

1.7 쉽게 계층쿼리 만들기[편집]

1) 간단하게 SQL 작성

SELECT * 
  FROM EMP

2) 최초 시작행을 정하자. => START WITH 사용 'MGR IS NULL'은 관리자가 없는것 우선 순위로

SELECT *
  FROM EMP
 START WITH MGR IS NULL -- 시작이 없으면 생략 가능
CONNECT BY 절

3) CONNECT BY 절 작성

SELECT EMPNO, ENAME, MGR
  FROM EMP
 START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR

-- PRIOR EMPNO = MGR 은 이전행의 EMPNO와 현재행의 MGR이 같은 행을 찾아라

  • 이런식으로 마지막 값까지 찾은 후 더 이상 하위계층이 없으면 상위로 올라가서 하위계층값을 찾음
  • 최종적으로 모든 행이 찾아질때까지 반복합

1.8 사용예제[편집]

SELECT *
  FROM DUAL A
 CROSS JOIN (SELECT LEVEL LV 
               FROM DUAL 
            CONNECT BY LEVEL <= 7) B;
[결과]
DUMMY LV
----- --
X     1
X     2
X     3
X     4
X     5
X     6
X     7

1.8.1 원하는 기간 리턴 (일자만큼)[편집]

SELECT V_DATE
  FROM (SELECT TO_CHAR(TO_DATE('20140201', 'YYYYMMDD') + LEVEL - 1, 'YYYYMMDD') V_DATE 
          FROM DUAL
       CONNECT BY LEVEL <= TO_DATE('20140314', 'YYYYMMDD') - TO_DATE('20140201', 'YYYYMMDD')+1
     );

1.8.2 원하는 기간 리턴 (월별)[편집]

SELECT V_DATE
  FROM (SELECT TO_CHAR(ADD_MONTHS(TO_DATE('201401','YYYYMM'), LEVEL-1),'YYYYMM') AS V_DATE
          FROM DUAL 
       CONNECT BY LEVEL <= MONTHS_BETWEEN(TO_DATE(TO_CHAR(SYSDATE,'YYYYMM'),'YYYYMM'),TO_DATE('201401','YYYYMM'))+1);

1.8.3 현재달 날짜 구하기[편집]

SELECT TRUNC(SYSDATE, 'month')   + (LEVEL - 1)  AS TDAY
  FROM DUAL
CONNECT BY LEVEL <=  (LAST_DAY(SYSDATE) - TRUNC (SYSDATE, 'month')  + 1);