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

START WITH ... CONNECT BY 절

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

따라서 SQL을 사용해서 이러한 계층형 정보를 표현하기가 매우 어렵다.

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

평면적인 데이터 구조를 수직적인 구조로 표현하는데 SQL 만으로는 한계가 있기 때문에

오라클에서는 이러한 계층적인 정보를 표현할 수 있도록 특별한 문장을 지원하는데 그것이 바로

START WITH ... CONNECT BY 절이다.

문법)

[[START WITH 조건 1] CONNECT BY 조건 2] START WITH 조건 1 : 루트 노드를 식별한다. 조건 1을 만족하는 모든 ROW들이 루트노드가 된다. START WITH 절을 생략할 수도 있는데 이 때는 모든 ROW 가 루트노드로 간주된다. 조건 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_QTY INTEGER, PRIMARY KEY (ITEM_ID))

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

첫번째

가장 상위에 있는 루트노드에 해당하는 항목이 무엇인지 알아낸다.

PARENT_ID 가 NULL 인 항목이 가장 상위 품목이므로 START WITH 절 다음에 루트노드를 식별하는 구분인

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 ITEM_ID = PARENT_ID

PRIOR 연산자

오직 계층형 쿼리에서만 사용하는 연산자이다. CONNECT BY 절에서 해당 컬럼의 부모로우를 식별하는데 사용된다.

앞의 로직에서 본체의 PARENT_ID 컬럼에는 컴퓨터의 ITEM_ID 값을 가지고 있으므로 PRIOR 연산자가 ITEM_ID 앞에 붙게 된다.

레벨 의사컬럼

계층형 정보를 표현할때의 레벨을 나타낸다.

루트 노드가 1LEVEL , 루트의 자식노드가 2LEVEL , 그 자식 노드가 3LEVEL 이 된다.

ex)

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 절을 사용하지 않더라도 계층과 레벨에 따라 스스로 알아서 로우들을 정렬한다.

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

또다시 정렬하는 셈이 된다.

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