Recursive Subquery Factoring 활용

Oracle 11g R2부터 Recursive Subquery Factoring이라는 기능을 제공합니다. 이 기능을 이용하면 Connect By 구문을 대신할 수 있죠. 아래에 간단한 사용법이 있습니다.

SQL>  select lpad(' ', 2 * level - 2, ' ') || ename as ename
  2        , empno
  3        , mgr
  4        , level
  5     from emp
  6  connect by mgr = prior empno
  7    start with mgr is null
  8  /

SQL> with emps (ename,empno,mgr,lvl) as
  2  ( select ename
  3         , empno
  4         , mgr
  5         , 1
  6      from emp
  7     where mgr is null
  8     union all
  9    select emp.ename
 10         , emp.empno
 11         , emp.mgr
 12         , emps.lvl + 1
 13      from emp
 14           join emps on (emp.mgr = emps.empno)
 15  ) search depth first by empno set a
 16  select lpad(' ', 2 * lvl - 2, ' ') || ename as ename
 17       , empno
 18       , mgr
 19       , lvl
 20    from emps
 21    order by a
 22  /

굳이 Connect By로 잘 사용하고 있었던 것을 왜 다시 Recursive Subquery Factoring을 사용해야 하는지를 고민해보면 딱히 떠오르는 것이 없는데요. 다음과 같은 유형의 쿼리에서 유용하게 사용할 수 있을 것 같습니다.

1. 다음과 같은 두 개의 테이블 T_MATERIAL과 T_COMPOSE가 있습니다. 테이블 T_MATERIAL은 "약"(material_type=Med) 또는 "약의 성분"(material_type=Mat) 데이터를 가지고 있습니다. 테이블 T_COMPOSE는 하나의 약이 어떤 하위 성분과 하위 약으로 이루어져 있는지의 관계를 나타냅니다. 하위 약은 다시 하위 약 또는 하위 성분을 가지므로 계층 구조가 됩니다. 그리고 각 하위 약 또는 하위 성분이 몇 %를 구성하고 있는지의 정보(contain_pct)를 가지고 있습니다.

SQL> create table t_material(
  2  	material_id		number,
  3  	material_name	varchar2(10),
  4  	material_type	varchar2(3) -- Med = medicine, Mat = material
  5  );
Table created.
 
SQL> create table t_compose (
  2  	medicine_id			number,
  3  	material_id			number,
  4  	contain_pct			number
  5  );
Table created.

이제 다음과 같이 데이터를 생성합니다.

SQL> begin
  2  	insert into t_material values(1, 'medicine1', 'Med');
  3  	insert into t_material values(2, 'medicine2', 'Med');
  4  	insert into t_material values(3, 'material1', 'Mat');
  5  	insert into t_material values(4, 'medicine3', 'Med');
  6  	insert into t_material values(5, 'material2', 'Mat');
  7  	insert into t_material values(6, 'medicine4', 'Med');
  8  end;
  9  /
PL/SQL procedure successfully completed.

SQL> begin
  2  	insert into t_compose values(1, 2, 0.3); -- Med
  3  		insert into t_compose values(2, 6, 0.5);
  4  			insert into t_compose values(6, 3, 0.8);
  5  			insert into t_compose values(6, 5, 0.2);
  6  		insert into t_compose values(2, 5, 0.5);
  7  	insert into t_compose values(1, 3, 0.3); -- Mat
  8  	insert into t_compose values(1, 4, 0.2); -- Med
  9  		insert into t_compose values(4, 3, 0.7);
 10  		insert into t_compose values(4, 5, 0.3);
 11  	insert into t_compose values(1, 5, 0.2); -- Mat
 12  end;
 13  /
PL/SQL procedure successfully completed.

1번 약은 (2번 약 30% + 3번 성분 30% + 4번 약 20% + 5번 성분 20%) 으로 이루어져있죠. 2번 약은 (6번 약 50% + 5번 약 50%)로 이루어져 있고, 6번 약은 (3번 성분 80% + 5번 성분 20%)로 이루어져 있습니다. 이런 식으로 계층 구조를 이루고 있습니다.

계층 구조를 지니면서 성분의 함량(contain_pct) 정보가 존재합니다. 여기서 이런 쿼리가 필요해집니다. 1번 약을 구성하는 각 성분의 함량은 어떻게 되는가? 즉, 1번 약을 구성하는 성분인 3번 성분(material1)과 5번 성분(material2)는 각각 몇 %인가?

위와 같은 쿼리가 까다로운 것은 계층 구조를 따라 모든 노드의 값(여기서는 contain_pct)를 알아야하기 때문입니다. 간단하게 계산해보면 3번 성분(material1)의 함량을 구하려면 계층 구조를 따라가면서 0.3*0.5*0.8 + 0.3 + 0.2*0.7 = 0.56 = 56%와 같은 계산이 필요합니다.

Connect By 구문에서는 현재 값과 이전 값(부모 값)만을 알 수 있습니다. 이 한계를 극복하기 위해 나온 것이 SYS_CONNECT_BY_PATH같은 함수죠. 아래와 같이 각 노드의 모든 함량 정보를 얻을 수 있습니다.

SQL> col pholder format a10
SQL> col pct_path format a20
SQL> select
  2  	lpad('-',level,'-') as pholder,
  3  	medicine_id,
  4  	material_id,
  5  	contain_pct,
  6  	sys_connect_by_path(contain_pct,'/') as pct_path
  7  from
  8  	t_compose
  9  connect by medicine_id = prior material_id
 10  start with medicine_id = 1
 11  ;

PHOLDER    MEDICINE_ID MATERIAL_ID CONTAIN_PCT PCT_PATH
---------- ----------- ----------- ----------- --------------------
-                    1           2          .3 /.3
--                   2           5          .5 /.3/.5
--                   2           6          .5 /.3/.5
---                  6           3          .8 /.3/.5/.8
---                  6           5          .2 /.3/.5/.2
-                    1           3          .3 /.3
-                    1           4          .2 /.2
--                   4           3          .7 /.2/.7
--                   4           5          .3 /.2/.3
-                    1           5          .2 /.2
10 rows selected.

위의 값을 실제로 계산하려면 다음과 같이 별도의 함수를 이용한 로직이 필요하게 됩니다.

SQL> create or replace function get_total_pct(pct_path in varchar2)
  2  return number
  3  is
  4  	v_idx1		number := 0;
  5  	v_idx2		number;
  6  	v_temp		number;
  7  	v_total		number := 1;
  8  begin
  9  	v_idx1 := instr(pct_path, '/');
 10  
 11  	loop
 12  
 13  		v_idx2 := instr(pct_path, '/', v_idx1+1);
 14  		if v_idx2 = 0 then
 15  			v_idx2 := length(pct_path)+1;
 16  		end if;
 17  
 18  		v_temp := to_number(substr(pct_path, v_idx1+1, v_idx2-v_idx1-1));
 19  		v_total := v_total * v_temp;
 20  
 21  		v_idx1 := v_idx2;
 22  
 23  		exit when v_idx1 > length(pct_path);
 24  
 25  	end loop;
 26  
 27  	return v_total;
 28  end;
 29  /
Function created.

CONNECT BY 구문과 SYS_CONNECT_BY_PATH 함수, 그리고 위에서 정의한 함수 GET_TOTAL_PCT를 이용하면 다음과 같이 원하는 값을 얻을 수 있습니다.

SQL> with c as (
  2  	select
  3  		material_id,
  4  		get_total_pct(sys_connect_by_path(contain_pct,'/')) as comp_pct
  5  	from
  6  		t_compose
  7  	connect by medicine_id = prior material_id
  8  	start with medicine_id = 1
  9  )
 10  select
 11  	m.material_name,
 12  	sum(c.comp_pct) as total_pct
 13  from
 14  	c,
 15  	t_material m
 16  where
 17  	c.material_id = m.material_id
 18  	and m.material_type = 'Mat'
 19  group by
 20  	m.material_name
 21  ;

MATERIAL_N  TOTAL_PCT
---------- ----------
material1         .56
material2         .44

(음... 더 멋진 방법이 있을 듯... )

Recursive Subquery Factoring에서는 위의 작업을 보다 직관적으로 처리할 수 있습니다. 다음과 같이 부모의 값을 받아서 함량(contain_pct)을 계속 곱해가면 최종 자식 노드의 함량을 알 수 있죠. 그 값을 SUM 하면 함량의 합이 됩니다. 즉, Recursive Subquery Factoring의 장점은 SYS_CONNECT_BY_PATH 같은 함수의 도움을 빌리지 않아도 모든 모드의 값을 이용할 수 있다는 것입니다.

SQL> with recur_mat(comp_pct, material_id)
  2  as (
  3  	select
  4  		contain_pct,
  5  		material_id
  6  	from
  7  		t_compose
  8  	where
  9  		medicine_id = 1
 10  	union all
 11  	select
 12  		p.comp_pct * c.contain_pct,  -- 부모 * 현재 
 13  		c.material_id
 14  	from
 15  		recur_mat p,
 16  		t_compose c
 17  	where
 18  		c.medicine_id = p.material_id
 19  )
 20  select
 21  	m.material_name,
 22  	sum(r.comp_pct) as total_pct
 23  from
 24  	recur_mat r,
 25  	t_material m
 26  where
 27  	r.material_id = m.material_id
 28  	and m.material_type = 'Mat'
 29  group by
 30  	m.material_name
 31  ;

MATERIAL_N  TOTAL_PCT
---------- ----------
material1         .56
material2         .44

Recursive Subquery Factoring을 이용함으로써 좀 더 작업이 간편해지는 몇 안되는 예제입니다.

출처: 욱짜의 오라클 블로그