[ oracle 10g ] START WITH ... CONNECT BY 절을 사용한 계층형쿼리

START WHIT ... CONNECT BY 절

오라클 같은 관계형 DBMS 에서 관계형 (Relation) 이란
계층형 (Hierarchical) 과는 상반되는 개념이다.

관계형이란 말은 서로 평등하고 수평적인 관계를 의미하는 반면에,
계층형이란 구조는 계급적이고 수직적인 관계를 가진다.

관계형 데이터베이스는 2차원 테이블 구조에 의해 모든 데이터를 저장한다.
하지만 실무에서는 기업의 조직도나 게시판 구조와 같은 계층적인 데이터 구조를
많이 사용하고 있다.

따라서 평면적인 구조를 가지는 테이블에서도 계층적인 데이터를 조회할 수 있는
방법이 필요하다.

특히, 게시판 구조에서 특정 게시물의 답글이나 답글의 답글 등을 구현하고자 할 때,
복잡한 연산을 필요로 한다.

평면적인 데이터 구조를 수직적인 구조로 표현하는 데 SQL 만으로는
한계가 있기 때문에 오라클에서는 이러한 계층적인 정보를 표현할 수 있도록
특별한 문장을 지원하는 데 그것이 바로

START WITH ... CONNECT BY

절이다.

문법)

[[START WITH 조건 1] CONNECT BY 조건 2]

* START WITH 조건 1
  START WITH 절은 계층 관계가 시작되는 루트 행을 지정하기 위한 것으로, 
  START WITH 절 다음에 계층 관계를 검색하기 위한 조건 식을 포함한다. 
  만약, START WITH 절 다음에 위치하는 조건 식이 생략되면 대상 테이블 내에 
  존재하는 모든 행을 루트 행으로 간주한다. 
  요약)
  1. 루트 노드를 식별한다. 조건 1을 만족하는 모든 ROW 들이 루트 노드가 된다.
  2. START WITH 절을 생략할 수도 있는데 이 때는 모든 ROW 가 루트노드로 간주된다.
  3. 조건 1 에는 서브 쿼리가 올 수 있다.

* CONNECT BY 조건 2 
  부모와 자식노드들 간의 관계를 명시하는 부분이다.
  조건 2에는 반드시 PRIOR 연산자를 포함시켜야 한다.
  PRIOR 연산자는 부모노드의 컬럼을 식별하는 데 사용한다.
  조건 2 에는 서브쿼리가 올 수 없다.



계층형 쿼리의 로직

다음과 같은 테이블에 대해서 계층형 쿼리가 실제로 어떻게 처리되는지 보자.

CREATE TABLE BOM (                   ITEM_ID INTEGER NOT NULL,              PARENT_ID INTEGER,              ITEM_NAME VARCHAR2(20) NOT NULL,              ITEM_QY INTEGER,              PRIMARY KEY(ITEM_ID) }

ITEM_ID	PARENT_ID	ITME_NAME	ITEM_QTY
1001	NULL	컴퓨터	1
1002	1001	본체	1
...	...	...	...
1004	10001	프린터	1
1006	10002	랜카드	1
...	...	...	...

첫번째 
     가장 상위에 있는 루트노드에 해당하는 항목이 무엇인지 알아낸다. 
     PARENT_ID 가 NULL 인 항목이 가장 상위 품목이므로 START WHITH 절 다음에 
     루트노드를 식별하는 구분인 PARENT_ID IS NULL 을 추가한다. 

START WITH PARENT_ID IS NULL

두번째
    각각의 항목들 간에 부모와 자식노드를 식별해야 한다.
    부모와 자식노드들 간의 관계를 연결하는 부분이 바로 CONNECT BY 절이다.
    가장 상위 품목을 제외하고 다른 모든 품목들은 PARENT_ID 컬럼에 상위항목의 값
    (즉, 부모노드의 ITEM_ID)을 가지고 있다.
    이것을 조건으로 표현하면 PARENT_ID = ITEM_ID 이다. 
    그리고 PARENT_ID 는 부모노드인 ITEM_ID 와 연결되므로 PRIOR 키워드는
    ITEM_ID 앞에 붙는다.

CONNECT BY PRIOR ITME_ID = PARENT_ID

PRIOR 연산자 
 
A. CONNECT BY 조건 식은 한 쌍의 행에 대한 상-하 게층 관계 (부모-자식 관계)를 정의하기
    위한 것으로, 조건 식 내에서 하나는 부모(PARENT)로 지정되고, 다른 하나는 자식(CHILD)으로
    지정된다. 이처럼 행 간의 부모-자식 간 계층 관계를 정의하기 위하여 CONNECT BY 조건식
    내에 PRIOR 연산자를 이용하여 부모 행의 컬럼 값을 지정한다.
    즉, 부모 행의 컬럼 값과 같은 컬럼 값을 가지는 모든 행은 자식 행이 된다.

요약)
오직 계층형 쿼리에서만 사용하는 연산자이다.
CONNECT BY 절에서 해당 컬럼의 부모 ROW를 식별하는 데 사용된다.
앞의 로직에서 본체의 PARENT_ID 컬럼에는 컴퓨터의 ITEM_ID 값을 가지고 있으므로
PRIOR 연산자가 ITEM_ID 앞에 붙게 된다.


NOCYCLE

A. CONNECT BY 절의 조건 식에 따른 계층 질의 결과는 루프를 포함할 수 있으며,
    이것은 계층 트리를 생성할 때 무한 루프를 발생시키는 원인이 될 수 있다.
    따라서, 루프를 발견하면 기본적으로 오류를 반환하고, 특수 연산자인 NOCYCLE이
    CONNECT BY 절에 명시된 경우에는 오류를 발생 시키지 않고 해당 루프에 의해
    검색된 결과를 출력한다.

B. CONNECT BY 절에서 NOCYCLE이 명시되지 않은 계층 질의문을 수행 중에 루프가 
    감지되는 경우, 오류를 반환하고 해당 질의문을 취소한다. 반면, NOCYCLE이 명시된
    계층 질의문에서 루프가 감지되는 경우, 오류를 반환하지는 않지만 루프가 감지된 행에
    대해 CONNECT_BY_ISCYCLE 값을 1로 설정하고, 더 이상 계층 트리의 검색을
    확장하지 않는다.

레벨 의사 컬럼

계층형 정보를 표현할 때의 레벨을 나타낸다.
루트 노드가 1 LEVEL, 루트의 자식노드가 2 LEVEL, 그 자식 노드가 3 LEVEL 이된다.

예시)
   SELECT LPAD('',2*(LEVEL-1)) || ITEM_NAME ITEM_NAMES
   FROM BOM
   START WITH PARENT_ID IS NULL
   CONNECT BY PRIOR ITEM_ID = PARENT_ID


계층형 쿼리의 정렬

계층형 쿼리에는 이미 레벨과 부모-자식관계를 고려해서 순서를 결정하는 로직이 숨어있다.
그러니 굳이 ORDER BY 절을 사용하지 않더라도 계층과 레벨에 따라 스스로 알아서
ROW들을 정렬한다.

따라서, 계층형 쿼리에서 ORDER BY 절을 사용하는 것은 오라클 SQL 엔진이 레벨에 따라서
순서를 맞추어 정렬한 결과를 또다시 정렬하는 셈이 된다.

그러니 굳이 필요한 경우가 아니라면 계층형 쿼리에서 ORDER BY 절은 사용하지 않는 것이 좋다.


ORDER SIBILINGS BY 절

ORDER SIBILINGS BY 절은 계층 질의 결과 값들의 계층 정보를 유지하면서 특정 컬럼을
기준으로 오름차순 또는 내림차순으로 데이터를 정렬하기 위해 사용할 수 있는 절로서,
동일한 부모를 가진 자식 행들을 정렬할 수 있다.

이처럼 계층적 질의 문에서 데이터의 계층적 순서를 파악하기 위해 사용되는
ORDER BY 절의 구문은 다음과 같다.

구문

ORDER SIBLINGS BY col_1 [ASC | DESC] [, col_2[ASC | DESC][, ... [,col_n[ASC | DESC]] ... ]]

계층 질의 결과는 기본적으로 ORDER BY 절에 명시된 컬럼 리스트를 따라 정렬된 부모와
그 부모의 자식 노드들이 연속적으로 출력되며, 동일 부모를 가지는 형제는 명시된 정렬
순서에 따라 정렬되어 출력된다.