PostgreSQL 계층형 쿼리 따라하기

PostgreSQL에서는 오라클에서 계층형 쿼리라고 표현하는 start with, connect by 구문을 지원하지 않는다. 따라서 PostgreSQL에서 데이터를 계층형의 트리 구조로 표현하기 위해 RECURSIVE 키워드를 사용하여 재귀적인 쿼리문을 작성할 수 있다.

실습을 위해 다음과 같이 테이블을 생성하고 데이터를 삽입하여 보자.

SQL> CREATE TABLE BOM( ITEM_ID INTEGER NOT NULL,
           PARENT_ID INTEGER,
           ITEM_NAME CHARACTER VARYING(20) NOT NULL,
           ITEM_QTY INTEGER,
          CONSTRAINT BOM_KEY PRIMARY KEY (ITEM_ID));

SQL> INSERT INTO BOM VALUES (1001, null, ‘컴퓨터’, 1);
           INSERT INTO BOM VALUES (1002, 1001, ‘본체’, 1);
           INSERT INTO BOM VALUES (1003, 1001, ‘모니터’, 1);
           INSERT INTO BOM VALUES (1004, 1001, ‘프린터’, 1);
           INSERT INTO BOM VALUES (1005, 1002, ‘메인보드’, 1);
           INSERT INTO BOM VALUES (1006, 1002, ‘렌카드’, 1);
           INSERT INTO BOM VALUES (1007, 1002, ‘파워’, 1);
           INSERT INTO BOM VALUES (1008, 1005, ‘RAM’, 1);
           INSERT INTO BOM VALUES (1009, 1005, ‘CPU’, 1);
           INSERT INTO BOM VALUES (1010, 1005, ‘그래픽카드’, 1);
           INSERT INTO BOM VALUES (1011, 1005, ‘기타장치’, 1);

생성된 테이블을 조회해 보면 다음과 같다.

생성된 데이터를 살펴 보면 자신의 ID와 함께 부모 ID가 표시되어 있음을 확인할 수 있다. 예를 들어 컴퓨터라는 항목은 자신의 ID가 1001이며, 부모 ID가 없기 때문에 최상위 품목이 된다. 본체와 모니터, 프린터는 각각의 식별 ID를 가지면서 부모 ID가 공통적으로 1001이기 때문에 컴퓨터의 하위 품목임을 확인할 수 있다. 이렇게 부모와 자식으로 데이터가 표현이 될 경우 트리구조로 데이터를 조회가 가능해 진다.

이제 WITH RECURSIVE 절을 사용하여 데이터를 조회해 보자.

SQL> WITH RECURSIVE search_bom(
            ITEM_ID, PARENT_ID, ITEM_NAME, ITEM_QTY, LEVEL) AS (
            SELECT  g.ITEM_ID, g.PARENT_ID, g.ITEM_NAME, g.ITEM_QTY, 0
            FROM BOM g
           WHERE g.PARENT_ID IS NULL
           UNIAL ALL
           SELECT g.ITEM_ID, g.PARENT_ID, g.ITEM_NAME, g.ITEM_QTY, LEVEL + 1
           FROM BOM g, search_bom sb
          WHERE g.PARENT_ID = sb.ITEM_ID)
          SELECT ITEM_ID, PARENT_ID, lpad('', LEVEL) || ITEM_NAME, ITEM_QTY, LEVEL
          FROM search_bom

위의 구문을 분석해 보자. WITH구문의 첫 번째 SELECT 문장은 오라클 계층형 쿼리의 START WITH에 해당 한다. 즉, 데이터의 ROOT를 구하는 부분이다. UNIAN ALL 다음의 SELECT문은 하위 데이터를 찾아가기 위한 반복 구문이 된다. 여기서 WHERE 조건은 찾은 레코드의 부모 ID와 아이템 ID가 같다는 조건을 주었다.
추가적으로 데이터의 레벨을 시각적으로 식별할 수 있다록 lpad 함수를 사용하여 공백을 입력 하였다. 이에 대한 결과는 다음과 같다.

그런데 뭔가 조금 이상하다. 데이터가 레벨로 정렬이 되어 부모와 자식간의 관계를 나타내는 트리 구조로 표현이 되지 않았다. 이 부분을 해결하기 위해 ID를 PATH 배열에 추가하여 이 PATH를 기준으로 정렬해 주어야 한다. 말로는 설명이 어렵다. 실제 다음 쿼리 문을 실행해 보자.

SQL> WITH RECURSIVE search_bom(ITEM_ID, PARENT_ID, ITEM_NAME, ITEM_QTY,
           LEVEL, PATH, CYCLE) AS (
           SELECT  g.ITEM_ID, g.PARENT_ID, g.ITEM_NAME, g.ITEM_QTY, 0, ARRAY
           [g.ITEM_ID], false
           FROM BOM g
          WHERE g.PARENT_ID IS NULL
          UNION ALL
          SELECT g.ITEM_ID, g.PARENT_ID, g.ITEM_NAME, g.ITEM_QTY, LEVEL + 1,
          PATH || g.ITEM_ID, g.ITEM_ID = ANY(PATH)
          FROM BOM g, search_bom sb
         WHERE g.PARENT_ID = sb.ITEM_ID AND NOT CYCLE)
          SELECT ITEM_ID, PARENT_ID, lpad('', LEVEL) || ITEM_NAME, ITEM_QTY, LEVEL,
          PATH
          FROM search_bom ORDER BY PATH

이에 대한 결과는 다음과 같다.

 PATH 배열에 검색된 ITEM_ID 값을 추가하여 이를 기준으로 데이터를 정렬한 결과 이다. 이로써 우리가 원하는 계층형 데이터를 조회하는 쿼리가 완성 되었다.

그런데 여기서 CYCLE 이라는 키워드가 추가 되었다. 이는 RECURSIVE를 통한 재귀쿼리 수행시 성능 상의 문제를 해결하기 위함 이다. WITH 구문 수행 시 CYCLE의 값을 false로 초기화 하였다. 그리고 UNION ALL 다음의 SELECT 구문이 수행되면 CYCLE이 false 이기 때문에 SELECT문이 수행이 되고 검색된 자식 node의 ITEM_ID 값이 배열에 추가된다. 이때 배열에는 부모 ID와 자신의 ID 값이 들어 간다. 그리고 "ANY(PAHT)"에 의해 PAHT 배열에 자신의 ITEM_ID값이 있는지를 검사한다. 검사 결과 이미 찾은 값에 대해 서는 더 이상 데이터 검색을 수행하지 않게 되어 중복 처리를 하지 않게 된다. 물론 이러한 방법을 동원한다 하더라도 오라클의 계층형 쿼리 성능에는 미치지 못하겠지만, 이른 통해 최소한의 성능 향상을 도모할 수 있을 것이다.