평가 수와 평점은 맞는데 매출만 원장의 정확히 50배다

중급SELECT한빛유통239 / 700

상황

분기 자료의 「상품별 매출 · 평점」 표를 두고 정산팀 조민우 씨가 말한다. "평가 수 50건과 평균 평점은 리뷰 화면과 똑같은데, 재무팀이 매출이 원장과 안 맞는다고 반려했습니다. 1위 ITM00071이 화면에서는 22,216,620,000, 원장에서는 444,332,400이에요." 소스 이력에는 2020-03 평가 수가 100,000 으로 나와서 COUNT(DISTINCT) 로 수정이라는 한 줄이 있다. 그 커밋을 할 때 금액 칸은 확인하지 않았다.

테이블과 인덱스

M_ITM 상품: 100행 · 5블록 · PK_M_ITM (ITM_ID)
T_ORD_DET 주문상세: 200,000행 · 전체 스캔 807블록 (교재 08화 실측) · 상품당 2,000줄 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO)
T_ITM_EVL 상품평가: 5,000행 · 48블록 · 상품당 50건 · PK_T_ITM_EVL (ITM_ID, EVL_LST_NO) (2020-02에 앱 리뷰를 연 뒤부터 다건이고, 그 전에는 상품당 1건이었다)
원장(재무팀) = 주문상세만 상품별로 더한 것 · 회사 전체 매출 30,272,557,700
100개 상품 전부에 판매와 평가가 있다 · 통계 최신

원본 SQL

sql

SELECT i.ITM_ID, i.ITM_NM,
       SUM(d.ORD_QTY * d.UNT_PRC)   AS sale_amt,
       COUNT(DISTINCT e.EVL_LST_NO) AS evl_cnt,
       ROUND(AVG(e.EVL_PT), 2)      AS avg_pt
  FROM M_ITM i, T_ORD_DET d, T_ITM_EVL e
 WHERE d.ITM_ID = i.ITM_ID
   AND e.ITM_ID = i.ITM_ID
 GROUP BY i.ITM_ID, i.ITM_NM
 ORDER BY sale_amt DESC

실행계획 학습용 예시 · 교재 08화 규모

xplan

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Starts | E-Rows | A-Rows |    A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |      1 |        |    100 | 00:00:01.08 |     860 |
|   1 |  SORT ORDER BY         |           |      1 |    100 |    100 | 00:00:01.08 |     860 |
|   2 |   SORT GROUP BY        |           |      1 |    100 |    100 | 00:00:01.08 |     860 |
|*  3 |    HASH JOIN           |           |      1 |    10M |    10M | 00:00:00.39 |     860 |
|*  4 |     HASH JOIN          |           |      1 |   5000 |   5000 | 00:00:00.01 |      53 |
|   5 |      TABLE ACCESS FULL | M_ITM     |      1 |    100 |    100 | 00:00:00.01 |       5 |
|   6 |      TABLE ACCESS FULL | T_ITM_EVL |      1 |   5000 |   5000 | 00:00:00.01 |      48 |
|   7 |     TABLE ACCESS FULL  | T_ORD_DET |      1 |   200K |   200K | 00:00:00.01 |     807 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("D"."ITM_ID"="I"."ITM_ID")
   4 - access("E"."ITM_ID"="I"."ITM_ID")

문제

매출이 정확히 50배가 된 까닭을 실행계획의 줄 번호와 A-Rows로 설명하시오. 평가 수와 평균 평점은 왜 맞게 나왔는지도 설명하시오.
화면 칸(상품 · 매출 · 평가 수 · 평균 평점)은 그대로 두고, 매출이 원장과 같아지도록 SQL을 고치시오.
고친 SQL이 맞다는 것을 무엇과 어떻게 대조해 보일지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 먼저 묶고 붙이기 : 양쪽 '많은 쪽' 을 상품당 한 줄로 만든 뒤 1 : 1 로 조인
SELECT i.ITM_ID, i.ITM_NM, d.sale_amt, e.evl_cnt, e.avg_pt
  FROM M_ITM i,
       (SELECT ITM_ID, SUM(ORD_QTY * UNT_PRC) AS sale_amt
          FROM T_ORD_DET
         GROUP BY ITM_ID) d,
       (SELECT ITM_ID, COUNT(*) AS evl_cnt, ROUND(AVG(EVL_PT), 2) AS avg_pt
          FROM T_ITM_EVL
         GROUP BY ITM_ID) e
 WHERE d.ITM_ID = i.ITM_ID
   AND e.ITM_ID = i.ITM_ID
 ORDER BY d.sale_amt DESC;

-- 정합성 대조 ① 합계 : 위 결과의 SUM(sale_amt) = 원장 합계 (30,272,557,700)
SELECT SUM(ORD_QTY * UNT_PRC) AS ledger_amt FROM T_ORD_DET;

-- 정합성 대조 ② 상품별 MINUS — 원장 - 화면 0 행 (반대 방향도 같은 모양으로 0 행) · 건수 100 = 100
SELECT ITM_ID, SUM(ORD_QTY * UNT_PRC) AS amt
  FROM T_ORD_DET
 GROUP BY ITM_ID
MINUS
SELECT d.ITM_ID, d.sale_amt
  FROM (SELECT ITM_ID, SUM(ORD_QTY * UNT_PRC) AS sale_amt FROM T_ORD_DET GROUP BY ITM_ID) d,
       (SELECT ITM_ID, COUNT(*) AS evl_cnt FROM T_ITM_EVL GROUP BY ITM_ID) e
 WHERE e.ITM_ID = d.ITM_ID;

개선 후 실행계획 학습용 예시 · 교재 08화 규모

xplan

------------------------------------------------------------------------------------------------
| Id  | Operation               | Name      | Starts | E-Rows | A-Rows |    A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |      1 |        |    100 | 00:00:00.05 |     860 |
|   1 |  SORT ORDER BY          |           |      1 |    100 |    100 | 00:00:00.05 |     860 |
|*  2 |   HASH JOIN             |           |      1 |    100 |    100 | 00:00:00.05 |     860 |
|*  3 |    HASH JOIN            |           |      1 |    100 |    100 | 00:00:00.01 |      53 |
|   4 |     TABLE ACCESS FULL   | M_ITM     |      1 |    100 |    100 | 00:00:00.01 |       5 |
|   5 |     VIEW                |           |      1 |    100 |    100 | 00:00:00.01 |      48 |
|   6 |      HASH GROUP BY      |           |      1 |    100 |    100 | 00:00:00.01 |      48 |
|   7 |       TABLE ACCESS FULL | T_ITM_EVL |      1 |   5000 |   5000 | 00:00:00.01 |      48 |
|   8 |    VIEW                 |           |      1 |    100 |    100 | 00:00:00.05 |     807 |
|   9 |     HASH GROUP BY       |           |      1 |    100 |    100 | 00:00:00.05 |     807 |
|  10 |      TABLE ACCESS FULL  | T_ORD_DET |      1 |   200K |   200K | 00:00:00.01 |     807 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("D"."ITM_ID"="I"."ITM_ID")
   3 - access("E"."ITM_ID"="I"."ITM_ID")

[정합성 대조]
  원장 합계 (T_ORD_DET 전체)       30,272,557,700
  고친 SQL 의 sale_amt 합계         30,272,557,700   (고치기 전 1,513,627,885,000 = 정확히 50 배)
  상품 수                           100 = 100
  상품별 MINUS 양방향               0 행 / 0 행
  1 위 ITM00071                    444,332,400 (원장과 같다 · 고치기 전 22,216,620,000)

모범 답안 서술

3번 줄의 해시 조인에서 200K 행(7번 줄)이 10M 행으로 불어난다. 상품 하나에 주문상세 2,000줄과 평가 50줄이 매달려 있어서(M : 1 : M), 상품번호로 짝지으면 2,000 × 50 = 100,000줄이 되고 주문상세 한 줄이 평가 수만큼 50번 더해진다. 그래서 22,216,620,000 = 원장 444,332,400 × 50이 된다. 평가 수는 2020-03에 COUNT(DISTINCT)로 가려 놓아서 50으로 맞았다. 평균 평점은 모든 평가가 똑같이 2,000번씩 복제되어 평균이 그대로였다. 맞아 보이는 두 칸 때문에 틀린 조인이 드러나지 않았다. 양쪽의 '많은 쪽'을 각각 상품별로 먼저 묶어 상품당 한 줄로 만든 뒤(주문상세는 매출 100줄, 평가는 건수와 평균 100줄) 1 : 1로 붙인다. 조인 행은 10M → 100으로, A-Time은 1.08 → 0.05초로 준다. Buffers는 860 → 860으로 그대로다. 이것은 성능 리비전이 아니라 정합성 리비전이다. 맞다는 증거는 Buffers가 아니라 원장 대조다. 원장(주문상세만 상품별로 더한 것)과 비교해 ① 합계 30,272,557,700 = 30,272,557,700, ② 건수 100 = 100, ③ 상품별 MINUS 양방향 0 / 0을 보인다. 금액을 SUM(DISTINCT …)로 덮으면 금액이 같은 서로 다른 주문상세가 하나로 합쳐져 합계가 작아지고 순위가 바뀐다.

해설

함정 셋 (교재 08화 실측): 첫째, SUM(DISTINCT 금액)을 쓰면 합계가 작아지고 순위가 바뀐다 (원장 12위인 ITM00006 286,527,900이 1위가 된다). 둘째, 주문상세만 묶고 평가 원본에 붙이면 같은 줄이 50번 나오고(MINUS로는 잡히지 않는다), 그것을 다시 더하면 또 50배가 된다. 셋째, 평가 조인을 아예 지우면 '평가가 있는 상품만'이라는 요구까지 지워진다.
오라클이 이미 고쳐 쓰는 경우: Group-by Placement가 적용되면(VW_GBC_ · VW_GBF_ · PLACE_GROUP_BY) 계획에 10M 행이 보이지 않는다. A-Rows는 100인데 답은 여전히 50배다. 쿼리 변환은 SQL의 뜻을 그대로 지키므로, 틀린 뜻도 그대로 지킨다.
다른 정답: 매출은 주문상세만 상품별로 묶고, 평가 칸은 스칼라 서브쿼리 (SELECT COUNT(*) FROM T_ITM_EVL e WHERE e.ITM_ID = d.ITM_ID)로 구한다. 상품 수만큼 100번만 호출되므로 싸다. 원본의 내부 조인이 뜻하던 조건(평가가 있는 상품만)을 지키려면 EXISTS를 함께 건다.
평가가 없는 신상품: 원본과 고친 SQL 모두 내부 조인이라 평가가 없는 상품은 빠진다. 이런 상품도 보여 줘야 한다면 평가 쪽을 외부 조인으로 바꾼다. 어느 쪽인지는 요구 사항을 물어서 정한다.
MINUS는 중복을 보지 못한다 (같은 줄이 50번 나와도 0 / 0). 그래서 건수를 함께 대조한다 (교재 08화 「정합성 회귀 체크리스트」).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 교재 08화 규모 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 교재 08화 규모 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황