JOIN-041

전월 대비 품목 판매표가 10초 걸리고 수량은 수백 배로 부풀고 시즌 메뉴는 빠진다

중급SELECT유통191 / 700

상황

상품팀은 매달 초 「전월 대비 품목 판매」로 품목마다 지난달과 이번 달 판매 수량을 나란히 놓고 증감을 본다. 한 달에만 팔린 시즌 메뉴도 나와야 해서 개발자가 FULL OUTER JOIN으로 썼다. 첫 실행이 10초 가까이 걸렸고, 쉐이크 수량이 실제의 500배 가까이 나왔으며, 11월에만 판 가을 한정 메뉴 2개와 12월에 새로 낸 윈터 시즌 메뉴 4개가 표에 없다. 아래는 2023년 11월과 12월을 비교한 실행계획이다.

테이블과 인덱스

CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · SLE_NO는 'YYYYMMDD-NNNN'(앞 8자리가 판매일자)
2023년 11월 44,105줄(86개 품목) · 12월 46,322줄(88개 품목) · 두 달 모두 팔린 품목 84개
품목 하나가 한 달에 평균 500줄 안팎 팔린다 · 통계 최신

원본 SQL

sql

SELECT NVL(a.ITM_NO, b.ITM_NO) AS ITM_NO,
       SUM(a.SLE_QTY) AS nov_qty,
       SUM(b.SLE_QTY) AS dec_qty
  FROM CF11 a
  FULL OUTER JOIN CF11 b
    ON b.ITM_NO = a.ITM_NO
 WHERE a.SLE_NO >= '20231101' AND a.SLE_NO < '20231201'
   AND b.SLE_NO >= '20231201' AND b.SLE_NO < '20240101'
 GROUP BY NVL(a.ITM_NO, b.ITM_NO)
 ORDER BY 1

실행계획 학습용 예시

xplan

------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |      1 |        |     84 |00:00:09.84 |    1754 |          |
|   1 |  SORT GROUP BY                |         |      1 |     88 |     84 |00:00:09.84 |    1754 | 9216  (0)|
|*  2 |   HASH JOIN                   |         |      1 |     23M|     24M|00:00:04.37 |    1754 | 3612K (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID| CF11    |      1 |  43870 |  44105 |00:00:00.02 |     860 |          |
|*  4 |     INDEX RANGE SCAN          | CF11_PK |      1 |  43870 |  44105 |00:00:00.01 |     174 |          |
|   5 |    TABLE ACCESS BY INDEX ROWID| CF11    |      1 |  45980 |  46322 |00:00:00.02 |     894 |          |
|*  6 |     INDEX RANGE SCAN          | CF11_PK |      1 |  45980 |  46322 |00:00:00.01 |     181 |          |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B"."ITM_NO"="A"."ITM_NO")
   4 - access("A"."SLE_NO">='20231101' AND "A"."SLE_NO"<'20231201')
   6 - access("B"."SLE_NO">='20231201' AND "B"."SLE_NO"<'20240101')

문제

계획 2번 줄이 FULL OUTER가 아닌 HASH JOIN이 된 이유와, 그 줄의 A-Rows 24M이 어디서 나왔는지 설명하시오.
시즌 메뉴 6개를 포함한 90개 품목이 실제 수량으로 나오도록 SQL을 고치시오. 달 조건을 ON 절로 옮기는 고침은 왜 틀린지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 달마다 먼저 거르고 품목별로 묶은 뒤(86 행, 88 행) 맞댄다. 품목 번호는 양쪽 키를 합쳐 낸다
SELECT COALESCE(a.ITM_NO, b.ITM_NO)     AS ITM_NO,
       NVL(a.qty, 0)                    AS nov_qty,
       NVL(b.qty, 0)                    AS dec_qty,
       NVL(b.qty, 0) - NVL(a.qty, 0)    AS diff_qty
  FROM (SELECT ITM_NO, SUM(SLE_QTY) AS qty
          FROM CF11
         WHERE SLE_NO >= '20231101' AND SLE_NO < '20231201'
         GROUP BY ITM_NO) a
  FULL OUTER JOIN
       (SELECT ITM_NO, SUM(SLE_QTY) AS qty
          FROM CF11
         WHERE SLE_NO >= '20231201' AND SLE_NO < '20240101'
         GROUP BY ITM_NO) b
    ON b.ITM_NO = a.ITM_NO
 ORDER BY 1

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

xplan

-------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |          |      1 |        |     90 |00:00:00.05 |    1754 |
|   1 |  SORT ORDER BY                     |          |      1 |     90 |     90 |00:00:00.05 |    1754 |
|   2 |   VIEW                             | VW_FOJ_0 |      1 |     90 |     90 |00:00:00.05 |    1754 |
|*  3 |    HASH JOIN FULL OUTER            |          |      1 |     90 |     90 |00:00:00.05 |    1754 |
|   4 |     VIEW                           |          |      1 |     86 |     86 |00:00:00.02 |     860 |
|   5 |      HASH GROUP BY                 |          |      1 |     86 |     86 |00:00:00.02 |     860 |
|   6 |       TABLE ACCESS BY INDEX ROWID  | CF11     |      1 |  43870 |  44105 |00:00:00.01 |     860 |
|*  7 |        INDEX RANGE SCAN            | CF11_PK  |      1 |  43870 |  44105 |00:00:00.01 |     174 |
|   8 |     VIEW                           |          |      1 |     88 |     88 |00:00:00.02 |     894 |
|   9 |      HASH GROUP BY                 |          |      1 |     88 |     88 |00:00:00.02 |     894 |
|  10 |       TABLE ACCESS BY INDEX ROWID  | CF11     |      1 |  45980 |  46322 |00:00:00.01 |     894 |
|* 11 |        INDEX RANGE SCAN            | CF11_PK  |      1 |  45980 |  46322 |00:00:00.01 |     181 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("B"."ITM_NO"="A"."ITM_NO")
   7 - access("SLE_NO">='20231101' AND "SLE_NO"<'20231201')
  11 - access("SLE_NO">='20231201' AND "SLE_NO"<'20240101')

모범 답안 서술

두 달 조건이 모두 WHERE에 있다. FULL OUTER JOIN은 짝이 없는 행의 반대쪽 컬럼을 NULL로 채우는데, WHERE는 조인 뒤에 걸리므로 a.SLE_NO >= '20231101'은 a가 NULL인 행(12월에만 팔린 품목)을, b.SLE_NO >= '20231201'은 b가 NULL인 행(11월에만 팔린 품목)을 버린다. 양쪽의 NULL 행이 모두 버려지면 결과는 이너 조인과 같으므로 옵티마이저가 2번 줄을 HASH JOIN으로 바꿨다. 그래서 두 달 모두 팔린 84개 품목만 남고 시즌 메뉴 6개(11월 2개, 12월 4개)가 빠졌다. 더 큰 문제는 묶기 전의 판매상품 줄끼리 맞댄 것이다. 품목 하나가 한 달에 500줄 안팎이므로 품목마다 11월 줄 × 12월 줄의 조합이 생긴다(M:M). 2번 줄 A-Rows가 24M이 된 까닭이고, SUM(a.SLE_QTY)는 11월 수량을 그 품목의 12월 줄 수만큼 곱해 더한 값이 됐다. 9.84초의 대부분이 이 2,400만 행을 만들고 묶는 데 들었다(Buffers는 1,754로 작다). 달마다 먼저 거르고 품목별로 묶은 뒤(86행, 88행) FULL OUTER JOIN한다. 그러면 계획은 HASH JOIN FULL OUTER(VW_FOJ_0)가 되고 90개 품목이 실제 수량으로 나온다. Buffers는 1,754 → 1,754로 같고 시간은 9.84초 → 0.05초다. 품목 번호는 COALESCE(a.ITM_NO, b.ITM_NO)로 낸다. 달 조건을 ON으로 옮기면(ON ... AND a.SLE_NO >= ...) 이번에는 아무것도 걸러지지 않는다. FULL OUTER JOIN의 ON은 짝을 지을지만 정하므로, 두 달이 아닌 판매상품 줄까지 양쪽에서 짝 없는 행으로 모두 남고 CF11을 두 번 통째로 읽는다. FULL OUTER JOIN에 대는 집합의 조건은 조인 전에 인라인 뷰 안에서 건다.

해설

다른 정답: 두 달을 한 범위(SLE_NO >= '20231101' AND SLE_NO < '20240101')로 한 번 읽고 SUM(CASE WHEN SLE_NO < '20231201' THEN SLE_QTY END)처럼 달마다 나눠 더해도 된다. 조인이 아예 없고 읽는 블록도 같다. 같은 테이블의 두 기간을 비교할 때는 이 모양이 가장 간단하다.
FULL OUTER JOIN에서 조건의 자리: 양쪽이 모두 보존되므로 WHERE에 한쪽 조건을 두면 그쪽이 NULL인 행이 버려지고, ON에 두면 아무것도 걸러지지 않는다. 각 집합의 조건은 조인 전에 인라인 뷰 안에서 건다.
M:M은 블록에 안 보인다: Buffers는 고친 뒤와 같은 1,754다. 시간과 2번 줄의 A-Rows(24M)를 봐야 보인다. 묶는 단위(품목)가 같은 두 집합은 묶은 뒤에 붙인다 (교재 08화 §1).
대사: 고친 결과의 11월 수량 합계와 12월 수량 합계가 각 달 판매상품의 수량 합계와 같은지, 품목 수가 86 + 88 - 84 = 90인지 확인한다 (교재 08화 §10).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황