금액이 두 배로 나와 DISTINCT를 붙였더니 이번에는 주문 금액이 원장의 73%로 찍힌다

중급SELECT한빛유통352 / 700

상황

정산팀의 「월별 주문 판매 집계」는 기간(기본 최근 1년)의 주문을 달별로 묶어 주문 수, 주문 금액, 주문상세 줄 수, 판매 수량을 보여 준다. 2022년에 처음 만들 때 주문 금액이 원장의 두 배쯤으로 나와서, 개발자가 주문 수와 주문 금액에 DISTINCT를 붙여 고쳤다. 그때는 주문 수가 원장과 맞는 것만 확인했다. 이번 분기 재무팀이 2024년 6월 주문 금액(905,627,800원)이 원장(1,241,286,300원)의 73%라며 반려했다. 박팀장은 "숫자를 맞추고, 왜 그렇게 됐는지와 비용도 같이 정리해 달라"고 했다.

테이블과 인덱스

T_ORD 주문: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · ORD_DT는 시분초 있음 · ORD_AMT는 100원 단위다. 2024년 주문은 50,112건, 6월은 4,110건이다.
T_ORD_DET 주문상세: 200,000행 · 896블록 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) · 주문당 1 ~ 3줄(평균 2.0줄). 2024년 주문의 상세는 100,224줄이다.
주문상세가 없는 주문은 지금 0건이다. 금액이 같은 주문은 흔하다(6월 4,110건의 서로 다른 금액은 2,996가지).
원장은 T_ORD만 달별로 더한 값이다. 통계는 최신이다.

원본 SQL

sql

SELECT TO_CHAR(o.ORD_DT, 'YYYYMM')  AS ym,
       COUNT(DISTINCT o.ORD_SEQ)    AS ord_cnt,
       SUM(DISTINCT o.ORD_AMT)      AS ord_amt,      -- 2022.08 : 금액이 두 배로 나와 DISTINCT 를 붙였다
       COUNT(*)                     AS det_lines,
       SUM(d.ORD_QTY)               AS sale_qty
  FROM T_ORD o, T_ORD_DET d
 WHERE o.ORD_DT >= :from_dt AND o.ORD_DT < :to_dt
   AND d.ORD_SEQ = o.ORD_SEQ
 GROUP BY TO_CHAR(o.ORD_DT, 'YYYYMM')
 ORDER BY ym

실행계획 학습용 예시

xplan

[2024년 1월 ~ 12월]
-----------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |      1 |        |     12 |00:00:00.21 |    1792 |          |
|   1 |  SORT GROUP BY        |           |      1 |     12 |     12 |00:00:00.21 |    1792 | 6144K (0)|
|*  2 |   FILTER              |           |      1 |        |    100K|00:00:00.13 |    1792 |          |
|*  3 |    HASH JOIN          |           |      1 |    100K|    100K|00:00:00.12 |    1792 | 3712K (0)|
|*  4 |     TABLE ACCESS FULL | T_ORD     |      1 |  50000 |  50112 |00:00:00.01 |     896 |          |
|   5 |     TABLE ACCESS FULL | T_ORD_DET |      1 |    200K|    200K|00:00:00.02 |     896 |          |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:FROM_DT<:TO_DT)
   3 - access("D"."ORD_SEQ"="O"."ORD_SEQ")
   4 - filter(("O"."ORD_DT">=:FROM_DT AND "O"."ORD_DT"<:TO_DT))

[6월 한 줄 · 원장과 대조]
          ORD_CNT         ORD_AMT   DET_LINES   SALE_QTY
  화면       4,110     905,627,800       8,231     16,508
  원장       4,110   1,241,286,300

문제

2022년에 금액이 두 배로 나온 까닭과, DISTINCT를 붙인 뒤 주문 수는 맞는데 금액만 작아진 까닭을 설명하시오.
DISTINCT 없이 원장과 맞는 SQL로 고치시오. DISTINCT 집계가 실행계획에서 무엇을 더 시키는지도 쓰시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

-- 조인 결과를 주문 단위로 먼저 묶어 1:1 로 되돌린 뒤 달로 묶는다 (값을 지우는 DISTINCT 가 필요 없다)
SELECT ym,
       COUNT(*)        AS ord_cnt,
       SUM(ORD_AMT)    AS ord_amt,
       SUM(det_lines)  AS det_lines,
       SUM(sale_qty)   AS sale_qty
  FROM (SELECT TO_CHAR(o.ORD_DT, 'YYYYMM') AS ym, o.ORD_SEQ, o.ORD_AMT,
               COUNT(*) AS det_lines, SUM(d.ORD_QTY) AS sale_qty
          FROM T_ORD o, T_ORD_DET d
         WHERE o.ORD_DT >= :from_dt AND o.ORD_DT < :to_dt
           AND d.ORD_SEQ = o.ORD_SEQ
         GROUP BY TO_CHAR(o.ORD_DT, 'YYYYMM'), o.ORD_SEQ, o.ORD_AMT)
 GROUP BY ym
 ORDER BY ym;

-- 전체 기간을 볼 때는 상세를 주문별로 먼저 묶고 붙여도 같다 (상세 20만 줄을 다 묶으므로 기간이 짧을수록 손해)
-- SELECT TO_CHAR(o.ORD_DT, 'YYYYMM'), COUNT(*), SUM(o.ORD_AMT), SUM(d.det_lines), SUM(d.sale_qty)
--   FROM T_ORD o,
--        (SELECT ORD_SEQ, COUNT(*) AS det_lines, SUM(ORD_QTY) AS sale_qty FROM T_ORD_DET GROUP BY ORD_SEQ) d
--  WHERE o.ORD_DT >= :from_dt AND o.ORD_DT < :to_dt
--    AND d.ORD_SEQ = o.ORD_SEQ
--  GROUP BY TO_CHAR(o.ORD_DT, 'YYYYMM');

개선 후 실행계획 학습용 예시

xplan

-------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |      1 |        |     12 |00:00:00.12 |    1792 |          |
|   1 |  SORT GROUP BY          |           |      1 |     12 |     12 |00:00:00.12 |    1792 | 2048 (0) |
|   2 |   VIEW                  |           |      1 |  50000 |  50112 |00:00:00.11 |    1792 |          |
|   3 |    HASH GROUP BY        |           |      1 |  50000 |  50112 |00:00:00.11 |    1792 | 4407K (0)|
|*  4 |     FILTER              |           |      1 |        |    100K|00:00:00.07 |    1792 |          |
|*  5 |      HASH JOIN          |           |      1 |    100K|    100K|00:00:00.06 |    1792 | 3712K (0)|
|*  6 |       TABLE ACCESS FULL | T_ORD     |      1 |  50000 |  50112 |00:00:00.01 |     896 |          |
|   7 |       TABLE ACCESS FULL | T_ORD_DET |      1 |    200K|    200K|00:00:00.02 |     896 |          |
-------------------------------------------------------------------------------------------------------------

[6월 한 줄]  4,110건 · 1,241,286,300원 · 8,231줄 · 16,508개 (원장과 같다)

모범 답안 서술

주문 한 건에 주문상세가 1 ~ 3줄 있으므로 조인하면 주문 한 줄이 상세 줄 수만큼 늘어난다(3번 줄, 50,112행 → 100K행). 이 상태에서 SUM(o.ORD_AMT)를 하면 주문 금액이 상세 줄 수만큼 곱해져 평균 2배가 된다. 2022년의 두 배가 이것이다. COUNT(DISTINCT o.ORD_SEQ)는 키를 세므로 주문 수가 맞는다. 하지만 SUM(DISTINCT o.ORD_AMT)는 중복된 줄을 지우는 것이 아니라 같은 값을 하나로 친다. 한 달 안에 금액이 같은 주문은 흔해서(6월 4,110건의 금액이 2,996가지) 같은 금액의 두 번째 주문부터는 더해지지 않는다. 그래서 주문 수는 맞고 금액만 원장의 73%로 작다. 주문 수가 맞아서 고쳐진 것처럼 보였던 것이 더 위험했다. 고친 SQL은 조인 결과를 먼저 주문 단위(ORD_SEQ)로 묶어 주문 한 줄마다 상세 줄 수와 판매 수량을 만든다. 주문과 1:1로 돌아왔으므로 바깥에서 달로 묶을 때는 COUNT(*)와 SUM(ORD_AMT)를 그대로 쓴다. 6월은 4,110건, 1,241,286,300원으로 원장과 같다. 비용을 보면 DISTINCT 집계는 값마다 중복을 가려야 해서 해시 집계 대신 정렬(1번 줄 SORT GROUP BY)로 조인 결과 100K행을 줄 세운다(6,144K). 고친 SQL은 주문 단위 해시 집계(4,407K) 뒤에 12줄만 정렬한다. 읽는 블록은 1,792로 같고 시간은 0.21초 → 0.12초다. 이 문제에서 얻는 것은 먼저 정합성이고, 덤으로 큰 정렬 한 번이 사라진다.

해설

상세를 주문별로 먼저 묶고 붙이는 답: T_ORD_DET를 ORD_SEQ로 묶은 인라인 뷰를 주문에 조인해도 1:1이 되어 맞는다. 다만 기간과 상관없이 상세 20만 줄을 모두 묶으므로(10만 묶음), 최근 1년이나 한 달을 볼 때는 조인 뒤에 묶는 쪽이 싸다. 전체 기간 리포트라면 두 방법이 비슷하다(교재 08화 고치는 법 5번).
스칼라 서브쿼리로 상세를 붙이는 답: 주문마다 상세를 찾으므로 1년이면 50,112번 PK를 찾아간다(SUBQ-009의 모양). 주문 수가 적은 화면에만 맞다.
주문 수만 보면 속는다: 달마다 건수와 금액 합계를 원장과 함께 대조한다. MINUS는 중복을 못 보므로 건수, 합계와 같이 본다(교재 08화 §10).
주문상세가 없는 주문이 생기면 내부 조인이라 그 주문이 주문 수와 금액에서 빠진다. 그런 주문도 세야 한다면 바깥 조인으로 바꾸고 상세 줄 수를 0으로 둔다. 지금은 0건이지만 FK가 막지 않으므로 대조 쿼리를 정합성 점검에 넣는다.
COUNT(DISTINCT)가 맞는 집계도 있다(그달 팔린 상품 종류 수처럼 키를 세는 경우). 문제는 DISTINCT 자체가 아니라, 금액처럼 값이 겹칠 수 있는 컬럼에 붙여 '중복 줄 지우기'로 쓴 것이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황