거래처 한 곳을 거래 정지로 등록한 다음 날, POS 메뉴에서 매장 제조 음료가 모두 사라졌다

기초SELECT유통268 / 700

상황

본사 전산실은 매일 06시에 「판매 가능 상품」 목록을 다섯 매장의 POS 메뉴로 내려보낸다. 지난달 구매팀 요청으로 "거래 정지된 거래처의 상품은 팔지 않는다"는 조건을 SQL에 한 줄 더해 배포했고, 3주 동안 아무 일이 없었다. 어제 오후 구매팀이 처음으로 거래처 한 곳(7번)을 거래 정지로 등록했다. 오늘 아침 모든 매장 POS에서 아메리카노와 카페라테를 비롯한 매장 제조 음료가 메뉴에서 사라졌다. 빠져야 할 것은 거래처 7의 상품 5개뿐이었다.

테이블과 인덱스

CF01 상품: 109행 · 5블록 · CF01_PK (ITM_NO) · 판매 중(SLE_YN = 'Y') 88개 = 매장 제조 음료와 디저트 41개 + 거래처 상품 47개
매장 제조 상품 41개는 거래처가 없어 PTNR_NO가 NULL이다(나머지 상품은 모두 거래처 번호가 있다)
CF_PTNR_STOP 거래 정지 거래처: 이 기능을 위해 만든 표 · PTNR_NO NUMBER(PK라 NOT NULL) · STOP_DT · 어제까지 0행, 지금 1행(거래처 7) · CF_PTNR_STOP_PK 1블록
거래처 7의 판매 상품은 5개다 · 통계 최신

원본 SQL

sql

SELECT i.ITM_NO, i.ITM_NM, i.CTG_NO
  FROM CF01 i
 WHERE i.SLE_YN = 'Y'
   AND i.PTNR_NO NOT IN (SELECT s.PTNR_NO FROM CF_PTNR_STOP s)
 ORDER BY i.ITM_NO

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

xplan

----------------------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 |      1 |        |     42 |00:00:00.01 |       6 |
|   1 |  SORT ORDER BY       |                 |      1 |     87 |     42 |00:00:00.01 |       6 |
|*  2 |   HASH JOIN ANTI SNA |                 |      1 |     87 |     42 |00:00:00.01 |       6 |
|*  3 |    TABLE ACCESS FULL | CF01            |      1 |     88 |     88 |00:00:00.01 |       5 |
|   4 |    INDEX FULL SCAN   | CF_PTNR_STOP_PK |      1 |      1 |      1 |00:00:00.01 |       1 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("I"."PTNR_NO"="S"."PTNR_NO")
   3 - filter("I"."SLE_YN"='Y')

[어제까지 · CF_PTNR_STOP 0행]  같은 계획 · 결과 88개

문제

3주 동안 멀쩡하던 SQL이 거래처 한 곳을 등록한 다음 날부터 매장 제조 음료를 빠뜨린 까닭을 NOT IN의 뜻으로 설명하시오. 실행계획 2번 줄의 SNA가 무엇인지도 설명하시오.
거래처 7의 상품만 빠지도록 SQL을 고치시오.

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

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

모범답안

모범 답안 SQL

sql

-- '정지된 거래처의 상품이 아니다' = 그 상품의 거래처와 짝이 되는 정지 행이 없다
SELECT i.ITM_NO, i.ITM_NM, i.CTG_NO
  FROM CF01 i
 WHERE i.SLE_YN = 'Y'
   AND NOT EXISTS (SELECT 1 FROM CF_PTNR_STOP s WHERE s.PTNR_NO = i.PTNR_NO)
 ORDER BY i.ITM_NO;
-- NOT IN 을 남긴다면 : AND (i.PTNR_NO IS NULL OR i.PTNR_NO NOT IN (SELECT s.PTNR_NO FROM CF_PTNR_STOP s))

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

xplan

----------------------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 |      1 |        |     83 |00:00:00.01 |       6 |
|   1 |  SORT ORDER BY       |                 |      1 |     87 |     83 |00:00:00.01 |       6 |
|*  2 |   HASH JOIN ANTI     |                 |      1 |     87 |     83 |00:00:00.01 |       6 |
|*  3 |    TABLE ACCESS FULL | CF01            |      1 |     88 |     88 |00:00:00.01 |       5 |
|   4 |    INDEX FULL SCAN   | CF_PTNR_STOP_PK |      1 |      1 |      1 |00:00:00.01 |       1 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("S"."PTNR_NO"="I"."PTNR_NO")
   3 - filter("I"."SLE_YN"='Y')

모범 답안 서술

매장 제조 상품 41개는 PTNR_NO가 NULL이다. NULL NOT IN (7)은 NULL <> 7이고, NULL과의 비교는 참도 거짓도 아닌 UNKNOWN이므로 WHERE를 통과하지 못한다. 그래서 41개가 거래처 7의 상품 5개와 함께 빠지고 42개(88 − 41 − 5)만 남았다. 3주 동안 문제가 없었던 것은 CF_PTNR_STOP이 비어 있었기 때문이다. 서브쿼리 결과가 빈 집합이면 x NOT IN (빈 집합)은 x가 NULL이어도 참이라 모든 행이 통과한다. 첫 거래처가 등록된 순간 NULL 행이 한꺼번에 빠졌다. 테스트 환경에도 정지 거래처가 0건이라 드러나지 않았다. 2번 줄의 HASH JOIN ANTI SNA는 Single Null-Aware 안티 조인이다. 바깥 쪽 컬럼(i.PTNR_NO)만 NULL을 허용하고 서브쿼리 쪽(s.PTNR_NO, PK)에는 NULL이 없을 때 쓰는 모양으로, '서브쿼리가 비어 있지 않으면 바깥 값이 NULL인 행은 버린다'는 NOT IN의 뜻을 그대로 지켰다. 계획은 맞게 돌았고 질문이 틀렸다. 요구는 '정지된 거래처의 상품이 아니다'이므로 NOT EXISTS로 쓴다. NOT EXISTS는 짝이 있는지만 보므로 거래처가 없는(NULL) 상품은 짝이 없어 남는다. 결과는 42 → 83개가 되고, 매장 제조 상품 41개가 다시 돌아온다. Buffers는 6 그대로다. NOT IN을 남기려면 (i.PTNR_NO IS NULL OR i.PTNR_NO NOT IN (…))처럼 NULL인 경우를 따로 적어야 한다.

해설

'비어 있을 때 맞았다'는 증거가 아니다: NOT IN은 서브쿼리가 비어 있으면 NULL 행까지 모두 통과시키고, 한 행이라도 생기면 바깥 값이 NULL인 행을 모두 버린다. 경계 테스트에는 '서브쿼리 0행'과 '서브쿼리 1행 + 바깥 NULL'을 함께 넣는다.
SUBQ-003과 반대쪽 NULL: SUBQ-003은 서브쿼리 쪽에 NULL이 있어서 결과 전체가 0건이 됐다(ANTI NA). 이 문제는 바깥 쪽에 NULL이 있어서 그 행들만 빠졌다(ANTI SNA). NOT EXISTS는 두 경우 모두 '짝이 있는가'로만 판정한다.
다른 정답: NVL(i.PTNR_NO, -1) NOT IN (…)도 같은 83개를 낸다. 다만 -1이 거래처 번호로 쓰일 일이 없다는 약속에 기대므로, 뜻이 그대로 보이는 NOT EXISTS가 낫다.
배포 전 대조: 고친 SQL의 결과 83개 = 판매 중 88개 − 거래처 7의 상품 5개이고, 매장 제조 상품 41개가 모두 들어 있는지 본다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

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

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황