구매팀 「상품별 발주 대비 판매」 화면의 판매 수량이 POS 일보의 2%도 안 된다

중급SELECT유통296 / 700

상황

구매팀이 새로 만든 「상품별 발주 대비 판매」 화면을 시험하던 오과장이 이렇게 알려 왔다. "6월 판매 수량 합계가 POS 일보는 61,214개인데 이 화면은 1,138개다. 발주도 원장은 24,992개인데 21,872개로 나온다." 개발자는 발주와 판매를 한 목록으로 이어 붙인 뒤 상품별로 더했다. 두 목록에 겹치는 행은 없으니 UNION이나 UNION ALL이나 같다고 생각했다고 한다.

테이블과 인덱스

CF12 발주: 734행 · 10블록 · ORD_DT VARCHAR2(8) · 2023년 6월 28건 / CF13 발주상품: 7,102행 · 57블록 · 6월 271줄(상품 68개)
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · 판매번호는 판매일자 8자리로 시작한다 · 6월 45,935줄(상품 88개) · 반품은 수량이 음수로 들어간다
CF01 상품: 109행 · 5블록 · 통계 최신
화면은 :v_from = '20230601', :v_to = '20230630', :v_next = '20230701'을 넘긴다

원본 SQL

sql

SELECT x.ITM_NO, i.ITM_NM, SUM(x.ORD_QTY) AS ORD_QTY, SUM(x.SLE_QTY) AS SLE_QTY
  FROM (SELECT d.ITM_NO, d.ORD_QTY, 0 AS SLE_QTY
          FROM CF12 h, CF13 d
         WHERE h.ORD_DT BETWEEN :v_from AND :v_to
           AND d.ORD_NO = h.ORD_NO
        UNION
        SELECT b.ITM_NO, 0, b.SLE_QTY
          FROM CF11 b
         WHERE b.SLE_NO >= :v_from
           AND b.SLE_NO <  :v_next) x,
       CF01 i
 WHERE i.ITM_NO = x.ITM_NO
 GROUP BY x.ITM_NO, i.ITM_NM
 ORDER BY x.ITM_NO

실행계획 학습용 예시

xplan

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name    | Starts | E-Rows | A-Rows |      A-Time | Buffers |  Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |         |      1 |        |     97 | 00:00:00.09 |    1396 |           |
|   1 |  SORT GROUP BY                             |         |      1 |    109 |     97 | 00:00:00.09 |    1396 |  9216 (0) |
|*  2 |   HASH JOIN                                |         |      1 |  46206 |    638 | 00:00:00.09 |    1396 | 1236K (0) |
|   3 |    TABLE ACCESS FULL                       | CF01    |      1 |    109 |    109 | 00:00:00.01 |       5 |           |
|   4 |    VIEW                                    |         |      1 |  46206 |    638 | 00:00:00.09 |    1391 |           |
|   5 |     SORT UNIQUE                            |         |      1 |  46206 |    638 | 00:00:00.09 |    1391 | 2048K (0) |
|   6 |      UNION-ALL                             |         |      1 |        |  46206 | 00:00:00.03 |    1391 |           |
|*  7 |       HASH JOIN                            |         |      1 |    262 |    271 | 00:00:00.01 |      67 | 1321K (0) |
|*  8 |        TABLE ACCESS FULL                   | CF12    |      1 |     27 |     28 | 00:00:00.01 |      10 |           |
|   9 |        TABLE ACCESS FULL                   | CF13    |      1 |   7102 |   7102 | 00:00:00.01 |      57 |           |
|  10 |       TABLE ACCESS BY INDEX ROWID BATCHED  | CF11    |      1 |  45935 |  45935 | 00:00:00.02 |    1324 |           |
|* 11 |        INDEX RANGE SCAN                    | CF11_PK |      1 |  45935 |  45935 | 00:00:00.01 |     176 |           |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("I"."ITM_NO"="X"."ITM_NO")
   7 - access("D"."ORD_NO"="H"."ORD_NO")
   8 - filter(("H"."ORD_DT">=:V_FROM AND "H"."ORD_DT"<=:V_TO))
  11 - access("B"."SLE_NO">=:V_FROM AND "B"."SLE_NO"<:V_NEXT)

문제

판매 수량과 일부 발주 수량이 원장보다 적게 나오는 이유를 실행계획의 줄 번호와 A-Rows로 설명하시오.
원장과 맞도록 SQL을 고치시오. 발주만 있는 상품과 판매만 있는 상품이 모두 나와야 하는데, 고친 SQL이 그것을 어떻게 지키는지도 쓰시오.
두 집계를 FULL OUTER JOIN으로 붙이는 안과 비교하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 같은 값의 행이 여러 번 나오는 것이 정상인 데이터 → 행을 줄이지 않는 UNION ALL
-- 갈래마다 상품별로 먼저 묶고(271 → 68, 45,935 → 88) 이어 붙인 뒤 한 번 더 묶는다
SELECT x.ITM_NO, i.ITM_NM, SUM(x.ORD_QTY) AS ORD_QTY, SUM(x.SLE_QTY) AS SLE_QTY
  FROM (SELECT d.ITM_NO, SUM(d.ORD_QTY) AS ORD_QTY, 0 AS SLE_QTY
          FROM CF12 h, CF13 d
         WHERE h.ORD_DT BETWEEN :v_from AND :v_to
           AND d.ORD_NO = h.ORD_NO
         GROUP BY d.ITM_NO
        UNION ALL
        SELECT b.ITM_NO, 0, SUM(b.SLE_QTY)
          FROM CF11 b
         WHERE b.SLE_NO >= :v_from
           AND b.SLE_NO <  :v_next
         GROUP BY b.ITM_NO) x,
       CF01 i
 WHERE i.ITM_NO = x.ITM_NO
 GROUP BY x.ITM_NO, i.ITM_NM
 ORDER BY x.ITM_NO

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

xplan

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name    | Starts | E-Rows | A-Rows |      A-Time | Buffers |  Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |         |      1 |        |     97 | 00:00:00.05 |    1396 |           |
|   1 |  SORT GROUP BY                             |         |      1 |    109 |     97 | 00:00:00.05 |    1396 |  9216 (0) |
|*  2 |   HASH JOIN                                |         |      1 |    156 |    156 | 00:00:00.05 |    1396 | 1236K (0) |
|   3 |    TABLE ACCESS FULL                       | CF01    |      1 |    109 |    109 | 00:00:00.01 |       5 |           |
|   4 |    VIEW                                    |         |      1 |    156 |    156 | 00:00:00.05 |    1391 |           |
|   5 |     UNION-ALL                              |         |      1 |        |    156 | 00:00:00.05 |    1391 |           |
|   6 |      HASH GROUP BY                         |         |      1 |     68 |     68 | 00:00:00.01 |      67 | 1180K (0) |
|*  7 |       HASH JOIN                            |         |      1 |    262 |    271 | 00:00:00.01 |      67 | 1321K (0) |
|*  8 |        TABLE ACCESS FULL                   | CF12    |      1 |     27 |     28 | 00:00:00.01 |      10 |           |
|   9 |        TABLE ACCESS FULL                   | CF13    |      1 |   7102 |   7102 | 00:00:00.01 |      57 |           |
|  10 |      HASH GROUP BY                         |         |      1 |     88 |     88 | 00:00:00.04 |    1324 | 1210K (0) |
|  11 |       TABLE ACCESS BY INDEX ROWID BATCHED  | CF11    |      1 |  45935 |  45935 | 00:00:00.02 |    1324 |           |
|* 12 |        INDEX RANGE SCAN                    | CF11_PK |      1 |  45935 |  45935 | 00:00:00.01 |     176 |           |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("I"."ITM_NO"="X"."ITM_NO")
   7 - access("D"."ORD_NO"="H"."ORD_NO")
   8 - filter(("H"."ORD_DT">=:V_FROM AND "H"."ORD_DT"<=:V_TO))
  12 - access("B"."SLE_NO">=:V_FROM AND "B"."SLE_NO"<:V_NEXT)

[대조]  판매 수량 1,138 → 61,214 (POS 일보 61,214) · 발주 수량 21,872 → 24,992 (원장 24,992) · 상품 97개

모범 답안 서술

5번 줄 SORT UNIQUE가 6번 줄에서 받은 46,206행(발주 271줄 + 판매 45,935줄)을 638행으로 줄였다. UNION은 SELECT 목록의 칸이 모두 같은 행을 하나로 친다. 판매 갈래의 행은 (ITM_NO, 0, SLE_QTY) 세 칸뿐이라, 같은 상품을 같은 수량으로 판 줄은 영수증이 달라도 모두 같은 행이 된다. 한 상품을 6월에 1개씩 수백 번 팔았어도 (상품, 0, 1) 한 줄만 남는다. 그래서 판매 수량이 61,214개가 아니라 1,138개(2%)로 나왔다. 발주 갈래도 같은 상품을 같은 수량으로 두 번 이상 발주한 줄이 합쳐져 24,992개가 21,872개가 됐다. UNION을 UNION ALL로 바꾼다. 수불처럼 같은 값의 행이 여러 번 나오는 것이 정상인 데이터를 합칠 때는 행을 줄이면 안 된다. 갈래마다 상품별로 먼저 묶은 뒤(GROUP BY ITM_NO) 이어 붙이면 붙일 행이 156행으로 줄고, 바깥에서 상품별로 한 번 더 더해 발주와 판매를 한 줄로 모은다. 판매 수량 61,214개, 발주 수량 24,992개로 원장과 맞는다. 46,206행을 정렬하던 SORT UNIQUE도 사라져 A-Time이 0.09 → 0.05초가 된다(Buffers 1,396은 같다). 발주만 있는 상품은 판매 갈래에 행이 없고, 판매만 있는 상품은 발주 갈래에 행이 없다. 이어 붙인 뒤 묶으면 어느 한쪽에만 있는 상품도 그 갈래의 행으로 남고, 없는 쪽 칸은 갈래에서 넣은 0이 된다. 결과는 상품 97개다(둘 다 있는 상품 59개, 발주만 9개, 판매만 29개). 두 집계를 내부 조인으로 붙였다면 한쪽에만 있는 38개가 빠졌을 것이다. 두 집계를 FULL OUTER JOIN으로 붙여도 같은 결과를 낼 수 있다. 하지만 상품번호를 NVL(a.ITM_NO, b.ITM_NO)로 다시 맞춰야 하고, 없는 쪽 수량도 NVL(…, 0)로 바꿔야 한다. 옵티마이저가 FULL OUTER JOIN을 아우터 조인과 안티 조인 두 갈래로 풀면 같은 집합을 두 번 읽기도 한다. 행을 이어 붙이고 한 번 묶는 UNION ALL이 더 단순하고 읽는 양도 같거나 적다.

해설

UNION은 '집합'이다: 교재 08화의 SUM(DISTINCT 금액)이 같은 값을 하나로 친 것처럼, UNION, MINUS, INTERSECT는 행을 값으로 비교해 하나로 친다. 같은 값의 행이 여러 번 나오는 것이 정상인 데이터(판매상품 줄, 발주 줄)에는 쓰지 않는다.
판매번호를 SELECT에 넣어 피하는 안: 판매 갈래에 SLE_NO를 넣으면 행이 거의 유일해져 숫자는 맞아진다. 그래도 46,206행 정렬은 남고, 우연히 같은 행이 생기면 다시 틀린다. 원인을 피했을 뿐 고친 것이 아니다.
대조는 건수와 합계로: 이 화면을 원래 SQL과 MINUS로 비교하면 통과할 수도 있다. MINUS도 중복을 보지 못한다. 상품별 수량 합계를 POS 일보와 발주 원장에 맞춘다(교재 08화 §10).
판매 갈래의 합계는 반품(음수)을 뺀 순판매 수량이다. POS 일보의 총판매와 순판매 가운데 어느 숫자와 맞출지 구매팀과 먼저 정한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황