JOIN-005

월말 품목 마감표가 109줄이어야 하는데 88줄만 나온다

중급SELECT유통155 / 700

상황

월말 「품목 마감표」는 상품 마스터의 품목마다 한 줄씩 그달 판매 수량과 판매 줄 수를 낸다(판매가 없으면 0). 회계팀은 이 표를 마스터 행 수(109)와 대사하는데, 12월 마감표가 88줄이라 반려했다. 개발자는 "외부 조인 (+)를 붙였으니 다 나와야 한다. 마스터에 21개가 잘못 들어간 것 아닌가?"라고 한다.

테이블과 인덱스

CF01 상품: 109행 · 5블록 · CF01_PK (ITM_NO) · 판매상품 88 · 추가상품 1 (샷추가) · 원재료 20
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · SLE_NO는 'YYYYMMDD-NNNN' (앞 8자리가 판매일자) · 2023년 12월 46,322줄 · 그달 팔린 품목 88개
원재료 20개와 샷추가는 판매 상세에 한 번도 나오지 않는다 · FK 없음 · 통계 최신
바인드 :v_from = '20231201' · :v_to = '20240101'

원본 SQL

sql

SELECT p.ITM_NO, p.ITM_NM,
       NVL(SUM(i.SLE_QTY), 0) AS sle_qty,
       COUNT(i.SLE_NO)        AS sle_lines
  FROM CF01 p, CF11 i
 WHERE i.ITM_NO(+) = p.ITM_NO
   AND i.SLE_NO >= :v_from
   AND i.SLE_NO <  :v_to
 GROUP BY p.ITM_NO, p.ITM_NM
 ORDER BY p.ITM_NO

실행계획 학습용 예시 · 행 수는 실데이터

xplan

--------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |      1 |        |     88 |00:00:00.04 |     899 |
|   1 |  SORT GROUP BY                |         |      1 |    109 |     88 |00:00:00.04 |     899 |
|*  2 |   HASH JOIN                   |         |      1 |  45980 |  46322 |00:00:00.03 |     899 |
|   3 |    TABLE ACCESS FULL          | CF01    |      1 |    109 |    109 |00:00:00.01 |       5 |
|   4 |    TABLE ACCESS BY INDEX ROWID| CF11    |      1 |  45980 |  46322 |00:00:00.02 |     894 |
|*  5 |     INDEX RANGE SCAN          | CF11_PK |      1 |  45980 |  46322 |00:00:00.01 |     181 |
--------------------------------------------------------------------------------------------------

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

문제

계획 2번 줄에서 무엇이 빠졌는지 찾고, (+)를 붙였는데도 21개 품목이 사라진 이유를 설명하시오.
109줄이 나오도록 SQL을 고치시오((+) 표기와 ANSI 표기 중 하나 이상). 고친 뒤 성능이 어떻게 되는지, 흔히 쓰는 다른 고침(OR … IS NULL)이 왜 틀렸는지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- (+) 표기 — inner 쪽(CF11) 조건 전부에 (+) : 조인 '전에' 거르는 조건이 된다
SELECT p.ITM_NO, p.ITM_NM,
       NVL(SUM(i.SLE_QTY), 0) AS sle_qty,
       COUNT(i.SLE_NO)        AS sle_lines
  FROM CF01 p, CF11 i
 WHERE i.ITM_NO(+) = p.ITM_NO
   AND i.SLE_NO(+) >= :v_from
   AND i.SLE_NO(+) <  :v_to
 GROUP BY p.ITM_NO, p.ITM_NM
 ORDER BY p.ITM_NO;

-- ANSI — inner 쪽 조건은 ON 에 (WHERE 에 두면 같은 실수다). 보존할 쪽 CF01 의 조건만 WHERE 에
SELECT p.ITM_NO, p.ITM_NM,
       NVL(SUM(i.SLE_QTY), 0) AS sle_qty,
       COUNT(i.SLE_NO)        AS sle_lines
  FROM CF01 p
  LEFT JOIN CF11 i
    ON i.ITM_NO = p.ITM_NO
   AND i.SLE_NO >= :v_from
   AND i.SLE_NO <  :v_to
 GROUP BY p.ITM_NO, p.ITM_NM
 ORDER BY p.ITM_NO;

개선 후 실행계획 학습용 예시 · 행 수는 실데이터

xplan

--------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |      1 |        |    109 |00:00:00.04 |     899 |
|   1 |  SORT GROUP BY                |         |      1 |    109 |    109 |00:00:00.04 |     899 |
|*  2 |   HASH JOIN OUTER             |         |      1 |  46001 |  46343 |00:00:00.03 |     899 |
|   3 |    TABLE ACCESS FULL          | CF01    |      1 |    109 |    109 |00:00:00.01 |       5 |
|   4 |    TABLE ACCESS BY INDEX ROWID| CF11    |      1 |  45980 |  46322 |00:00:00.02 |     894 |
|*  5 |     INDEX RANGE SCAN          | CF11_PK |      1 |  45980 |  46322 |00:00:00.01 |     181 |
--------------------------------------------------------------------------------------------------

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

모범 답안 서술

계획 2번 줄은 HASH JOIN이고, OUTER가 빠져 있다. (+)를 조인 조건에만 붙이고 CF11의 기간 조건(i.SLE_NO >= :v_from)에는 붙이지 않았기 때문이다. 외부 조인은 짝이 없는 품목에 CF11 쪽 컬럼을 NULL로 채운 행을 만든다. 그런데 WHERE의 i.SLE_NO >= :v_from은 NULL에 대해 참이 아니므로 그 행을 전부 버린다. 결과가 내부 조인과 같아지므로 옵티마이저도 외부 조인을 내부 조인으로 바꿨다. 그래서 12월에 안 팔린 21개 품목(원재료 20개, 샷추가 1개)이 사라져 109줄이 88줄이 됐다. 마스터가 잘못된 것이 아니다. 회계팀이 보려던 '판매 0인 품목'이 바로 사라진 21줄이다. inner 쪽 테이블의 조건은 전부 조인 '전에' 걸려야 한다. (+) 표기라면 i.SLE_NO(+) >= :v_from AND i.SLE_NO(+) < :v_to로 쓰고, ANSI 표기라면 ON 절에 둔다. WHERE에는 보존할 쪽(CF01)의 조건만 남긴다. 고친 뒤에는 2번 줄이 HASH JOIN OUTER가 되고 109줄이 나온다 (21줄은 수량 0, 줄 수 0). 성능은 그대로다. inner 쪽 기간 조건은 ON으로 옮겨도 CF11_PK 범위 스캔의 access 조건으로 그대로 쓰인다 (Buffers 899 → 899). AND (i.SLE_NO >= :v_from AND i.SLE_NO < :v_to OR i.SLE_NO IS NULL)로 메우면 안 된다. CF01을 CF11 전체 110만 줄과 먼저 외부 조인한 뒤에 거르므로 CF11을 FULL로 8,204블록 읽게 되고, 다른 달에는 팔렸지만 12월에만 안 팔린 품목은 여전히 사라진다 (그 품목은 짝이 있어서 NULL 행이 생기지 않는다). 지금 데이터에는 그런 품목이 없어서 109줄이 나오고 테스트도 통과한다. 그래서 더 위험하다.

해설

COUNT(*)의 함정: 외부 조인 결과를 COUNT(*)로 세면 안 팔린 품목도 1로 나온다 (NULL로 채운 행도 한 행이다). 건수는 inner 쪽의 NOT NULL 컬럼으로 센다(COUNT(i.SLE_NO)).
다른 정답: CF11을 그달 품목별로 먼저 묶은 인라인 뷰(88행)에 CF01을 외부 조인해도 답은 같다. 조인에 대는 행이 46,322 → 88로 줄어 CPU가 준다 (Buffers는 같다. 교재 18화 Q2 rev02).
(+)는 OR이나 IN과 함께 쓸 수 없고, 테이블이 셋 넘게 얽히면 어느 쪽이 보존되는지 읽기 어렵다. 새로 쓰는 SQL은 ANSI 표기로 쓰고 inner 쪽 조건은 ON에 둔다는 규칙을 코딩 가이드에 한 줄로 넣어 둔다.
정합성 회귀: 결과 109행이 마스터 109행과 같은지, 판매 수량 합계가 12월 원장 115,694와 같은지, 0인 품목 21개가 '원재료 20 + 샷추가'와 같은지 확인한다 (교재 08화 §10 체크리스트).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 행 수는 실데이터 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 행 수는 실데이터 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황