[오라클(Oracle)] 계층형 쿼리

계층형 쿼리란 테이블에 계층형 데이터가 존재하는 경우 이를 조회하기 위한 쿼리이다. SQL-D 시험 공부를 할 때, 정말 읽는 것만으로는 이해가 안되었던 부분이 계층형 쿼리였다. 따라서 글을 쓰면서 다시 정리해본다.

1. 계층형 데이터

계층형 데이터란 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말한다.
예시로 들어볼 하드웨어의 계층적 구조이다. 다음과 같이 상위와 하위 데이터가 포함된 데이터를 계층형 데이터라고 한다. 예를 들어, 중앙처리장치와 기억장치, 입력장치의 상위 데이터는 하드웨어이며, 하드웨어의 하위 데이터는 위의 세 개이다.

이를 테이블로 나타내면 다음과 같은 형태로 나타낼 수 있다.

2. 계층형 쿼리의 형태

START WITH 조건
CONNECT BY [NOCYCLE] 조건
[ORDER SIBLINGS BY 컬럼];

계층형 쿼리는 다음과 같은 형식으로 쓰인다. [] 안의 내용은 필수적으로 들어가지 않아도 되는 내용이다. 이 구문에 대해서 설명을 해보자면

START WITH 절은 그 이름에서도 추측할 수도 있듯이 계층 구조 전개의 시작 위치를 지정한다. 예를 들어 위 테이블에서 START WITH 상위장치 IS NULL을 하면 1행을 시작위치로 지정하는 것이다.
CONNECT BY 절은 다음에 전개될 자식 데이터를 지정하는데, PRIOR를 주로 사용해서 지정한다. 이때 PRIOR 자식 = 부모 형태를 쓸 경우 순방향 전개, PRIOR 부모 = 자식 형태를 쓸 경우 역방향 전개를 한다. 위 테이블을 예로 들자면 PRIOR 장치이름 = 상위장치의 경우 순방향 전개, PRIOR 상위장치 = 장치이름의 경우 역방향 전개이다.
CONNECT BY 절에서 []친 NOCYCLE은 데이터를 전개하면서 이미 나타난 데이터가 또 나타는 것을 방지해준다.
ORDER SIBLINGS BY 절은 형제 노드(동일 LEVEL) 사이에서 정렬을 수행해준다.

3. 계층형 쿼리의 예시

위의 설명만으로는 이게 뭔소린가...? 하는 생각이 들 것이다. 직접 테이블을 만들고 계층형 쿼리를 써보며 이해를 해보자.

CREATE TABLE TAB1 (
    장치이름 VARCHAR2(20),
    상위장치 VARCHAR2(20) );
    
INSERT INTO TAB1 VALUES ('하드웨어', NULL);
INSERT INTO TAB1 VALUES ('중앙처리장치', '하드웨어');
INSERT INTO TAB1 VALUES ('기억장치', '하드웨어');
INSERT INTO TAB1 VALUES ('입출력장치', '하드웨어');
INSERT INTO TAB1 VALUES ('입력장치', '입출력장치');
INSERT INTO TAB1 VALUES ('출력장치', '입출력장치');

SELECT * FROM TAB1;

컬럼 이름은 보기 편하도록 그냥 한글이름을 사용하였다. 이 sql문을 실행하면 위에서 보았던 테이블과 같은 형태가 된다. 먼저 순방향 전개문을 먼저 실행시켜보자.

SELECT 장치이름, 상위장치, LEVEL FROM TAB1
START WITH 상위장치 IS NULL
CONNECT BY PRIOR 장치이름 = 상위장치;

계층형 쿼리가 어떤 식으로 전개되는지 보기 편하도록 LEVEL이라는 가상 컬럼을 사용하였다. LEVEL은 루트 데이터는 1, 그 하위 데이터는 2, 그 하위 데이터의 하위 데이터는 3... 이런식으로 리프 데이터까지 1씩 증가한다. 이해가 되지 않는 다면 아래 그림을 참고하는 것이 좋을 것이다.

위 SQL문을 하나하나 살펴보자면 START WITH 상위장치 IS NULL을 통해 하드웨어를 계층형 전개의 시작점으로 사용하였다. 이때 시작점은 루트 데이터로 LEVEL은 1이다. 이제 CONNECT BY PRIOR 장치이름 = 상위장치 를 통해 자식 데이터를 전개한다.

다음과 같이 이전 계층의 장치이름(하드웨어)를 상위장치로 가지고 있는 기억장치, 입출력장치, 중앙처리장치는 LEVEL 2가 된다.

마지막으로 이전 계층의 장치이름(입출력장치)를 상위장치로 가지고 있는 입력장치, 출력장치가 LEVEL 3이 된다.

SELECT 장치이름, 상위장치, LEVEL FROM TAB1
START WITH 상위장치 = '입출력장치'
CONNECT BY PRIOR 상위장치 = 장치이름;

반대로 PRIOR 상위장치 = 장치이름을 써서 역방향 전개를 통해 다음과 같은 결과를 생성해낼 수도 있으며

SELECT 장치이름, 상위장치, LEVEL FROM TAB1
START WITH 상위장치 IS NULL
CONNECT BY PRIOR 장치이름 = 상위장치
ORDER SIBLINGS BY 장치이름 DESC;

ORDER SIBLINGS BY 절을 이용해 같은 레벨의 노드 간에 정렬 순서를 정해줄 수도 있다.

4. 참조

데이터 전문가 지식포털 DBGuide.net, http://www.dbguide.net/db.db?cmd=view&boardUid=148202&boardConfigUid=9&categoryUid=216&boardIdx=135&boardStep=1