클로저 테이블 #2

지난 글에서 살펴본 클로저 테이블에 부모 노드 속성을 추가해보자. 추가 속성으로 인한 부하가 발생하지만, 조인하지 않고 부모 노드를 조회할 수 있다. 글의 전개 방식은 지난 글과 동일하다.

예제를 위해 아래와 같이 테이블을 생성하자.

-- 1
DROP TABLE tree PURGE;

CREATE TABLE tree (
    anc NUMBER -- 조상
  , dec NUMBER -- 자손
  , par NUMBER -- 부모
  , dep NUMBER -- 깊이
  , CONSTRAINT tree_pk PRIMARY KEY (anc, dec));

INSERT INTO tree VALUES (1, 1, NULL, 0);
INSERT INTO tree VALUES (1, 2, 1   , 1);
INSERT INTO tree VALUES (1, 3, 2   , 2);
INSERT INTO tree VALUES (1, 4, 1   , 1);
INSERT INTO tree VALUES (1, 5, 4   , 2);
INSERT INTO tree VALUES (1, 6, 4   , 2);
INSERT INTO tree VALUES (1, 7, 6   , 3);
INSERT INTO tree VALUES (2, 2, NULL, 0);
INSERT INTO tree VALUES (2, 3, 2   , 1);
INSERT INTO tree VALUES (3, 3, NULL, 0);
INSERT INTO tree VALUES (4, 4, NULL, 0);
INSERT INTO tree VALUES (4, 5, 4   , 1);
INSERT INTO tree VALUES (4, 6, 4   , 1);
INSERT INTO tree VALUES (4, 7, 6   , 2);
INSERT INTO tree VALUES (5, 5, NULL, 0);
INSERT INTO tree VALUES (6, 6, NULL, 0);
INSERT INTO tree VALUES (6, 7, 6   , 1);
INSERT INTO tree VALUES (7, 7, NULL, 0);
COMMIT;

현재 트리 구조는 아래와 같다.

1 - 2 - 3
    4 - 5
        6 - 7

아래 쿼리는 4번 노드의 하위 노드를 조회한다. 부모 노드 속성을 관리하므로 조인이 불필요하다.

-- 2-1 : 하위 노드 조회
SELECT * FROM tree WHERE anc = 4;

ANC DEC PAR DEP
--- --- --- ---
  4   4       0
  4   5   4   1
  4   6   4   1
  4   7   6   2

4 행이 선택되었습니다.

아래 쿼리는 7번 노드의 상위 노드를 조회한다. 부모 노드를 조회하기 위해 LEAD 함수를 사용했다.

-- 2-2 : 상위 노드 조회
SELECT anc, dec, LEAD (anc) OVER (ORDER BY dep) AS par, dep
  FROM tree
 WHERE dec = 7;

ANC DEC PAR DEP
--- --- --- ---
  7   7   6   0
  6   7   4   1
  4   7   1   2
  1   7       3

4 행이 선택되었습니다.

아래 패키지로 입력, 수정, 삭제를 수행할 수 있다.

-- 3
CREATE OR REPLACE PACKAGE pkg_tree
IS
    PROCEDURE prc_ins (i_cur NUMBER, i_par NUMBER);
    PROCEDURE prc_del (i_cur NUMBER);
    PROCEDURE prc_upd (i_cur NUMBER, i_par NUMBER);
END pkg_tree;
/

CREATE OR REPLACE PACKAGE BODY pkg_tree
IS
    -- 입력
    PROCEDURE prc_ins (i_cur NUMBER, i_par NUMBER)
    IS
    BEGIN
        INSERT
          INTO tree
              (anc, dec, par, dep)
        SELECT i_cur AS anc
             , i_cur AS dec
             , NULL AS par
             , 0 AS dep
          FROM DUAL
        UNION ALL
        SELECT anc
             , i_cur AS dec
             , i_par AS par
             , dep + 1 AS dep
          FROM tree
         WHERE dec = i_par;
    END prc_ins;

    -- 수정
    PROCEDURE prc_upd (i_cur NUMBER, i_par NUMBER)
    IS
    BEGIN
        DELETE
          FROM tree
         WHERE dec IN (SELECT dec
                         FROM tree
                        WHERE anc = i_cur)
           AND anc IN (SELECT anc
                         FROM tree
                        WHERE dec =  i_cur
                          AND anc <> i_cur);

        INSERT
          INTO tree
              (anc, dec, par, dep)
        SELECT a.anc
             , b.dec
             , NVL (b.par, i_par) AS par
             , a.dep + b.dep + 1 AS dep
          FROM tree a
             , tree b
         WHERE a.dec = i_par
           AND b.anc = i_cur;
    END prc_upd;

    -- 삭제
    PROCEDURE prc_del (i_cur NUMBER)
    IS
    BEGIN
        DELETE
          FROM tree
         WHERE dec IN (SELECT dec
                         FROM tree
                        WHERE anc = i_cur);
    END prc_del;
END pkg_tree;
/

아래 예제는 8번 노드를 5번 노드의 하위 노드로 입력한다.

-- 4-1
EXEC pkg_tree.prc_ins (8, 5);

현재 트리 구조는 아래와 같다.

1 - 2 - 3
    4 - 5 - 8
        6 - 7

아래 예제는 5번 노드를 3번 노드의 하위 노드로 변경한다.

-- 4-2
EXEC pkg_tree.prc_upd (5, 3);

현재 트리 구조는 아래와 같다.

1 - 2 - 3 - 5 - 8
    4 - 6 - 7

아래 예제는 6번 노드와 그 하위 노드를 삭제한다.

-- 4-3
EXEC pkg_tree.prc_del (6);

COMMIT;

현재 트리 구조는 아래와 같다.

1 - 2 - 3 - 5 - 8
    4

아래 쿼리는 2번 노드의 하위 노드를 조회한다. 트리가 변경된 것을 확인할 수 있다.

-- 5-1
SELECT * FROM tree WHERE anc = 2;

ANC DEC PAR DEP
--- --- --- ---
  2   2       0
  2   3   2   1
  2   5   3   2
  2   8   5   3

4 행이 선택되었습니다.

아래 쿼리는 5번 노드의 상위 노드를 조회한다.

-- 5-2
SELECT anc, dec, LEAD (anc) OVER (ORDER BY dep) AS par, dep
  FROM tree
 WHERE dec = 5;

ANC DEC PAR DEP
--- --- --- ---
  5   5   3   0
  3   5   2   1
  2   5   1   2
  1   5       3

4 행이 선택되었습니다.