[SQL] Recursive 처리 (재귀 SQL)

KDUG를 방문했다가 oracle “connect by” 변환에 대한 질문이 있어 테스트를 해 보았다.

댓글 달기가 좀 불편한 점이 있어 recursive 처리에 대한 자세한 내용을 하지 못해서, 이곳에 좀 더 자세히 올리면 좋겠다 하는 생각에 적어 본다.

V9.1까지는 오라클 SQL 호환성 기능이 지원되지 않아 recursive 처리가 쉽지가 않았다. 9.5부터 connect by 사용이 가능해져서 전보다 변환 작업이 편해 졌을 것이라 생각한다.

참고로 오라클 SQL 호환성 기능을 활용하여 ORACLE 변환 작업한 기회가 많지 않아 어느 정도 가능한지 말하기는 어렵다. (단, 2년 전 V9.7 Cobra로 PoC 수행시 문제가 발생해서 connect by를 쓰지 않고 Common table로 변환 처리했던 기억이 어렴풋이 난다. ^^; )

테스트는 sample 데이터베이스의 department 테이블로 한다.

1. department 테이블 정의

$ db2 describe table department Column name                            schema    Data type name      Length     Scale Nulls ------------------------------- --------- ------------------- ---------- ----- ------ DEPTNO                                    SYSIBM    CHARACTER                    3          0 No DEPTNAME                               SYSIBM    VARCHAR                       36          0 No MGRNO                                    SYSIBM    CHARACTER                    6           0 Yes ADMRDEPT                               SYSIBM    CHARACTER                    3           0 No LOCATION                                SYSIBM     CHARACTER                   16          0 Yes

deptno 컬럼의 값과 admrdept 컬럼의 값이 동일하다. 이 2 컬럼을 통하여 “계층"(hierarchey) 처리가 가능하다.

 

2. department 데이터

$ db2 "select deptno, admrdept from department" DEPTNO ADMRDEPT ------ -------- A00    A00 B01    A00 C01    A00 D01    A00 D11    D01 D21    D01 E01    A00 E11    E01 E21    E01 F22    E01 G22    E01 H22    E01 I22     E01 J22    E01   14 record(s) selected..

 

3. 계층  recurcive 처리

   (1) deptno 컬럼과 admrdept 컬럼의 값이 동일한 값을 “최상위 계층”이라 정의하고 level에 0 값을 지정하여 parent 테이블에 저장한다. (아래 SQL의 붉은색 글씨)

    (2) “최상위 계층”의 값의 deptno값과 department 테이블의 admrdept 값이 일치하는 값을 찾아

두번째 level의 데이터를 찾아 parent 테이블에 저장한다. (연두색 글씨)

    (3) 두번째 level 값을 갖는 deptno 값을 통하여 department 테이블의 admrdept 값이 일치하는 값을 다시 찾아 세번째 level 데이터를 parent 테이블에 저장한다.

    (4) 이렇게 parent 테이블에 저장된 데이터를 통하여 새로운 level의 데이터를 찾은 후, 전체 데이터에 대해 완료하면 parent 테이블을 조회하여 값은 반환시킨다.

(a.deptno != p.fkey 에 대한 이유는 글을 보시는 분들에게 숙제로 남겨 본다.)

$ db2 +p -tv << EOF > with parent (pkey, fkey, level) as > (select deptno, admrdept, 0 from department >  where deptno = admrdept >    union all >  select a.deptno, a.admrdept, p.level+1 >  from department a, parent p >  where a.deptno != p.fkey and a.admrdept = p.pkey > ) > select p.pkey,p.level from parent as p; > EOF with parent (pkey, fkey, level) as (select deptno, admrdept, 0 from department where deptno = admrdept union all select a.deptno, a.admrdept, p.level+1 from department a, parent p where a.deptno != p.fkey and a.admrdept = p.pkey ) select p.pkey,p.level from parent as p PKEY        LEVEL ----          ----------- SQL0347W  The recursive common table expression "INST97.PARENT" may contain an infinite loop.  SQLSTATE=01605 A00            0 B01            1 C01            1 D01            1 E01            1 D11            2 D21            2 E11            2 E21            2 F22            2 G22            2 H22            2 I22            2 J22            2   14 record(s) selected with 1 warning messages printed.

 

4. recursive가 되는 테이블의 SELECT 절에 recursive 처리는 넣어 처리하려면?

이것은 KDUG에 문의된 내용이다. 될까? 안될까? 궁금해서 테스트를 해 보았는데 한 SQL에서 recursive를 2번 사용하는 것은 지원되지 않는 듯 하다. (SQL0104N 에러 발생)

그래서 SELECT 절에서 사용되는 recursive 처리는 UDF (User define Function)으로 빼서 처리를 했더니 이중 recursive 처리가 되었다.

with parent (pkey, fkey, level) as (select deptno, admrdept, 0 from department where deptno = admrdept union all   select a.deptno, a.admrdept, p.level+1   from department a, parent p   where a.deptno != p.fkey and a.admrdept = p.pkey ) select p.pkey,p.level,        ( with subpar (pkey,fkey, level) as            ( select d.deptno, d.admrdept, p.level from department d where d.admrdept=p.pkey               union all               select a.deptno, a.admrdept, s.level+1 from department a, subpar s where a.deptno !=s.fkey and a.admrdept=s.pkey             )          select count(*) from subpar) as subcnt from parent as p;   실행 결과 SQL0104N  An unexpected token "as" was found following "r (pkey,fkey, level)". Expected tokens may include:  "JOIN".  SQLSTATE=42601

 

SELECT 절의 recursive의 함수 처리

db2 +p -td"@" -v << EOF drop function reccnt() @ create function reccnt(p_key varchar(5), p_level integer) returns integer return with subpar (pkey,fkey, level) as             ( select d.deptno, d.admrdept, p_level from department d where d.admrdept=p_key               union all               select a.deptno, a.admrdept, s.level+1 from department a, subpar s where a.deptno !=s.fkey and a.admrdept=s.pkey             )          select count(*) from subpar @ EOF SQL0347W  The recursive common table expression "INST97.SUBPAR" may contain an infinite loop.  SQLSTATE=01605

 

Recursive SQL

db2 +p -tv << EOF with parent (pkey, fkey, level) as (select deptno, admrdept, 0 from department where deptno = admrdept union all   select a.deptno, a.admrdept, p.level+1   from department a, parent p   where a.deptno != p.fkey and a.admrdept = p.pkey ) select pkey,level, reccnt(pkey, level) as cnt from parent; EOF 실행 결과 PKEY        LEVEL       CNT ----         ----------- ----------- SQL0347W  The recursive common table expression "INST97.SUBPAR" may contain an infinite loop.  SQLSTATE=01605 A00            0          27 B01            1           0 C01            1           0 D01            1           2 E01            1           7 D11            2           0 D21            2           0 E11            2           0 E21            2           0 F22            2           0 G22            2           0 H22            2           0 I22             2           0 J22            2           0   14 record(s) selected with 1 warning messages printed.

Recursive 처리 구현이 어느 정도는 가능하지만, 데이터가 많은 경우 성능 저하는 심하게 발생할 수 밖에 없다. Connect by를 이용한 처리에 비해 부하가 더 심할지, 비슷한지는 비교해 본 적이 없기에 알 수 없지만 “꺼려지는 SQL” 이다.