「상품 코드 오류 점검」이 마스터에 멀쩡히 있는 매장 제조 음료를 날마다 700줄 넘게 잡는다

중급SELECT유통279 / 700

상황

본사 전산실의 「상품 코드 오류 점검」은 매일 아침 어제 판매상품 가운데 상품 마스터(CF01)에 없는 상품번호로 찍힌 줄을 찾는다. POS 메뉴 동기화가 늦은 날 마스터에 없는 번호가 찍히는 일이 있어서 만든 점검이다. 지난주 점검표 양식에서 상품명 칸을 빼면서 SQL도 정리했는데, 그 뒤로 날마다 700줄 넘게 '코드 오류'로 나온다. 확인해 보니 아메리카노와 카페라테처럼 마스터에 멀쩡히 있는 매장 제조 음료의 줄이었다. 예전 점검표는 거래처 이름을 함께 보여 줘서 조인에 PTNR_NO를 쓰고 있었고, 정리하면서 IS NULL 조건도 그 컬럼에 걸었다.

테이블과 인덱스

CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO): BLEVEL 2 · SLE_NO는 'YYYYMMDD-NNNN'이라 앞 8자리가 판매일
CF01 상품: 109행 · 5블록 · CF01_PK (ITM_NO) · 매장 제조 상품 41개는 거래처가 없어 PTNR_NO가 NULL이다
2023-12-18 판매상품 1,512줄 가운데 매장 제조 상품 줄은 712줄이다 · 마스터에 없는 상품번호는 그날 0줄
통계 최신 · 바인드 :v_from = '20231218', :v_to = '20231219'

원본 SQL

sql

SELECT d.SLE_NO, d.ITM_NO, d.SLE_QTY
  FROM CF11 d
  LEFT OUTER JOIN CF01 p
    ON p.ITM_NO = d.ITM_NO
 WHERE d.SLE_NO >= :v_from
   AND d.SLE_NO <  :v_to
   AND p.PTNR_NO IS NULL
 ORDER BY d.SLE_NO, d.ITM_NO

실행계획 학습용 예시

xplan

---------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |         |      1 |        |    712 |00:00:00.01 |      26 |
|   1 |  SORT ORDER BY                         |         |      1 |    560 |    712 |00:00:00.01 |      26 |
|*  2 |   FILTER                               |         |      1 |        |    712 |00:00:00.01 |      26 |
|*  3 |    HASH JOIN RIGHT OUTER               |         |      1 |   1490 |   1512 |00:00:00.01 |      26 |
|   4 |     TABLE ACCESS FULL                  | CF01    |      1 |    109 |    109 |00:00:00.01 |       5 |
|   5 |     TABLE ACCESS BY INDEX ROWID BATCHED| CF11    |      1 |   1490 |   1512 |00:00:00.01 |      21 |
|*  6 |      INDEX RANGE SCAN                  | CF11_PK |      1 |   1490 |   1512 |00:00:00.01 |       8 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("P"."PTNR_NO" IS NULL)
   3 - access("P"."ITM_NO"="D"."ITM_NO")
   6 - access("D"."SLE_NO">=:V_FROM AND "D"."SLE_NO"<:V_TO)

문제

마스터에 멀쩡히 있는 매장 제조 음료가 '코드 오류'로 나온 까닭을 외부 조인 결과의 NULL로 설명하시오. 실행계획 2번과 3번 줄도 함께 설명하시오.
마스터에 없는 상품번호만 고르도록 SQL을 고치고, 고친 SQL의 계획이 어떻게 바뀔지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 짝이 없는지는 짝을 찾으면 반드시 값이 있는 컬럼(PK)으로 본다
SELECT d.SLE_NO, d.ITM_NO, d.SLE_QTY
  FROM CF11 d
  LEFT OUTER JOIN CF01 p
    ON p.ITM_NO = d.ITM_NO
 WHERE d.SLE_NO >= :v_from
   AND d.SLE_NO <  :v_to
   AND p.ITM_NO IS NULL
 ORDER BY d.SLE_NO, d.ITM_NO;
-- 같은 뜻 : WHERE … AND NOT EXISTS (SELECT 1 FROM CF01 p WHERE p.ITM_NO = d.ITM_NO)

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

xplan

---------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |         |      1 |        |      0 |00:00:00.01 |      22 |
|   1 |  SORT ORDER BY                         |         |      1 |      1 |      0 |00:00:00.01 |      22 |
|*  2 |   HASH JOIN RIGHT ANTI                 |         |      1 |      1 |      0 |00:00:00.01 |      22 |
|   3 |    INDEX FULL SCAN                     | CF01_PK |      1 |    109 |    109 |00:00:00.01 |       1 |
|   4 |    TABLE ACCESS BY INDEX ROWID BATCHED | CF11    |      1 |   1490 |   1512 |00:00:00.01 |      21 |
|*  5 |     INDEX RANGE SCAN                   | CF11_PK |      1 |   1490 |   1512 |00:00:00.01 |       8 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("P"."ITM_NO"="D"."ITM_NO")
   5 - access("D"."SLE_NO">=:V_FROM AND "D"."SLE_NO"<:V_TO)

모범 답안 서술

외부 조인 결과에서 p.PTNR_NO가 NULL인 경우는 두 가지다. 마스터에 짝이 없어 CF01 쪽 컬럼이 모두 NULL로 채워진 행, 그리고 짝은 찾았지만 그 상품의 거래처 번호가 원래 비어 있는 행이다. 매장 제조 상품 41개는 거래처가 없어 PTNR_NO가 NULL이므로, 마스터와 짝을 찾았는데도 두 번째 경우로 걸렸다. 그래서 그날 판매상품 1,512줄 가운데 매장 제조 상품 712줄이 모두 '코드 오류'가 됐다. 실행계획 3번 줄 HASH JOIN RIGHT OUTER는 상품 109개를 메모리에 올리고 판매상품 1,512줄을 모두 대어 본다. 짝이 없는 줄은 0줄이라 1,512줄이 그대로 나오고, 2번 줄 FILTER가 "P"."PTNR_NO" IS NULL로 712줄을 남긴다. IS NULL이 걸린 컬럼이 NULL 허용이라 옵티마이저는 이 조건을 '짝이 없는 행'으로 읽을 수 없고, 외부 조인을 그대로 둔 채 조인이 끝난 뒤에 걸렀다. 짝이 없는지를 보려면 IS NULL을 조인 키나 NOT NULL 컬럼(여기서는 PK인 p.ITM_NO)에 건다. 짝을 찾은 행에서 PK는 절대 NULL이 아니므로 p.ITM_NO IS NULL은 NULL로 채운 행만 고른다. 이렇게 쓰면 옵티마이저도 '짝이 없는 행만'이라는 뜻을 알아보고 외부 조인을 안티 조인(HASH JOIN RIGHT ANTI)으로 바꾼다. 상품 쪽은 PK 인덱스만 읽으면 된다. 결과는 712 → 0줄이고, Buffers는 26 → 22다. NOT EXISTS로 써도 같은 계획이다.

해설

IS NULL 하나에 두 뜻: 외부 조인 쪽 컬럼의 NULL은 '짝이 없다'와 '짝의 값이 비어 있다'를 구분하지 못한다. 짝이 없는지는 PK처럼 짝이 있으면 반드시 값이 있는 컬럼으로 본다(SUBQ-042의 (C)와 같은 함정).
'지금은 맞다'도 위험하다: 지금 NULL이 없는 컬럼(상품명 같은 것)에 IS NULL을 걸면 답은 맞는다. 하지만 그 컬럼에 NOT NULL 제약이 없으면 옵티마이저는 안티 조인으로 바꾸지 못해 조인을 끝까지 만들고, 언젠가 그 컬럼에 NULL이 들어오면 이 문제처럼 틀린다.
같은 NULL, 다른 함정: 매장 제조 상품의 PTNR_NO NULL은 SUBQ-033에서는 NOT IN의 바깥 쪽 NULL로 상품을 빠뜨렸고, 여기서는 외부 조인의 IS NULL로 멀쩡한 줄을 오류로 만들었다. 유통 DB에서 PTNR_NO를 조건에 쓰는 SQL은 NULL 41개를 먼저 떠올려야 한다.
대조: 고친 점검은 0줄이다. 마스터에 없는 번호(예: 999)로 판매상품 한 줄을 넣은 복사본에서 그 줄 하나만 나오는지도 확인해 둔다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황