거래처 납품 상품의 판매 실적표에서 상품마다 매출이 서로 다른 배수로 부풀었다

실전SELECT유통266 / 700

상황

구매팀의 「거래처 납품 상품 판매 실적」은 거래처를 고르면 그 거래처에 발주한 적이 있는 상품들의 한 달 판매 수량과 매출을 보여 준다. 처음에 IN 서브쿼리로 짰던 SQL을 이번 달 개편 때 조민우가 조인으로 바꿨다. "발주 쪽 컬럼(최근 발주일)을 나중에 화면에 붙이려고" 바꿨다고 한다. 개편 뒤 첫 보고에서 재무팀이 매출이 판매 원장과 맞지 않는다며 반려했다. 상품마다 어긋난 정도가 37배에서 61배까지 제각각이라, 처음에는 단가 오류를 의심했다.

테이블과 인덱스

CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · SLE_NO가 'YYYYMMDD-NNNN' 모양이라 한 달 범위는 CF11_PK로 읽는다(2023년 6월 46,384줄 · 리프 181블록 · 테이블 347블록)
CF12 발주: 734행 · 10블록 · CF12_PK (ORD_NO) · 거래처 :ptnr의 발주 61건
CF13 발주상품: 7,102행 · 57블록 · CF13_PK (ORD_NO, ITM_NO) · 거래처 :ptnr의 발주 61건에 상품 12종이 590줄(상품마다 37 ~ 61줄)
2023년 6월 그 12종의 판매 6,325줄 · 통계 최신

원본 SQL

sql

SELECT b.ITM_NO,
       SUM(b.SLE_QTY)              AS qty,
       SUM(b.SLE_QTY * b.SLE_UPRC) AS amt
  FROM CF11 b, CF13 d, CF12 h
 WHERE b.SLE_NO >= :v_from          -- '20230601'
   AND b.SLE_NO <  :v_to            -- '20230701'
   AND d.ITM_NO  = b.ITM_NO
   AND h.ORD_NO  = d.ORD_NO
   AND h.PTNR_NO = :ptnr
 GROUP BY b.ITM_NO
 ORDER BY amt DESC

실행계획 학습용 예시

xplan

---------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |         |      1 |        |     12 |00:00:00.24 |     595 |
|   1 |  SORT ORDER BY                         |         |      1 |     12 |     12 |00:00:00.24 |     595 |
|   2 |   HASH GROUP BY                        |         |      1 |     12 |     12 |00:00:00.24 |     595 |
|*  3 |    HASH JOIN                           |         |      1 |    311K|    311K|00:00:00.13 |     595 |
|*  4 |     HASH JOIN                          |         |      1 |    590 |    590 |00:00:00.01 |      67 |
|*  5 |      TABLE ACCESS FULL                 | CF12    |      1 |     61 |     61 |00:00:00.01 |      10 |
|   6 |      TABLE ACCESS FULL                 | CF13    |      1 |   7102 |   7102 |00:00:00.01 |      57 |
|   7 |     TABLE ACCESS BY INDEX ROWID BATCHED| CF11    |      1 |  46384 |  46384 |00:00:00.02 |     528 |
|*  8 |      INDEX RANGE SCAN                  | CF11_PK |      1 |  46384 |  46384 |00:00:00.01 |     181 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("D"."ITM_NO"="B"."ITM_NO")
   4 - access("H"."ORD_NO"="D"."ORD_NO")
   5 - filter("H"."PTNR_NO"=:PTNR)
   8 - access("B"."SLE_NO">=:V_FROM AND "B"."SLE_NO"<:V_TO)

문제

매출이 상품마다 다른 배수로 부푼 이유를 실행계획의 줄 번호와 A-Rows로 설명하시오. 처음에 IN으로 짠 SQL에서는 왜 이런 일이 없었는지도 설명하시오.
(가) 조인한 뒤 판매 줄에 DISTINCT를 거는 안, (나) 조인한 뒤 SUM(DISTINCT …)로 더하는 안, (다) 발주 쪽을 상품당 한 줄로 먼저 만든 뒤 조인하는 안, (라) 원래의 IN 서브쿼리로 돌아가는 안의 결과와 일량을 비교하시오.
최근 발주일을 화면에 함께 보일 수 있도록 SQL을 고치고, 고친 SQL이 맞다는 것을 무엇과 어떻게 대조해 보일지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 발주 쪽을 상품당 한 줄로 먼저 만든 뒤 붙인다 (최근 발주일을 붙여도 판매 줄이 곱해지지 않는다)
SELECT b.ITM_NO,
       SUM(b.SLE_QTY)              AS qty,
       SUM(b.SLE_QTY * b.SLE_UPRC) AS amt,
       MAX(s.last_ord_dt)          AS last_ord_dt
  FROM CF11 b,
       (SELECT d.ITM_NO, MAX(h.ORD_DT) AS last_ord_dt
          FROM CF12 h, CF13 d
         WHERE h.ORD_NO  = d.ORD_NO
           AND h.PTNR_NO = :ptnr
         GROUP BY d.ITM_NO) s
 WHERE b.SLE_NO >= :v_from
   AND b.SLE_NO <  :v_to
   AND s.ITM_NO = b.ITM_NO
 GROUP BY b.ITM_NO
 ORDER BY amt DESC;

-- 대조 ① 원래 IN 서브쿼리 결과와 상품별 MINUS 양방향 (0 행 / 0 행) · 상품 수 12 = 12
-- 대조 ② 수량 · 매출 합계 = 판매 원장 (CF11 만 6월 · 그 12종으로 더한 값)

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

xplan

----------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |         |      1 |        |     12 |00:00:00.03 |     595 |
|   1 |  SORT ORDER BY                          |         |      1 |     12 |     12 |00:00:00.03 |     595 |
|   2 |   HASH GROUP BY                         |         |      1 |     12 |     12 |00:00:00.03 |     595 |
|*  3 |    HASH JOIN                            |         |      1 |   6325 |   6325 |00:00:00.02 |     595 |
|   4 |     VIEW                                |         |      1 |     12 |     12 |00:00:00.01 |      67 |
|   5 |      HASH GROUP BY                      |         |      1 |     12 |     12 |00:00:00.01 |      67 |
|*  6 |       HASH JOIN                         |         |      1 |    590 |    590 |00:00:00.01 |      67 |
|*  7 |        TABLE ACCESS FULL                | CF12    |      1 |     61 |     61 |00:00:00.01 |      10 |
|   8 |        TABLE ACCESS FULL                | CF13    |      1 |   7102 |   7102 |00:00:00.01 |      57 |
|   9 |     TABLE ACCESS BY INDEX ROWID BATCHED | CF11    |      1 |  46384 |  46384 |00:00:00.01 |     528 |
|* 10 |      INDEX RANGE SCAN                   | CF11_PK |      1 |  46384 |  46384 |00:00:00.01 |     181 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("S"."ITM_NO"="B"."ITM_NO")
   6 - access("H"."ORD_NO"="D"."ORD_NO")
   7 - filter("H"."PTNR_NO"=:PTNR)
  10 - access("B"."SLE_NO">=:V_FROM AND "B"."SLE_NO"<:V_TO)

모범 답안 서술

3번 줄의 해시 조인에서 행이 불어난다. 7번 줄의 6월 판매 46,384줄 가운데 이 거래처 상품 12종의 판매 6,325줄이 4번 줄의 발주 590줄과 상품번호로 짝지어지는데, 상품 하나에 발주 줄이 37 ~ 61줄 매달려 있어 판매 한 줄이 그 수만큼 복제된다(A-Rows 311K). 그래서 매출이 상품마다 자기 발주 줄 수만큼, 곧 37배에서 61배까지 서로 다른 배수로 부풀었다. 배수가 상품마다 달라 순위까지 바뀌었고, 일정한 배수가 아니라서 단가 오류처럼 보였다. 처음의 IN 서브쿼리는 '이 상품이 거래처의 발주 목록에 있는가'만 물었다. 옵티마이저는 이것을 세미 조인으로 풀거나, 발주 쪽을 상품번호로 먼저 하나로 모은 뒤(HASH UNIQUE) 붙인다. 어느 쪽이든 판매 한 줄은 한 번만 남는다. IN을 조인으로 바꾸면서 이 '한 번만'이 사라졌다. 네 안을 비교하면 다음과 같다. Buffers는 모두 595로 같아서 블록으로는 가려지지 않는다.

안	결과	조인이 만드는 행	A-Time
지금 (그냥 조인)	틀림 (37 ~ 61배)	311K	0.24초
(가) 조인 뒤 판매 줄 DISTINCT	키를 넣으면 맞음	311K → 6,325	0.31초
(나) 조인 뒤 `SUM(DISTINCT …)`	틀림	311K	0.25초
(다) 발주 쪽을 먼저 상품당 한 줄로	맞음	6,325	0.03초
(라) 원래의 IN 서브쿼리	맞음	6,325 (세미 조인)	0.03초

(가)는 판매 줄의 키 (SLE_NO, ITM_NO)까지 넣어 DISTINCT를 걸면 답은 맞지만, 31만 줄을 만든 뒤 다시 지우는 일이 더 든다. 키를 빼고 (상품, 수량, 단가)로 DISTINCT를 걸면 서로 다른 판매 줄이 하나로 합쳐져 틀린다. (나)도 같은 이유로 틀린다. 같은 수량과 단가로 팔린 다른 영수증의 줄이 한 번만 더해진다. (다)와 (라)는 조인 전에 발주 쪽이 상품당 한 줄이 되므로 6,325줄만 만든다. 최근 발주일을 붙여야 하므로 (다)로 고친다. 발주 쪽을 상품별로 묶으면서 MAX(h.ORD_DT)로 최근 발주일을 함께 구하고, 그 12줄을 판매에 붙인다. 맞다는 것은 두 가지로 대조한다. 원래 IN 서브쿼리의 결과와 상품별로 MINUS를 양방향으로 해서 0 / 0이 나오는지 보고, 수량과 매출 합계가 판매 원장(CF11만 6월 그 12종으로 더한 값)과 같은지 본다. MINUS는 중복을 보지 못하므로 상품 수(12 = 12)도 함께 본다.

해설

"나중에 컬럼을 붙이려고": 서브쿼리 쪽 컬럼이 필요하면 조인으로 바꾸는 것이 맞다. 다만 그쪽이 '많은 쪽'이면 먼저 한 줄로 묶어야 한다. 묶지 않고 붙이면 SUBQ-004의 M:1:M과 같은 곱하기가 된다.
같은 블록, 다른 시간: 넷 다 595블록이라 AWR의 논리 읽기로는 구별되지 않는다. 31만 줄을 만드는 안은 A-Time과 CPU에서 드러난다. 정답 여부는 블록이 아니라 원장 대조로 가린다(교재 08화 §2).
발주가 한 번뿐인 상품: 발주 줄이 1줄인 상품은 조인해도 결과가 맞다. 그래서 거래처를 바꿔 가며 몇 번 시험하면 맞는 경우가 섞여 나와 통과하기 쉽다. 시험은 발주 줄이 여러 줄인 상품이 있는 거래처로 한다.
(라)를 고르지 않은 이유: 최근 발주일이 필요 없다면 원래의 IN이 가장 간단하고 옵티마이저가 알아서 세미 조인으로 푼다. 컬럼이 필요해진 순간에 (다)로 옮긴다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황