순환관계의 개념 - 계층 쿼리 connected by prior 구문

이번 포스팅에서는 순환 관계에 대한 개념과 순환 관계를 SQL로 처리하는 계층 쿼리에 대해 알아 보겠습니다. 일반적인 선형적인 관계와는 달리 순환 관계로 엔터티를 연결하는 경우에 해당됩니다. 보통 엔티티에서 자기 자신을 참조한다고 해서 '재귀적 관계'라고도 불리우는 순환관계에 대해 살펴 보도록 하겠습니다. 

 

 

 

순환 관계 개념

데이터베이스에서 순환 관계는 엔터티가 순환적인 방식으로 연결될 때 발생합니다. 순환 관계에 대한 데이터 모델 설계 방식은 하나의 엔티티로 표현합니다. 엔터티 내에서 자식 컬럼이 부모 컬럼을 참조하는 방식입니다. 업무적으로는 조직 테이블을 유연하게 설계할 때 주로 사용합니다.

 

제조업 분야에서는 BOM 구조 라고도 합니다. BOM(Bill of Materials) 구조는 제품을 제조하는 데 필요한 구성 요소, 하위 자재를 계층적으로 표현한 것입니다. 이는 제품 구조에 대한 포괄적이고 체계적으로 관리하기 위해 사용합니다.  다양한 부품이 어떻게 결합되어 최종 조립품을 형성하는지 보여 줄 수 있습니다. BOM은 제조, 엔지니어링 및 기타 산업에서 생산 계획, 재고 관리 등을 편하게 관리하기 위해 널리 사용됩니다

CONNECT BY PRIOR 구문

오라클 SQL에서 'CONNECT BY PRIOR' 절은 계층적 쿼리를 수행하는 데 사용됩니다. 오라클이  아닌경우 해당 구문이 없을 수도 있습니다. 순환 구조를 재현하기 위해서는 동일한 테이블을 두번 읽는 방식으로 가능합니다. 하지만 속도는 확실히 좋지 않았던 경험이 있습니다.

 

CONNECT BY PRIOR 구문은 트리 구조로 구성된 데이터를 처리할 때 딱 적합합니다. 일반적으로 'START WITH' 절과 함께 사용되어 계층 구조의 루트를 지정합니다. 이 조합은 조직도, 가계도 또는 프로젝트 구조와 같은 계층적 데이터를 탐색하고 쿼리하는 데 많이 사용합니다.

SELECT LPAD(' ', 2 * LEVEL) || COL1 ...
FROM   BOM
WHERE  소요량  > 2
CONNECT BY PRIOR ID = P_ID   
START WITH P_ID = ‘A’   --   P_ID가 A부터 시작
;

 

• START WITH 조건 : 계층 구조의 루트를 식별합니다. 이 조건을 만족하는 행은 계층적 쿼리의 시작점으로 간주됩니다.
• CONNECT BY PRIOR 컬럼 = 컬럼 : 상위 행과 하위 행 간의 관계를 정의합니다. 이 절은 계층 구조에서 행이 연결되는 방식을 지정합니다. 'PRIOR' 키워드는 상위 row를 참조하며 = 양쪽의 열은 상위 row와 하위 row 간의 관계를 나타냅니다.

CONNECT BY PRIOR 구문에 대한 변형

SELECT LPAD( ' ', 2 * LEVEL) || COL1 ...
FROM   BOM
CONNECT BY PRIOR ID = P_ID
           AND T_ID  = 1 
START WITH P_ID  = 'A' 
           AND T_ID  = 1
;

 

T_ID가 1이면서 P_ID가 A에서 사작하고 ID = P_ID 면서 T_ID = 1인 것들을 조회합니다. 여기서 중요한 포인트는 T_ID가 1일 경우에 대한 것이므로 연결하는 부분인 CONNECT BY PRIOR 절에도 T_ID = 1이 들어가야 합니다. 이런 방식으로 소그룹별로 순환관계를 만들 수 있습니다. 예를 들어 T_ID = 1인 그룹, T_ID = 2 인 그룹 등 추가하여 구성할 수 있습니다. 

-- PK가 한 컬럼인 경우 SELECT LPAD( ' ', 2 * LEVEL) || COL1 ... FROM   BOM CONNECT BY PRIOR ID = P_ID START WITH P_ID  = ‘A’ ;

-- PK가 두 컬럼인 경우 SELECT LPAD( ' ', 2 * LEVEL) || COL1 ...   FROM BOM CONNECT BY PRIOR ID1 = P_ID1                 AND PRIOR ID2 = P_ID2 START WITH  P_ID1 = 'A'                 AND P_ID2 = '10' ;

 

기본적으로 PK가 한 컬럼인 결우는 왼쪽과 같이 사용하면 되지만 만약 두 컬럼인 경우 왼쪽처럼 컬럼 모두 CONNECT BY PRIOR 절과 START WITH 절에 넣어줘야 합니다. 

 

순환 관계를 전개할 때 여러가지 Case가 발생합니다. 아래 두 가지 방식에서 SQL로 어떻게 구현하는지에 대해 설명해 놓았습니다. 

예하그룹 미전개

특정 경우만 미추출

 

예하그룹 미전개는 'F' 값 아래로 보이지 않게 해야 하기 때문에 조인하는 부분 CONNECT BY PRIOR 구문에서 AND 조건을 추가해 줍니다. (ID <> 'F')

SELECT LPAD( ' ', 2 * LEVEL) || COL1 ...
FROM   BOM
CONNECT BY PRIOR ID = P_ID
           AND ID <> 'F'
START WITH P_ID = 'A'
;
 

 

특정 경우만 미추출의 경우는 연결은 마지막까지 처리하되, 해당 'F' 값만 보이지 않아야 하기 때문에 조인 부분이 아니라 WHERE 절을 추가해서 WHERE ID <> 'F'를 추가해 줍니다. 

SELECT LPAD( ' ', 2 * LEVEL) || COL1 ...
FROM   BOM
WHERE  ID <> 'F'
CONNECT BY PRIOR ID = P_ID
START WITH P_ID = 'A'
;