1. 순환관계의 개념 일반적으로 key entity는 다수의 child entity를 가지게 되며, key entity의 subtype간에 계층적 관계를 가지고 있을 경우에 child entity와 배타적 관계를 가지게 되므로 데이터 연결시에는 불가피하게 OR 연산이 발생하며 child entity의 인덱스 구조에 따라 관계 수만큼 일량이 증가할 수 도 있다. 또한 계층구조모델은 구조 변경시 식별자가 변경되는 취약점을 가지고 있다. 이와 같은 계층관계의 문제점을 해소할 수 있는 보다 함축적이고 통합된 구조로 표현한 것이 순환관계이며 다음과 같이 1:M 순환관계모델과 M:M 순환관계에 대한 BOM 모델 2가지가 있다.

본사 본부 부서 팀 조직 부품 사원 부품 조립규칙 사원

2. 순환구조의 장단점 2.1 순환 구조의 장점 1) 구조가 변경되어도 식별자가 변경되지 않으므로 구조 변경에 쉽게 대응할 수 있다.

2) child entity와 데이터 연결을 할 때 일량이 증가하지 않는다.

3) 계층적 구조 데이터 처리가 편리하다.

2.2 순환 구조의 단점 1) 부적절하게 사용했을 때 수행 속도에 상당한 영향을 미친다.

2) 순환구조는 개별 node의 점 조직이므로 RDBMS의 특징상 특정 점에 관련된 종속적인 정보만 직접 처리하기에 부적절하다.

3. 순환구조의 처리 ORACLE의 경우에는 connect by ~ start with를 이용하여 처리할 수 있으며, 이러한 기능을 제공하지 않는 RDBMS의 경우에는 순환구조 프로그래밍을 구현하여야 한다.

3.1 ORACLE 순환구조 처리 select empno,mgr from emp where sal > 1000 이전에 access된 상수조건 connect by prior empno = mgr => join 조건 => check 조건 start with mgr is null =>driving 조건 3.2 ORACLE 순환구조 제한사항 1) 순환구조 내에서는 조인이 불가하므로 조인을 수행하는 query로 구성된 view에 대해서도 select가 불가하다.

2) connect by절은 subquery를 제외한 모든 조건(condition)이 적용된다.

3) connect by는 순환구조에서 반복(looping)이 발생할 수 있다. 반복은 서로 다른 row에 부모와 자식이 존재할 경우 발생하며 이는 무한looping을 초래하므로 ORACLE은 error를 발생시킨다.

3.3 ORACLE 순환구조의 Algorithm Tree traverse 방식은 preorder, inorder, postorder가 있으며 모두 stack을 사용하며 recursion function을 이용한다.

ORACLE 순환구조는inorder traverse 방식으로 구현되어 있다. Inorder traverse는 node 구성 및 탐색시 parent node에 비해 chile node의 data 크기가 작으면 왼쪽, 크면 오른쪽으로 이동한다. 다음은 inorder traverse 방식에 대한 개괄적인 Algorithm이다.

struct IN_NODE { int value; struct IN_NODE *left; struct IN_NODE *right; /* data */ /* 왼쪽 노드 */ /*오른쪽 노드*/ } NODE; int NODE_bytesize; /* root node 가 p 인 tree 에 새로운 node를 추가하여 tree를 구축하고 data n수록 */ NODE *tree(NODE *p, int n) { if (p == NULL) /* 아래에 더 이상 노드가 없으면 */ { p = (NODE *)malloc(NODE_bytesize); p->value = n; p->left = p->right = NULL; } else if ( n < p->value ) p->left = tree(p->left, n); else p->right = tree(p->right, n); /* 작으면 좌로 */ /* 크면 우로 */ return p; } void tree_inorder(NODE *p) /* 만들어진 tree를 inorder traverse */ { if (p != NULL) { tree_inorder(p->left); /* 좌로 내려가서 */ printf("%-3d", p->value); /* data 출력 */ tree_inorder(p->right); /* 우로 내려가서 반복 */ } }

4. 순환구조의 응용사례 다음은 국내 모이동통신회사 데이터 전환시 순환구조 개념을 적용한 응용 사례이다.

일반적으로 고객이 신규 개통을 할 경우 1개의 가입 계약이 설정되고 이후 명의변경,전화번호변경 등의 사유로 해당 개통 건 에 대한 이력이 발생한다. 당사는 최초 신규 개통에 대한 고유 관리번호가 존재하지 않고 계약변경사항 발생시 마다 이력이 발 생한다. 이는 신규개통 가입계약에 대한 전체적인 이력관리가 되지 않으므로 모든 신규개통에 대한 고유관리번호를 부여하고 자 한다. 아래 그림은 가입계약별 변경사항(event)이 발생할 때의 event diagram이다.

모든 가입계약은 최초 개통 event가 존재하며 이후 다양한 event(명의변경,번호변경,일시정지등의 상태변경)에 의해 이력이 발생한다. ID가 변경되는 경우는 명의변경만 해당되며 나머지는 이력만 발생한다. 명의변경 전후의 ID 정보는 Prev_ID,Next_ID로 알 수 있으므로 이력 발생시마다 Prev_ID는 복제되며 Next_ID는 명의변경의 경우에만 존재한다.

Prev_ID NULL Curr_ID 00000 Next_ID 11111 CTN 01611111 Prev_ID 00000 Curr_ID 11111 Next_ID 22222 CTN 01611111 Prev_ID 11111 Curr_ID 22222 Next_ID NULL CTN 01622222 Prev_ID 11111 Curr_ID 22222 Next_ID 33333 CTN 01633333 개통 명의 번호 정지 복구 해지 Prev_ID 22222 Curr_ID 33333 Next_ID NULL CTN 01633333 Prev_ID 22222 Curr_ID 33333 Next_ID NULL CTN 01633333 Prev_ID 22222 Curr_ID 33333 Next_ID 44444 CTN 01633333 Prev_ID 33333 Curr_ID 44444 Next_ID NULL CTN 01633333

가입계약의 최초 개통에 대한 고유관리번호를 부여하기 위해서는 Prev_ID가 null인 경우로 시작해서 해당 Cur_ID가 Prev_ID인 경우를 찾으면 된다. 최초 개통을 제외한 모든 경우는 Prev_ID가 존재하므로 M:M 순환관계가 형성된다. 하지만 이력을 발생시키는 event중 명의변경의 경우만 ID가 변경되므로 실제 데이터 관점에서는 1:1 순환관계라고 할 수 있다. 즉 아래 그림과 같이 점선,실선을 구분하지 않으면 M:M 관계이지만, 점선을 제외한다면 1:1 관계가 되는 것이다.

ID : 00000 ID : 11111 ID : 22222 ID : 22222 ID : 33333 ID : 33333 ID : 33333 ID : 44444 이제부터 요지는 M:M 관계를 1:1 관계로 처리하는 것이다. M:M 관계를 해소하는 방법은 다음 3가지가 있다.

1.

Inline View를 활용 2. Subquery를 활용 3. User defined function을 활용 여기서 순환구조 특성상 Inline View나 Subquery를 활용하는 것은 불가하므로 사용자 정의 함수를 활용하여 가입계약별 최초 개통에 대한 고유관리번호를 부여하는 SQL을 작성해 보자.

다음은 테스트를 위한 script 및 SQL이다.

create table contract ( curr_id prev_id varchar(10), next_id ctn varchar(12), status varchar(10), varchar(10), varchar(1)); insert into contract values ('00000',null,'11111','01611111','O'); insert into contract values ('11111','00000','22222','01611111','M'); insert into contract values ('22222','11111','','01622222','M'); insert into contract values ('22222','11111','33333','01633333','N'); insert into contract values ('33333','22222','','01633333','M'); insert into contract values ('33333','22222','','01633333','S'); insert into contract values ('33333','22222','44444','01633333','R'); insert into contract values ('44444','33333','','01633333','M'); insert into contract values ('44444','33333','','01633333','C'); insert into contract values ('55555',null,'66666','01611111','O'); insert into contract values ('66666','55555','77777','01611111','M'); insert into contract values ('77777','66666','','01622222','M'); insert into contract values ('77777','66666','88888','01633333','N'); insert into contract values ('88888','77777','','01633333','M'); insert into contract values ('88888','77777','','01633333','S'); insert into contract values ('88888','77777','99999','01633333','R'); insert into contract values ('99999','88888','','01633333','M'); insert into contract values ('99999','88888','','01633333','C');

CREATE OR REPLACE FUNCTION f_connect_by (arg1 IN char) return char is rtn_id varchar(20) ; BEGIN select into /*+ index(contract contract_idx) */ rowid rtn_id from where return rtn_id; contract prev_id = arg1 and rownum =1; EXCEPTION WHEN NO_DATA_FOUND THEN return NULL; WHEN OTHERS THEN return NULL; END; 편의상 새로 부여하는 고유 관리번호는 최초 개통시의 ID를 부여하는 것으로 함.

select from curr_id, max(contract_no) over (order by rownum) contract_no (select curr_id, from decode(level,1,curr_id) contract_no contract connect by f_connect_by(prior curr_id) = rowid start with prev_id is null); << 결과집합 >> CURR_ID CONTRACT_NO ====================== 00000 00000 11111 22222 33333 44444 55555 00000 00000 00000 00000 55555 66666 77777 88888 99999 55555 55555 55555 55555

<< User Function Execution Plan >> Select /*+ index(contract contract_idx) */ rowid From contract Where prev_id = :b1 and rownum = 1 call count cpu elapsed disk query current rows ------------------------------------------ Parse 0 0.00 0.00 0 0 0 0 Execute 10 0.00 0.00 0 0 0 0 Fetch 10 0.01 0.00 0 10 0 8 ------------------------------------------ total 20 0.01 0.00 0 10 0 8 Rows Execution Plan ------------------------------------------ 0 SELECT STATEMENT GOAL: CHOOSE 0 COUNT (STOPKEY) 0 INDEX (RANGE SCAN) OF 'CONTRACT_IDX' (NON-UNIQUE) 통계정보의 execute call count가 10인 것을 주목!!

<< Main SQL Execution Plan >> Select curr_id, max(contract_no) over (order by rownum) contract_no From (select curr_id, decode(level,1,curr_id) contract_no from contract connect by f_connect_by(prior curr_id) = rowid start with prev_id is null) call count cpu elapsed disk query current rows ------------------------------------------- Parse 1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.01 0 11 4 10 ------------------------------------------- total 3 0.01 0.01 0 11 4 10 Rows Execution Plan ------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 10 WINDOW (SORT) 10 COUNT 10 VIEW 10 CONNECT BY 3 TABLE ACCESS (FULL) OF 'CONTRACT' 2 TABLE ACCESS (BY USER ROWID) OF 'CONTRACT' 18 TABLE ACCESS (BY USER ROWID) OF 'CONTRACT'

본 SQL의 요지는 M:M 순환관계에서 실제 전개할 distinct data value간은 1:1 관계를 가지고 있을 경우 data value별 한번만 access하여 순환구조를 전개하고자 하는 것이다.

이런 경우 일반적인 connect by로 순환구조를 처리하면 full table scan시에는 해당 데이터 건수가 n건이라면 n*n이 travere대상이며 인덱스를 이용할지라도 최소 (n-1)*2-1이 traverse 대상이다.

혹자는 굳이 rowid를 사용 할 필요가 없이 user function에서 rownum=1만 부여하면 되지 않겠느냐고 생각할 수도 있다.

하지만 이는 순환구조의 내부 전개 메커니즘때문에 지금과 동일한 결과가 도출되지 않는다. 즉, 일반적인 순환구조 전개와 동 일한 방식으로 처리되는 것이다. ORACLE의 connect by는 inorder traverse 방식으로 전개되며 inorder traverse는 recursion concept을 기반으로 하므로 Data value로 전개하면 이전 access된 data value가 stack에 저장된다. 그리고 leaf node의 데이터를 처리하고 나면 stack에 저장된 data value로 이전 parent node로 내부적으로 역전개하여 계속적인 순환전개를 수행하는 것이다. 그러므로 connect by의 순환전개는 data value를 기반으로 하는 것이다. Rowid로 전개한 목적은 바로 여기에 있다. 즉, Rowid라는 논리적인 위치정보를 이용하여 이전 parent node에 대한 역전개를 제거함으로써 start with에서 제공되는 Data value별 1:1의 순환전개로 유도하며 또한 rowid로 access하므로 performance 향상을 유도한다. 그럼으로써 기존의 순환전개에서 발생하는 상당한 비효율을 제거할 수 있다.