SQL BOOSTER 이어지는 이야기 .07

<쓸데 없는 MINUS???>

 

UNION과 UNION ALL은 두 데이터 집합을 상하로 결합시킨다. 아마도 이를 모르는 개발자는 없을 것이다. 반면에 MINUS 구문은 사용해 본적이 없거나 처음 접하는 개발자도 있을 것이다. MINUS는 상하의 두 데이터 집합간의 차집합을 구한다. 이 MINUS 연산은 도통 쓸데가 없다. 일반적인 조회 화면에서 MINUS가 포함된 SQL이 사용되는 경우는 거의 없기 때문이다. 하지만 MINUS는 데이터 검증 작업을 할 때 매우 유용하다. 필자는 MINUS 구문을 성능 개선 작업할 때 많이 사용한다.

 

SQL BOOSTER 에 이어지는 이야기들입니다.~!
SQL BOOSTER 를 보신 분들께 좀 더 도움을 드리고자 추가로 작성한 내용입니다.

www.aladin.co.kr/shop/wproduct.aspx?ItemId=216383877

SQL BOOSTER

설명의 편의상 반말체로 작성한 점 양해바랍니다.  pdf 파일도 첨부드리니 다운 받아 보셔도 됩니다.

SQL_Booster_이어지는이야기07.pdf

0.18MB

먼저 간단한 SQL을 통해 MINUS를 이해해보자.

[SQL-1] MINUS 예제
SELECT  *
FROM    (
         SELECT 'A' COL1, 1 COL2 FROM DUAL
         UNION ALL
         SELECT 'B' COL1, 3 COL2 FROM DUAL) T1
MINUS
SELECT  *
FROM    (SELECT 'A' COL1, 1 COL2 FROM DUAL
         UNION ALL
         SELECT 'B' COL1, 2 COL2 FROM DUAL
         ) T2

MINUS를 기준으로 위쪽 SQL(T1)을 기준 집합이라고 하자. 그리고 아래쪽 SQL(T2)을 참조 집합이라고 하자. [SQL-1]을 실행하면 기준 집합에는 있지만 참조 집합에는 없는 데이터만 조회할 수 있다. 아래와 같은 결과가 나온다.

[결과-1] MINUS 예제
COL1      COL2
========  =========
B	3

COL1이 B면서 COL2가 3인 데이터는 T1에는 있지만, T2에는 없다. 그러므로 해당 건만 결과에 나오게 된다. 이처럼 MINUS는 특정 컬럼이 아니라 SELECT절에 표시된 모든 컬럼을 비교한다.

 

성능 개선을 위해 SQL을 변경해야만 할 때가 있다. 힌트나 인덱스로는 성능 개선이 어려운 경우가 있기 때문이다. 간단한 변경은 문제 없지만, 약간 복잡한 변경을 하게 되면 변경 이전과 결과가 같은지 검증을 꼭 해야 한다. 이 때 MINUS를 사용 할 수 있다.

아래 SQL을 보자.(SQL BOOSTER에서 ROLLUP을 대신할 방법으로 소개했던 SQL이다.)

[SQL-2] UNION ALL을 이용한 중간합계
SELECT  TO_CHAR(T1.ORD_DT,'YYYYMM') ORD_YM ,T1.CUS_ID
        ,SUM(T1.ORD_AMT) ORD_AMT
FROM    T_ORD T1
WHERE   T1.CUS_ID IN ('CUS_0001','CUS_0002')
AND     T1.ORD_DT >= TO_DATE('20170301','YYYYMMDD')
AND     T1.ORD_DT < TO_DATE('20170501','YYYYMMDD')
GROUP BY TO_CHAR(T1.ORD_DT,'YYYYMM') ,T1.CUS_ID
UNION ALL
SELECT  TO_CHAR(T1.ORD_DT,'YYYYMM') ORD_YM ,'Total' CUS_ID
        ,SUM(T1.ORD_AMT) ORD_AMT
FROM    T_ORD T1
WHERE   T1.CUS_ID IN ('CUS_0001','CUS_0002')
AND     T1.ORD_DT >= TO_DATE('20170301','YYYYMMDD')
AND     T1.ORD_DT < TO_DATE('20170501','YYYYMMDD')
GROUP BY TO_CHAR(T1.ORD_DT,'YYYYMM')
UNION ALL
SELECT  'Total' ORD_YM ,'Total' CUS_ID
        ,SUM(T1.ORD_AMT) ORD_AMT
FROM    T_ORD T1
WHERE   T1.CUS_ID IN ('CUS_0001','CUS_0002')
AND     T1.ORD_DT >= TO_DATE('20170301','YYYYMMDD')
AND     T1.ORD_DT < TO_DATE('20170501','YYYYMMDD')

위와 같은 SQL은 ROLLUP으로 변경하는 것이 성능에 유리할 수 있다. 실행계획까지 확인해 ROLLUP이 더 좋다면 SQL을 변경하도록 한다. 이때, 성능보다 중요한 건 데이터의 정확성이다. 성능을 얻고 정확성을 잃을 수는 없다. 그러므로 반드시 변경한 SQL의 데이터가 맞는지 확인해야 한다. 다음과 같이 MINUS를 사용해 확인할 수 있다. MINUS를 기준으로 위쪽은 ROLLUP, 아래쪽은 UNION ALL이다.

 

[SQL-3] MINUS를 이용한 SQL 검증
--ROLLUP을 이용한 SQL
SELECT  CASE WHEN GROUPING(TO_CHAR(T1.ORD_DT,'YYYYMM')) = 1 THEN 'Total'
             ELSE TO_CHAR(T1.ORD_DT,'YYYYMM') END ORD_YM
        ,CASE WHEN GROUPING(T1.CUS_ID) = 1 THEN 'Total' ELSE T1.CUS_ID END CUS_ID
        ,SUM(T1.ORD_AMT) ORD_AMT
        ,COUNT(*) OVER() TTL_CNT
FROM    T_ORD T1
WHERE   T1.CUS_ID IN ('CUS_0001','CUS_0002')
AND     T1.ORD_DT >= TO_DATE('20170301','YYYYMMDD')
AND     T1.ORD_DT < TO_DATE('20170501','YYYYMMDD')
GROUP BY ROLLUP(TO_CHAR(T1.ORD_DT,'YYYYMM') ,T1.CUS_ID)
MINUS
-- 기존 SQL(UNION ALL)
SELECT  A.*, COUNT(*) OVER() TTL_CNT
FROM    (
        SELECT  TO_CHAR(T1.ORD_DT,'YYYYMM') ORD_YM ,T1.CUS_ID
                ,SUM(T1.ORD_AMT) ORD_AMT
        FROM    T_ORD T1
        WHERE   T1.CUS_ID IN ('CUS_0001','CUS_0002')
        AND     T1.ORD_DT >= TO_DATE('20170301','YYYYMMDD')
        AND     T1.ORD_DT < TO_DATE('20170501','YYYYMMDD')
        GROUP BY TO_CHAR(T1.ORD_DT,'YYYYMM') ,T1.CUS_ID
        UNION ALL
        SELECT  TO_CHAR(T1.ORD_DT,'YYYYMM') ORD_YM ,'Total' CUS_ID
                ,SUM(T1.ORD_AMT) ORD_AMT
        FROM    T_ORD T1
        WHERE   T1.CUS_ID IN ('CUS_0001','CUS_0002')
        AND     T1.ORD_DT >= TO_DATE('20170301','YYYYMMDD')
        AND     T1.ORD_DT < TO_DATE('20170501','YYYYMMDD')
        GROUP BY TO_CHAR(T1.ORD_DT,'YYYYMM')
        UNION ALL
        SELECT  'Total' ORD_YM ,'Total' CUS_ID
                ,SUM(T1.ORD_AMT) ORD_AMT
        FROM    T_ORD T1
        WHERE   T1.CUS_ID IN ('CUS_0001','CUS_0002')
        AND     T1.ORD_DT >= TO_DATE('20170301','YYYYMMDD')
        AND     T1.ORD_DT < TO_DATE('20170501','YYYYMMDD')
        ) A;

위 SQL을 실행하면 조회되는 데이터가 없다. MINUS를 기준으로 위쪽 SQL 결과와 아래쪽 SQL의 결과가 완전히 같기 때문이다. 6번 라인과 14번 라인을 보면 두 데이터 집합에 COUNT(*) OVER()를 추가한 것을 볼 수 있다. 데이터 건수까지 완전히 같은지 확인하기 위해서다.

이처럼 MINUS 연산은 데이터를 검증하기 위해서 많이 사용한다. 이행한 데이터가 문제 없는지, 변경한 SQL이 문제 없는지 확인하기 위해서 말이다. 그러므로 “쓸데 없는 MINUS”는 아니다. 나름 유용하게 쓸 데가 있다. 잘 기억하고 사용할 수 있기 바란다.

 

 

오늘은 여기까지입니다. 감사합니다.