NOT IN을 NOT EXISTS로 바꾼 다음 날부터 관심 상품 푸시 대상 적재가 ORA-01400으로 멈춘다

모범 답안 SQL ​

-- NOT EXISTS 는 그대로 두고, 바깥에서 비로그인 조회(고객번호 NULL)를 처음부터 뺀다
INSERT INTO PUSH_TARGET (PUSH_DT, CUSTOMER_ID)
SELECT DISTINCT TRUNC(SYSDATE), v.customer_id
  FROM product_views v
 WHERE v.view_date >= TRUNC(SYSDATE) - 7
   AND v.view_date <  TRUNC(SYSDATE)
   AND v.customer_id IS NOT NULL
   AND NOT EXISTS (SELECT 1
                     FROM orders o
                    WHERE o.customer_id = v.customer_id
                      AND o.order_date >= TRUNC(SYSDATE) - 7
                      AND o.order_date <  TRUNC(SYSDATE));

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

------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |      1 |        |    201 |00:00:00.01 |     560 |          |
|   1 |  HASH UNIQUE          |               |      1 |    305 |    201 |00:00:00.01 |     560 | 1287K (0)|
|*  2 |   HASH JOIN RIGHT ANTI|               |      1 |    305 |    226 |00:00:00.01 |     560 | 1453K (0)|
|*  3 |    TABLE ACCESS FULL  | ORDERS        |      1 |    134 |    134 |00:00:00.01 |     193 |          |
|*  4 |    TABLE ACCESS FULL  | PRODUCT_VIEWS |      1 |    313 |    313 |00:00:00.01 |     367 |          |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("O"."CUSTOMER_ID"="V"."CUSTOMER_ID")
   3 - filter(("O"."ORDER_DATE">=TRUNC(SYSDATE@!)-7 AND "O"."ORDER_DATE"<TRUNC(SYSDATE@!)))
   4 - filter(("V"."CUSTOMER_ID" IS NOT NULL AND "V"."VIEW_DATE">=TRUNC(SYSDATE@!)-7 AND
              "V"."VIEW_DATE"<TRUNC(SYSDATE@!)))

[대조]  201명 = 조회한 로그인 고객 268명 − 그 기간 주문 고객 67명 · INSERT 정상 종료

모범 답안 서술 ​

NOT EXISTS는 '짝이 있는가'만 묻는다. 비로그인 조회 71건은 customer_id가 NULL이라 o.customer_id = NULL이 어떤 주문과도 참이 되지 않으므로 짝이 없다. 짝이 없으니 NOT EXISTS는 참이 되고, 71건이 모두 남아 DISTINCT 뒤에 NULL 한 줄이 된다. 2번 줄 A-Rows 297은 로그인 조회 226건(주문한 고객 67명의 조회 87건을 뺀 것)과 비로그인 조회 71건을 더한 것이고, 1번 줄에서 202행이 된다. 그 NULL 한 줄이 NOT NULL 컬럼에 들어가다 ORA-01400을 냈다. 예전 NOT IN은 바깥 값이 NULL인 행을 버렸다. NULL NOT IN (…)은 서브쿼리가 비어 있지 않은 한 UNKNOWN이라 WHERE를 통과하지 못한다(계획의 ANTI SNA, A-Rows 226). 그래서 201명만 들어갔다. 리뷰가 말한 'NOT IN의 NULL 위험'은 서브쿼리 쪽 NULL 이야기이고, 이 SQL의 서브쿼리 컬럼(orders.customer_id)은 NOT NULL이라 해당이 없었다. 바깥 쪽 NULL에서는 두 문장이 서로 반대로 움직인다. NOT EXISTS를 남기고 바깥에 v.customer_id IS NOT NULL을 적어 비로그인 조회를 처음부터 뺀다. 결과는 202 → 201행으로 바꾸기 전과 같고, Buffers는 560 그대로다. MINUS로 써도(SELECT customer_id FROM product_views … MINUS SELECT customer_id FROM orders …) 같은 문제가 남는다. 집합 연산은 NULL끼리 같은 값으로 보는데 orders 쪽에는 NULL이 없으므로, 조회 쪽의 NULL 한 줄이 결과에 남는다. 정리하면, 서브쿼리 쪽에 NULL이 있으면 NOT IN은 결과가 0건이 되고 NOT EXISTS는 영향을 받지 않는다. 바깥 쪽에 NULL이 있으면 NOT IN은 그 행을 버리고 NOT EXISTS는 남긴다. 양쪽 모두 NULL이 없을 때만 두 문장의 답이 같다.

해설 ​

• 'NOT IN을 NOT EXISTS로 바꾸면 늘 안전하다'는 반쪽 규칙이다: 서브쿼리 쪽 NULL에서는 NOT EXISTS가 맞고(SUBQ-003), 바깥 쪽 NULL에서는 둘이 반대로 움직인다. SUBQ-033은 NOT IN이 버려서 틀렸고, 이 문제는 NOT EXISTS가 남겨서 멈췄다. 바꿀 때는 바깥 쪽 컬럼에 NULL이 들어올 수 있는지부터 본다.
• 멈춘 것이 다행이다: 대상 테이블에 NOT NULL이 없었다면 NULL 고객 한 줄이 조용히 들어가고, 푸시 발송 단계가 그 줄에서 실패하거나 엉뚱한 곳으로 보냈을 것이다. 제약이 틀린 답을 에러로 바꿔 준 셈이다.
• 다른 정답: 바깥을 먼저 SELECT DISTINCT customer_id FROM product_views WHERE … AND customer_id IS NOT NULL로 줄인 뒤 NOT EXISTS를 붙여도 같다. 비로그인 조회를 따로 세야 한다면 그 SQL은 따로 둔다.
• 결과는 201명 = 조회한 로그인 고객 268명 − 그 기간 주문 고객 67명으로 대조한다.

관련 교재 ​

• 08화 · NOT IN 과 NULL — Null-Aware 안티 조인