I. Oracle에서의 SYS_CONNECT_BY_PATH 사용 목적과 흉내내기

I. Oracle에서의 SYS_CONNECT_BY_PATH 사용 목적과 흉내내기

부모-자식의 관계가 있는 데이터, 특히 BOM, 사원정보 및 계층형 분류를 출력함에 있어서 최종 노드로부터 최상위 노드까지의 모든 노드의 데이터를 구분자를 이용하여 출력하기 위해 사용됩니다.

앞으로 제시할 방법은 말 그대로 흉내내기 입니다.

오라클의 계층형 함수는 다양한 형태로 사용될 수 있기 때문에 SQL만으로는 복잡한 쿼리문에 사용되는 함수형태로 구현하기는 어렵습니다. 단지, 계층형 구조 데이터를 오라클의 SYS_CONNECT_BY_PATH와 유사한 결과물을 표현하는 데 목적이 있다면 제가 소개하는 방법만으로도 충분할 것입니다.

언젠가는 CUBRID에서도 계층형 함수가 제공될 것으로 생각됩니다만, 그 전까지는 부족하나마 사용할 수 있으리라 생각합니다.

II. Oracle의 SYS_CONNECT_BY_PATH 함수의 스팩

SYS_CONNECT_BY_PATH ( column, char )

예제:

SELECT cat_no,

               SYS_CONNECT_BY_PATH (title, ‘/’) AS path

FROM    T_CAT

START WITH parent_cat_no IS NULL

CONNECT BY PRIOR cat_no = parent_cat_no

결과:

cat_no   title

1           /정치

2           /정치/국내정치

4           /정치/해외정치

3           /경제

5           /경제/국내경제

우리는 Oracle의 구문 형태는 무시하고 일반적인 결과물 출력만 만족하는 SQL을 만들어 보겠습니다. 시작 노드에 대한 정의도 없으며 현재 존재하는 모든 노드에 대한 경로를 생성하는 것이 그 목적입니다. 단, 입력된 데이터에서 루트 노드의 부모 노드 번호는 NULL값으로 지정이 되어 있어야 합니다.

III. SQL로 흉내내기

1. 테이블 생성 및 샘플 데이터 입력

CREATE TABLE "t_cat"(

"cat_no" integer PRIMARY KEY,

"parent_cat_no" integer,

"title" character varying(200) NOT NULL

);

insert into "t_cat" ("cat_no", "parent_cat_no", "title") 

values (1, NULL, '정치');

insert into "t_cat" ("cat_no", "parent_cat_no", "title") 

values (2, 1, '국내정치');

insert into "t_cat" ("cat_no", "parent_cat_no", "title") 

values (3, 1, '해외정치');

insert into "t_cat" ("cat_no", "parent_cat_no", "title") 

values (4, NULL, '경제');

insert into "t_cat" ("cat_no", "parent_cat_no", "title") 

values (5, 4, '국내경제');

insert into "t_cat" ("cat_no", "parent_cat_no", "title") 

values (6, 4, '해외경제');

insert into "t_cat" ("cat_no", "parent_cat_no", "title") 

values (7, 1, '기타정치');

insert into "t_cat" ("cat_no", "parent_cat_no", "title") 

values (8, 5, '기타경제');

insert into "t_cat" ("cat_no", "parent_cat_no", "title") 

values (9, 2, '정당');

insert into "t_cat" ("cat_no", "parent_cat_no", "title") 

values (10, 9, '한나라당');

insert into "t_cat" ("cat_no", "parent_cat_no", "title") 

values (11, 9, '민주당');

commit;

2. 최대 DEPTH만큼 테이블을 self outer join

SQL:

SELECT  

        t1.cat_no                     AS cat_no1,

        t2.cat_no                     AS cat_no2,

        t3.cat_no                     AS cat_no3,

        t4.cat_no                     AS cat_no4,

        t1.title                         AS title1,

        t2.title                         AS title2,

        t3.title                         AS title3,

        t4.title                         AS title4

FROM    

        t_cat t1,

        t_cat t2,

        t_cat t3,

        t_cat t4 

WHERE   

           t2.parent_cat_no = t1.cat_no (+)

AND     t3.parent_cat_no = t2.cat_no (+)

AND     t4.parent_cat_no = t3.cat_no (+)

실행결과:

부모-자식 관계를 4 depth에 걸쳐서 outer join으로 관계를 만들어주고 있습니다. 결과적으로 모든 노드에 대해 경로가 만들어졌습니다. 데이터의 수와 동일한 총 11개의 행이 조합되었습니다.

- 루트노드 : NULL 값으로 채워진 부분은 NULL이 아닌 값을 앞으로 가져오면 첫번째 분류번호는 루트 노드의 번호가 됩니다. 위 데이터에서는 결국 1, 4번이 루트 노드가 되겠습니다.

- 해당 계층의 말단 노드 : NULL을 제외한 가장 마지막 분류 번호가 해당 계층의 말단 노드 입니다. 단, 전체 노드를 통틀어 말단 노드가 되는 것은 아닙니다. 위 데이터에서는 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1과 같이 결국 모든 데이터의 분류 번호가 해당되겠습니다. 그 의미는 그 계층의 실제 분류 번호에 해당한다고 보시면 되겠습니다. 예를들어, 한나라당이라는 분류는 정치/국내정치/정당/한나라당이라고 하는 계층의 실제 분류 번호가 되겠지요.

- 계층 깊이 : NULL을 제외한 분류번호의 개수는 그 계층의 depth 입니다. 예를들어, 바로전의 민주당이라는 계층은 4 depth에 위치하고, 정치/국내정치에 해당하는 10번째 데이터는 2 depth에 해당하겠습니다.

3. 데이터 전환

출력된 데이터를 입맛에 맞게 전환해서 원하는 포멧으로 출력해보겠습니다. 우선, 분류번호, 최상위 분류번호, 노드깊이, 경로와 같은 형태로 출력하고자 합니다. 아래 SQL을 실행하셔서 출력형태를 확인해보시기 바랍니다.

SELECT  

        CASE WHEN cat_no4 IS NOT NULL THEN cat_no4

             WHEN cat_no3 IS NOT NULL THEN cat_no3

             WHEN cat_no2 IS NOT NULL THEN cat_no2

             WHEN cat_no1 IS NOT NULL THEN cat_no1

             END                              AS cat_no,

        CASE WHEN cat_no1 IS NOT NULL THEN cat_no1

             WHEN cat_no2 IS NOT NULL THEN cat_no2

             WHEN cat_no3 IS NOT NULL THEN cat_no3

             WHEN cat_no4 IS NOT NULL THEN cat_no4

             END                              AS root_cat_no,

        TO_NUMBER(NVL2(title1, '1', '0')) +

            TO_NUMBER(NVL2(title2, '1', '0')) +

            TO_NUMBER(NVL2(title3, '1', '0')) +

            TO_NUMBER(NVL2(title4, '1', '0')) AS cat_level,

        NVL2(title1, '/'+title1, '') + 

            NVL2(title2, '/'+title2, '') + 

            NVL2(title3, '/'+title3, '') + 

            NVL2(title4, '/'+title4, '')      AS cat_path

FROM 

(

        SELECT  

                t1.cat_no                     AS cat_no1,

                t2.cat_no                     AS cat_no2,

                t3.cat_no                     AS cat_no3,

                t4.cat_no                     AS cat_no4,

                t1.title                         AS title1,

                t2.title                         AS title2,

                t3.title                         AS title3,

                t4.title                         AS title4

        FROM    

                t_cat t1,

                t_cat t2,

                t_cat t3,

                t_cat t4 

        WHERE   

                   t2.parent_cat_no = t1.cat_no (+)

        AND     t3.parent_cat_no = t2.cat_no (+)

        AND     t4.parent_cat_no = t3.cat_no (+)

) t

실행결과:

순서는 뒤죽박죽이지만 원하는 데이터 유형은 출력되었습니다. 이제 마지막으로 정렬을 해볼 차례입니다.

4. 데이터 정렬

ORDER

BY      root_cat_no, 

          cat_path

위의 SQL을 덧붙여서 실행하시면 1차로 루트 노드의 번호로 정렬을 하고, 다음으로는 경로 문자열의 가나다 순으로 정렬을 하도록 합니다. 단순하게 효과만 내려고 분류명의 경로 문자열로 정렬을 하게 되었는데, 필요하다면 분류명의 경로명과 함께 분류 번호를 0으로 채워진 16진 문자열(정수형일 경우 4바이트)로 경로를 만들어서 정렬 용도로 사용하시면 형제노드끼리의 정렬 문제는 분류번호로 정렬되도록 할 수도 있습니다만 일반적인 경우에는 분류명 정렬로 충분하기에 그에 대한 내용은 추가하지 않았습니다. 물론, 현재 CUBRID에서는 HEX와 같은 함수가 없기 때문에 Java SP로 구현해야한다는 부담도 있기는 합니다.

실행결과:

5. 응용

만약, 최대 깊이가 4가 아니라 7일 경우에는 위 SQL에서 7개의 테이블을 결합해야하고, 7개의 cat_no와 title에 대한 데이터 변환 구문이 들어가야 합니다.

이 SQL을 응용한다면, CONNECT_BY와 유사한 효과를 내는 SQL도 만드실 수 있을 것입니다.

성능은 고려 대상에서 제외되어 있는데, 만약 성능이 문제라면 캐쉬 테이블을 하나 만드셔서 필요할 때마다 캐쉬테이블을 갱신하고 운영시에는 그 캐쉬테이블의 데이터를 활용하시는 것도 좋을 듯 합니다. 그것도 아니면 인덱스 튜닝을 좀 하셔야할 듯 합니다.

IV. 맺음말

흉내내기는 본래의 대상은 될 수 없으나 그 효과는 얻을 수 있습니다. 즉, 실제 Oracle에서 제공하는 계층형 함수의 활용에 관한 유연성을 얻기는 불가능하나 다만 비슷한 효과는 얻을 수 있다는 말입니다. 현재 가능한 방법에서 최대한 비슷한 결과를 도출하게 하는 것이 이번 글의 목적이며, 충분히 테스트는 되지는 못했지만 쿼리를 만든 절차를 알려드리면서 오류가 있다면 바로잡고 더 좋은 방법도 얻을 것으로 기대하며 쓰고 있습니다. 오류나 개선사항은 newpcraft at gmail.com 으로 언제든 메일 주시면 고맙겠습니다.


출처 : http://www.pcraft.kr/178