「발주 누락 점검」에 6월에 발주한 상품까지 109개가 다 나온다

중급SELECT유통273 / 700

상황

구매팀의 「발주 누락 점검」은 달마다 상품 109개 가운데 그달 발주가 한 번도 없는 상품을 뽑는다. 발주 담당이 빠뜨린 품목을 찾으려는 화면이다. 2023년 6월 점검표에 109개가 전부 나왔고, 그중에는 6월 12일에 발주한 원두와 우유도 있었다. 조민우는 "외부 조인으로 붙여서 짝이 없는 것만 IS NULL로 골랐다. 발주 헤더에만 6월 조건을 걸었으니 맞을 텐데"라고 한다.

테이블과 인덱스

CF01 상품: 109행 · 5블록 · CF01_PK (ITM_NO) · 거래처 상품 68개 + 매장 제조 상품 41개(발주한 적이 없다)
CF12 발주: 734행 · 10블록 · CF12_PK (ORD_NO) · ORD_DT VARCHAR2(8) · 2023년 6월 28건
CF13 발주상품: 7,102행 · 57블록 · CF13_PK (ORD_NO, ITM_NO) · 2023년 6월 271줄(상품 68개) · 거래처 상품 68개는 다른 달에도 거의 매달 발주된다
통계 최신

원본 SQL

sql

SELECT DISTINCT p.ITM_NO, p.ITM_NM
  FROM CF01 p, CF13 d, CF12 h
 WHERE d.ITM_NO(+) = p.ITM_NO
   AND h.ORD_NO(+) = d.ORD_NO
   AND h.ORD_DT(+) BETWEEN '20230601' AND '20230630'
   AND h.ORD_NO IS NULL
 ORDER BY p.ITM_NO

실행계획 학습용 예시 · 건수는 적재 데이터 그대로

xplan

------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |      1 |        |    109 |00:00:00.01 |      72 |          |
|   1 |  SORT UNIQUE            |      |      1 |    109 |    109 |00:00:00.01 |      72 | 9216  (0)|
|*  2 |   FILTER                |      |      1 |        |   6872 |00:00:00.01 |      72 |          |
|*  3 |    HASH JOIN RIGHT OUTER|      |      1 |   7143 |   7143 |00:00:00.01 |      72 | 1269K (0)|
|*  4 |     TABLE ACCESS FULL   | CF12 |      1 |     28 |     28 |00:00:00.01 |      10 |          |
|*  5 |     HASH JOIN OUTER     |      |      1 |   7143 |   7143 |00:00:00.01 |      62 | 1180K (0)|
|   6 |      TABLE ACCESS FULL  | CF01 |      1 |    109 |    109 |00:00:00.01 |       5 |          |
|   7 |      TABLE ACCESS FULL  | CF13 |      1 |   7102 |   7102 |00:00:00.01 |      57 |          |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("H"."ORD_NO" IS NULL)
   3 - access("H"."ORD_NO"(+)="D"."ORD_NO")
   4 - filter(("H"."ORD_DT">='20230601' AND "H"."ORD_DT"<='20230630'))
   5 - access("D"."ITM_NO"(+)="P"."ITM_NO")

문제

6월에 발주한 상품까지 109개가 모두 나온 까닭을 실행계획 2 ~ 5번 줄의 A-Rows로 설명하시오. DISTINCT가 무엇을 가렸는지도 쓰시오.
상품마다 '6월 발주가 있는가'를 판정하도록 SQL을 고치시오.

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

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

모범답안

모범 답안 SQL

sql

-- 판정 단위를 상품으로 : 그 상품의 6월 발주상품 줄이 하나도 없다
SELECT p.ITM_NO, p.ITM_NM
  FROM CF01 p
 WHERE NOT EXISTS (SELECT 1
                     FROM CF13 d, CF12 h
                    WHERE d.ITM_NO = p.ITM_NO
                      AND h.ORD_NO = d.ORD_NO
                      AND h.ORD_DT BETWEEN '20230601' AND '20230630')
 ORDER BY p.ITM_NO;

개선 후 실행계획 학습용 예시 · 건수는 적재 데이터 그대로

xplan

-------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |         |      1 |        |     41 |00:00:00.01 |      72 |          |
|   1 |  SORT ORDER BY          |         |      1 |      1 |     41 |00:00:00.01 |      72 | 4096  (0)|
|*  2 |   HASH JOIN ANTI        |         |      1 |      1 |     41 |00:00:00.01 |      72 | 1214K (0)|
|   3 |    TABLE ACCESS FULL    | CF01    |      1 |    109 |    109 |00:00:00.01 |       5 |          |
|   4 |    VIEW                 | VW_SQ_1 |      1 |    271 |    271 |00:00:00.01 |      67 |          |
|*  5 |     HASH JOIN           |         |      1 |    271 |    271 |00:00:00.01 |      67 | 1237K (0)|
|*  6 |      TABLE ACCESS FULL  | CF12    |      1 |     28 |     28 |00:00:00.01 |      10 |          |
|   7 |      TABLE ACCESS FULL  | CF13    |      1 |   7102 |   7102 |00:00:00.01 |      57 |          |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ITEM_1"="P"."ITM_NO")
   5 - access("H"."ORD_NO"="D"."ORD_NO")
   6 - filter(("H"."ORD_DT">='20230601' AND "H"."ORD_DT"<='20230630'))

모범 답안 서술

5번 줄에서 상품 109개에 발주상품 7,102줄이 외부 조인으로 붙어 7,143행이 된다(발주상품 7,102줄과 짝이 없는 매장 제조 상품 41개). 3번 줄은 그 행마다 6월 발주 헤더를 붙이는데, 6월 줄 271개만 짝을 찾고 나머지 6,872행은 헤더 쪽이 NULL이다. 2번 줄의 h.ORD_NO IS NULL은 이 6,872행을 모두 통과시킨다. 문제는 IS NULL이 상품 단위가 아니라 발주상품 줄 단위로 판정된다는 점이다. 원두처럼 6월에 발주한 상품도 다른 달의 발주상품 줄이 있고, 그 줄들은 6월 헤더와 짝이 없어 NULL이 된다. 상품에 6월이 아닌 줄이 하나라도 있으면 통과하므로 거래처 상품 68개가 모두 남았다. 1번 줄의 DISTINCT가 6,872행을 상품 109개로 합쳐 버려서, 한 상품이 수백 줄로 불었다가 합쳐졌다는 흔적이 결과에 남지 않았다. 요구는 '상품마다 6월 발주가 한 건이라도 있는가'이므로, 발주상품과 발주 헤더의 조인을 NOT EXISTS 서브쿼리 안에 넣는다. 옵티마이저는 서브쿼리를 6월 발주상품 271줄짜리 뷰(VW_SQ_1)로 펼쳐 상품과 해시 안티 조인한다. 결과는 109 → 41개(발주한 적 없는 매장 제조 상품)이고, 6월에 발주한 68개와 합치면 109개로 맞는다. Buffers는 72 그대로다.

해설

외부 조인 사슬의 IS NULL: 상품, 발주상품, 발주를 외부 조인으로 이은 뒤 맨 끝 테이블에 IS NULL을 걸면 '그 발주상품 줄에 6월 헤더가 없다'는 뜻이 된다. '그 상품에 6월 발주가 하나도 없다'와는 다른 질문이다. 판정 단위(상품)와 조인 결과의 단위(발주상품 줄)가 다르면 안티 조인은 서브쿼리로 쓴다.
외부 조인으로 고치는 답: 6월 발주상품을 상품별로 먼저 묶은 인라인 뷰(SELECT DISTINCT d.ITM_NO FROM CF13 d, CF12 h WHERE … 6월)를 상품에 외부 조인하고 x.ITM_NO IS NULL을 걸어도 41개다. 판정 단위를 상품으로 맞춘 것이다.
DISTINCT를 붙이기 전에 중복의 까닭부터: 이 SQL의 DISTINCT는 7,143행으로 불어난 조인을 숨겼다. 교재 08화에서 COUNT(DISTINCT)가 50배 조인을 가린 것과 같은 모양이다.
같은 6월 데이터를 쓰는 SUBQ-014는 외부 조인 결과를 COUNT(*)로 센 함정이다. 외부 조인 결과를 세거나 거를 때는 NULL로 채운 행이 어떻게 계산되는지 늘 따진다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 건수는 적재 데이터 그대로 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 건수는 적재 데이터 그대로 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황