Recursive Subquery Factoring의 한가지 활용 예
오라클/SQL 튜닝 2010/12/01 17:46
Oracle 11gR2부터 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> 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> 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을 이용함으로써 좀 더 작업이 간편해지는 몇 안되는 예제 중 하나라서 올려봅니다.
앞으로 11gR2가 본격적으로 사용되면 활용 예제가 더 많이 소개될 것으로 기대합니다.