리뷰 요청 대상 「지난주 평가 없는 상품」이 4주째 0개이고, 실행은 0.01초도 안 걸린다

기초SELECT한빛유통267 / 700

상황

상품팀은 매주 월요일 09시에 「리뷰 요청 대상」을 뽑는다. 지난주(월요일 0시부터 이번 주 월요일 0시 전까지) 평가가 한 건도 들어오지 않은 상품을 고르고, 그 상품을 산 고객에게 리뷰를 부탁하는 알림을 보낸다. 지난달 조민우가 이 SQL을 ANSI 외부 조인으로 다시 쓴 뒤로 4주 내리 대상이 0개다. 조민우는 "평가가 고르게 들어와서 그런 것 같다. 실행도 0.01초가 안 걸린다"라고 하지만, 상품팀이 화면에서 세어 보면 평가가 없는 상품이 매주 20개 넘게 있다.

테이블과 인덱스

M_ITM 상품: 100행 · 5블록 · PK_M_ITM (ITM_ID)
T_ITM_EVL 상품평가: 5,000행 · 48블록 · PK_T_ITM_EVL (ITM_ID, EVL_LST_NO) · ITM_ID는 NOT NULL · EVL_DT DATE(2024년, 하루 13 ~ 14건)
2024-06-10 ~ 06-16 한 주의 평가는 98건이고, 평가를 받은 상품은 76개다
통계 최신 · 바인드는 DATE(:v_from = DATE '2024-06-10', :v_to = DATE '2024-06-17')

원본 SQL

sql

SELECT i.ITM_ID, i.ITM_NM
  FROM M_ITM i
  LEFT OUTER JOIN T_ITM_EVL e
    ON e.ITM_ID = i.ITM_ID
 WHERE e.EVL_DT >= :v_from
   AND e.EVL_DT <  :v_to
   AND e.ITM_ID IS NULL
 ORDER BY i.ITM_ID

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

xplan

----------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |      1 |        |      0 |00:00:00.01 |       0 |
|   1 |  SORT ORDER BY       |           |      1 |      1 |      0 |00:00:00.01 |       0 |
|*  2 |   FILTER             |           |      1 |        |      0 |00:00:00.01 |       0 |
|*  3 |    HASH JOIN         |           |      0 |      1 |      0 |00:00:00.01 |       0 |
|*  4 |     TABLE ACCESS FULL| T_ITM_EVL |      0 |     96 |      0 |00:00:00.01 |       0 |
|   5 |     TABLE ACCESS FULL| M_ITM     |      0 |    100 |      0 |00:00:00.01 |       0 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(NULL IS NOT NULL)
   3 - access("E"."ITM_ID"="I"."ITM_ID")
   4 - filter(("E"."EVL_DT">=:V_FROM AND "E"."EVL_DT"<:V_TO))

문제

결과가 0개인 까닭을 외부 조인과 WHERE 절의 관계로 설명하시오. 실행계획 2번 줄의 조건과 3 ~ 5번 줄의 Starts가 무엇을 뜻하는지도 설명하시오.
지난주 평가가 없는 상품이 나오도록 SQL을 고치시오.

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

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

모범답안

모범 답안 SQL

sql

-- '지난주 평가가 없다' = 그 상품을 가리키는 지난주 평가 행이 없다 (날짜 조건은 짝을 고르는 쪽에)
SELECT i.ITM_ID, i.ITM_NM
  FROM M_ITM i
 WHERE NOT EXISTS (SELECT 1
                     FROM T_ITM_EVL e
                    WHERE e.ITM_ID = i.ITM_ID
                      AND e.EVL_DT >= :v_from
                      AND e.EVL_DT <  :v_to)
 ORDER BY i.ITM_ID;

-- 외부 조인을 남긴다면 날짜 조건을 ON 안으로
-- SELECT i.ITM_ID, i.ITM_NM
--   FROM M_ITM i
--   LEFT OUTER JOIN T_ITM_EVL e
--     ON e.ITM_ID = i.ITM_ID AND e.EVL_DT >= :v_from AND e.EVL_DT < :v_to
--  WHERE e.ITM_ID IS NULL
--  ORDER BY i.ITM_ID;

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

xplan

------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |      1 |        |     24 |00:00:00.01 |      53 |          |
|   1 |  SORT ORDER BY        |           |      1 |      4 |     24 |00:00:00.01 |      53 | 2048  (0)|
|*  2 |   HASH JOIN RIGHT ANTI|           |      1 |      4 |     24 |00:00:00.01 |      53 | 1236K (0)|
|*  3 |    TABLE ACCESS FULL  | T_ITM_EVL |      1 |     96 |     98 |00:00:00.01 |      48 |          |
|   4 |    TABLE ACCESS FULL  | M_ITM     |      1 |    100 |    100 |00:00:00.01 |       5 |          |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("E"."ITM_ID"="I"."ITM_ID")
   3 - filter(("E"."EVL_DT">=:V_FROM AND "E"."EVL_DT"<:V_TO))

모범 답안 서술

WHERE 절의 e.EVL_DT >= :v_from AND e.EVL_DT < :v_to는 외부 조인이 끝난 뒤의 행을 거른다. 평가가 없는 상품의 행은 평가 쪽 컬럼이 모두 NULL로 채워져 있으므로 이 날짜 조건에서 NULL로 채운 행이 모두 걸러진다. NULL로 채운 행을 버리는 조건이 WHERE에 있으면 외부 조인은 내부 조인과 같아지고, 옵티마이저도 외부 조인을 내부 조인으로 바꾼다(OJE). 3번 줄이 OUTER 없이 그냥 HASH JOIN인 것이 그 흔적이다. 내부 조인 결과에서 e.ITM_ID는 PK 컬럼이라 NULL일 수 없으므로 e.ITM_ID IS NULL은 언제나 거짓이다. 옵티마이저는 이 모순을 알아채고 2번 줄에 filter(NULL IS NOT NULL)을 붙였다. 그래서 3 ~ 5번 줄의 Starts가 0이고 Buffers도 0이다. 테이블을 한 블록도 읽지 않고 0개를 돌려준 것이라 0.01초도 걸리지 않았다. 빠른 것이 아니라 질문이 틀린 것이다. 날짜 조건은 '어느 평가를 짝으로 볼 것인가'를 정하는 조건이므로 NOT EXISTS 서브쿼리 안(또는 외부 조인의 ON 절 안)에 둔다. NOT EXISTS로 쓰면 HASH JOIN RIGHT ANTI가 지난주 평가 98건을 메모리에 올리고 상품 100개를 대어 보며, 짝이 없는 상품 24개가 나온다(Buffers 0 → 53). ON 절에 날짜 조건을 넣고 WHERE에 e.ITM_ID IS NULL만 남겨도 같은 24개다.

해설

(+) 표기의 같은 함정: e.ITM_ID(+) = i.ITM_ID AND e.EVL_DT >= :v_from처럼 날짜 조건에 (+)를 빠뜨려도 똑같이 내부 조인이 된다. 외부 조인 쪽 테이블의 조건에는 빠짐없이 (+)를 붙이거나 ANSI의 ON 절에 둔다.
IS NULL을 어느 컬럼에 거나: 짝이 없는지 보려면 조인 키나 NOT NULL 컬럼에 건다. NULL이 들어갈 수 있는 일반 컬럼에 걸면 짝이 있는데 그 값만 비어 있는 행까지 골라진다(SUBQ-044).
NOT EXISTS를 권하는 까닭: 날짜 조건을 ON 절로 옮긴 외부 조인은 옵티마이저가 안티 조인으로 바꿔 같은 계획이 되기도 한다. 그래도 '없는 것을 찾는다'는 뜻이 SQL에 그대로 드러나는 NOT EXISTS가 읽기 쉽고, 조건을 둘 자리를 헷갈릴 일도 없다.
0.01초에 0건이 나오면 먼저 계획의 Starts 0과 NULL IS NOT NULL을 본다. 결과는 상품 100개 = 평가를 받은 상품 76개 + 평가 없는 상품 24개로 대조한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

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

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황