Recursive CTE (Common Table Expression)

Recursive CTE는 쉽게 계층구조를 처리해 줍니다.

거의 모든 데이터베이스에서 지원함으로  알고 계시면 계층구조를 쉽게 처리 할 수 있습니다.

Recursive CTE (Common Table Expression)는 CTE 중 자기 자신을 반복적으로 호출하는(재귀 호출) CTE입니다.

흔히 조직도와 같은 계층적 데이타의 처리나 BOM (Bill Of Materials, 부품재료표) 등을 쿼리하는데 많이 사용됩니다.

Recursive CTE는 특별한 구문을 가지고 있는데, 처음 부분에는 BASE 케이스에 해당하는 부분이고,

UNION ALL (혹은 UNION, EXCEPT, INTERSECT) 다음 부분은 Recursive 반복 부분에 해당됩니다.

아래는 CTE를 이용하여, HIERARCHY 정보를 생성하는 예제 쿼리입니다.

--CTE 사용
WITH CTE AS
(
 
    -- Base 부분
    SELECT DEPTCODE,LEFT('000000000000000', 15 - LEN(CONVERT(INT, SORTKEY) ))+CONVERT(VARCHAR(MAX),SORTKEY)+'; ' AS HIERARCHY FROM CMON_DEPARTMENT E
    WHERE MEMBEROF IS NULL
    UNION ALL
    -- Recursive 부분
    SELECT E.DEPTCODE, C.HIERARCHY+LEFT('000000000000000', 15 - LEN(CONVERT(INT, SORTKEY) ))+CONVERT(VARCHAR(MAX),SORTKEY)+'; '
    FROM CMON_DEPARTMENT E INNER JOIN CTE C
    ON C.DEPTCODE=E.MEMBEROF
)
 
SELECT * FROM CTE

 

CTE를 사용하지 않고 HIERARCHY를 Update 하려면 아래와 같이 함수를 만들어 사용해야 합니다.
문제는 한 Row 마다 해당 함수가 실행되기 때문에 실행계획을 보면 성능이 좋지 못할것으로 예상됩니다.

UPDATE 
CMON_DEPARTMENT 
SET HireachyCreate = [dbo].[Fn_HireachyCreate_S](DEPTCODE)

 

CTE를 사용하여 UPDATE를 하게될 경우 실행계획을 보았을때, 성능이 훨씬 향상된 것을 확인 할 수 있습니다.

 

--CTE 사용
WITH CTE AS
(
 
    -- Base 부분
    SELECT DEPTCODE,LEFT('000000000000000', 15 - LEN(CONVERT(INT, SORTKEY) ))+CONVERT(VARCHAR(MAX),SORTKEY)+'; ' AS HIERARCHY FROM CMON_DEPARTMENT E
    WHERE MEMBEROF IS NULL
    UNION ALL
    -- Recursive 부분
    SELECT E.DEPTCODE, C.HIERARCHY+LEFT('000000000000000', 15 - LEN(CONVERT(INT, SORTKEY) ))+CONVERT(VARCHAR(MAX),SORTKEY)+'; '
    FROM CMON_DEPARTMENT E INNER JOIN CTE C
    ON C.DEPTCODE=E.MEMBEROF
)
 
-- HIERARCHY Update
UPDATE 
G 
SET G.HIERARCHY=C.HIERARCHY
FROM CMON_DEPARTMENT AS G 
    INNER JOIN CTE C ON G.DEPTCODE=C.DEPTCODE
 

 

 

[출처]

http://www.sqlprogram.com/TIPS/tip-recursive-cte.aspx

Recursive CTE 사용 - SQL 프로그래밍 배우기 (Learn SQL Programming)