[Oracle] 계층형 쿼리(START WITH ~ CONNECT BY)

계층적 쿼리 (Hierarchical Queries)

START WITH ~ CONNECT BY

 

계층 쿼리를 사용하는 예)

 회사에서 개발팀, 기획팀이 있다고 하면

개발팀에는 개발 1팀, 개발 2팀 / 기획팀에는 기획 1팀, 기획 2팀과 같이 하위 팀들이 있다.

자식노드에 관한 정보를 나타내기 위해서는 계층적 쿼리를 사용하여 화면에 아래와 같이 보여줄 수 있다.

개발팀

   |

   |ㅡㅡㅡㅡ개발 1팀

   |                |

   |                |ㅡ 개발 1팀( 기능 )

   |

   |ㅡㅡㅡㅡ개발 2팀

---

계층적 쿼리의 형태를 보자.

 

START WITH condition CONNECT BY NOCYCLE condition

* START WITH condition - condition에는 root row(s) 가 명시되어야 한다. ex) 개발팀
* CONNECT BY - CONNECT BY뒤에는 root row(s)와 child row(s)의 관계를 명시해야한다. ex) 개발팀 > 개발 1팀

---

* PRIOR 연산자 ( 상위 행의 컬럼임을 보여줌 )

 

* 계층 조회는 condition에서 단 하나 뿐인 부모 행을 참조하기 위해 PRIOR 연산자로 한정할 필요가 있다.

... PRIOR expr = expr
or
... expr = PRIOR expr

위와 같은 예제에서 CONNECT BY PRIOR 개발팀 = 개발 1팀 이렇게 사용하면, 개발 1팀에서 개발팀 (아래 -> 위)으로  역행하는 트리가 구성된다. ( 개발 1팀의 부모를 찾자 )

반대로, CONNECT BY PRIOR 개발 1팀 = 개발팀 이렇게하면, 개발팀아래에 있는 부속팀들을 찾자.

 

* CONNECT BY 조건이 여러개인 경우는 PRIOR 연산자를 필요로하는 조건은 1개뿐이지만, PRIOR 조건을 여러 개 지정할 수도 있다.

CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id ...
CONNECT BY PRIOR employee_id = manager_id and 
           PRIOR account_mgr_id = customer_id ...

---

* 참고

---

* PRIOR는 단항 연산자이며 단항 + 및 - 산술 연산자와 우선 순위가 같다.
* [단항 연산자(참,거짓 / 부호변경 / 값증감)에 +,- 는 부호를 말하며 산술 연산자의 +,-는 사칙연산을 말한다.]
* PRIOR는 항등 연산자로 열 값을 비교할 때 가장 일반적으로 사용된다.
* [항등 연산자 :  =, == 와 ===]
* CONNECT BY 절에 항등 연산자(=) 이외의 다른 연산자도 이론상으로는 가능하다.
* 하지만, 다른 연산자를 사용할 경우, Oracle은 무한반복을 초래 할 수 있으니 항등 연산자를 사용하길 권고한다.

---

Oracle의 계층 질의 처리 방법

 

* 결합이 존재하는 경우 결합이 FROM절에 지정되어 있는지 WHERE절로 지정되어 있는지에 관계없이 CONNECT BY절이 먼저 평가된다.
* CONNECT BY 조건이 평가된다 -> 나머지 WHERE절이 평가된다.

---

Oracle은 이러한 평가의 정보를 사용하여 다음과 같이 계층을 형성한다.

 

* Oracle은 계층 구조의 루트 행 (START WITH 뒤에 올 행)을 선택한다.  1단계
* Oracle은 각 루트 행의 자식 행을 선택한다.     2단계
* 자식 행은 하나의 루트 행에 대해 CONNECT BY 조건을 충족해야한다.  2단계
* Oracle은 연속 된 세대의 자식 행을 선택한다.  3단계
* Oracle은 먼저 2 단계에서 반환 된 행의 자식을 선택한 다음 그 자식의 자식을 선택한다. 3단계
* 쿼리에 결합되지 않은 WHERE 절이 포함되어있는 경우 Oracle은 WHERE 절 조건을 충족하지 않는 모든 행을 계층으로 부터 삭제한다.   4단계
* 단, Oracle이 조건을 충족하지 않는 행의 모든 ​​자식을 제거하는 것이 아니라 각 행에 대해 개별적으로 조건에 대해 평가한다.  4단계

 

 

위의 계층 구조를 보자.

 

* 부모 행의 자식을 찾기 위해, Oracle은 부모 행에 대한 CONNECT BY 조건 PRIOR 식을 평가한다. 
* 조건이 참인 행은 부모의 자식이다.
* CONNECT BY 조건 쿼리에서 다른 조건을 포함할 수 있다.
* CONNECT BY 조건에는 서브 쿼리를 사용할 수 없다.
* START WITH는 서브쿼리를 사용할 수 있다.

---

* 예외

---

 

* CONNECT BY 조건에 따라 계층 구조에서 루프가 발생한 경우 Oracle은 오류를 반환한다.
* 행이 다른 행의 부모 (조부모 또는 직계 조상)와 자식 (또는 손자 또는 직계 후손) 모두 인 경우 루프가 발생한다.

 

* 주의할 점

---

 

* 계층 쿼리에서는 ORDER BY 또는 GROUP BY를 모두 지정하면 안된다.
* CONNECT BY 결과의 계층 순서가 손상 때문

---

이제 예제를 보며 정리해 보자.

* 예제

1) 다음 계층 쿼리는 CONNECT BY 절을 사용하여 직원과 관리자 사이의 관계를 정의한 예제다.

SELECT employee_id, last_name, manager_id
   FROM employees
   CONNECT BY PRIOR employee_id = manager_id;

EMPLOYEE_ID LAST_NAME                 MANAGER_ID
----------- -------결 과 값--------- ----------
        101 Kochhar                          100
        108 Greenberg                        101
        109 Faviet                           108
        110 Chen                             108
        111 Sciarra                          108
        112 Urman                            108
        113 Popp                             108
        200 Whalen                           101

=> employee_id 와 manager_id가 동일레벨 일 경우를 보여준다.

상위 manager_id에서 하위 employee_id로 흐르는 트리

 

 

2) 다음 계층 쿼리는 계층 구조의 루트 행을 지정하기위한 START WITH 절과 계층정렬을 하기 위한 SIBLINGS 키워드를 사용하고 ORDER BY 절을 추가한 예제다.

SELECT last_name, employee_id, manager_id, LEVEL
      FROM employees
      START WITH employee_id = 100
      CONNECT BY PRIOR employee_id = manager_id
      ORDER SIBLINGS BY last_name;

LAST_NAME                 EMPLOYEE_ID MANAGER_ID      LEVEL
------------------------- ----------- ---------- ----------
King                              100                     1
Cambrault                         148        100          2
Bates                             172        148          3
Bloom                             169        148          3
Fox                               170        148          3
Kumar                             173        148          3
Ozer                              168        148          3
Smith                             171        148          3
De Haan                           102        100          2
Hunold                            103        102          3
Austin                            105        103          4

=> START WITH에 employee_id 가 100인 루트노드 행을 지정하고 employee_id와 manager_id가 동일 레벨일 경우 last_name이 먼저인 것부터 정렬했다.

상위 manager_id에서 하위 employee_id로 흐르는 트리

 

---

 

참고 링크

https://docu94.tistory.com/69

[Oracle] 계층적 쿼리 SYS_CONNECT_BY_PATH

https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm#i2053935

Hierarchical Queries

도움이 되셨으면 공감과 광고 한번 씩 눌러주시면 감사합니다!!