클로저 테이블 #1

SQL AntiPatterns에 수록된 클로저 테이블(Closure Table)을 살펴보자.^[각주:1] 

예제를 위해 아래와 같이 테이블을 생성하자. 클로저 테이블은 하위 노드의 경로를 모두 저장한다. 자식 노드가 많아질수록 행이 기하급수적으로 증가하는 구조다. 노드에 대한 내용은 별도 테이블로 관리된다. 기능을 개선하기 위해 깊이(dep) 속성을 추가했다.

-- 1
DROP TABLE tree PURGE;

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

CREATE INDEX tree_u1 ON tree (anc, dep, dec);
CREATE INDEX tree_u2 ON tree (dec, dep, anc);

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

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

1 - 2 - 3
    4 - 5
        6 - 7

아래 쿼리를 4번 노드의 하위 노드를 조회한다. 부모 노드를 조회할 수 있도록 쿼리를 개선했다. 쿼리가 복잡해서 뷰를 생성하는 편이 유리해 보인다.^[각주:2]

-- 2-1 : 하위 노드 조회
CREATE OR REPLACE VIEW tree_c
AS
SELECT a.anc
     , a.dec
     , a.dep
     , b.anc AS par -- 부모
  FROM tree a
     , tree b
 WHERE b.dec(+) = CASE WHEN a.dep > 0 THEN a.dec END
   AND b.dep(+) = 1

SELECT * FROM tree_c WHERE anc = 4;

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

4 행이 선택되었습니다.

아래 쿼리는 7번 노드의 상위 노드를 조회한다. 재귀를 사용하지 않고 계층 구조를 조회할 수 있다는 점이 흥미롭다.

-- 2-2 : 상위 노드 조회
CREATE OR REPLACE VIEW tree_p
AS
SELECT a.dec
     , a.anc
     , a.dep
     , b.anc AS par
  FROM tree a
     , tree b
 WHERE b.dec(+) = a.anc
   AND b.dep(+) = 1;

SELECT * FROM tree_p WHERE dec = 7;

DEC ANC DEP PAR
--- --- --- ---
  7   7   0   6
  7   6   1   4
  7   4   2   1
  7   1   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, dep)
        SELECT i_cur AS anc
             , i_cur AS dec
             , 0 AS dep
          FROM DUAL
        UNION ALL
        SELECT anc
             , i_cur AS dec
             , 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, dep)
        SELECT a.anc
             , b.dec
             , 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_c WHERE anc = 2;

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

4 행이 선택되었습니다.

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

-- 5-2
SELECT * FROM tree_p WHERE dec = 5;

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

4 행이 선택되었습니다.

1. 3장 순진한 트리(Naive Trees)에 수록되어 있다. 클로저 테이블은 계층 쿼리를 지원하지 않는 DBMS에서 활용할 수 있는 트리 구조다. [본문으로]
2. 부모 노드 속성을 관리하면 조인을 제거할 수 있다. 관련 내용은 추후에 살펴보자. [본문으로]