「2년 연속 구매 고객」 명단이 쿼리를 손본 다음 날부터 2,000명에서 1,937명으로 줄었다

중급SELECT한빛유통299 / 700

상황

CRM팀은 해마다 1월에 「2년 연속 구매 고객」 명단(고객번호, 이름, 휴대폰, 지역)을 뽑아 감사 문자와 우편을 보낸다. 마케팅팀이 이번 달부터 같은 명단을 매일 아침 받겠다고 하자, 조민우는 "집합 연산자는 정렬을 두 번 한다"는 사내 점검 결과를 보고 INTERSECT를 EXISTS로 바꿨다. 처음 고친 SQL은 명단이 32만 줄로 나와 DISTINCT를 붙였고, 그 뒤로는 1,937명이 나온다. 작년 명단은 2,000명이었다. 오과장은 "개인정보 파기 요청으로 휴대폰 번호를 지운 고객이 몇십 명 있다"라고 했다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 인덱스 없음 · 2023년 333,245건 · 2024년 333,245건 · 고객 2,000명 모두 두 해에 주문이 있다(고객마다 해마다 164 ~ 168건)
M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · MBL_NO는 파기 요청을 받은 63명이 NULL이다(NOT NULL 제약 없음) · CUS_NM, RGN_ID는 모두 값이 있다
명단은 고객 한 명에 한 줄이어야 한다 · 통계 최신

원본 SQL

sql

-- 원래 SQL (작년까지 · 2,000명)
SELECT c.CUS_ID, c.CUS_NM, c.MBL_NO, c.RGN_ID
  FROM T_ORD_BIG o, M_CUS c
 WHERE o.ORD_DT >= DATE '2023-01-01' AND o.ORD_DT < DATE '2024-01-01'
   AND c.CUS_ID = o.CUS_ID
INTERSECT
SELECT c.CUS_ID, c.CUS_NM, c.MBL_NO, c.RGN_ID
  FROM T_ORD_BIG o, M_CUS c
 WHERE o.ORD_DT >= DATE '2024-01-01' AND o.ORD_DT < DATE '2025-01-01'
   AND c.CUS_ID = o.CUS_ID;

-- 조민우가 고친 SQL (1,937명)
SELECT DISTINCT c.CUS_ID, c.CUS_NM, c.MBL_NO, c.RGN_ID
  FROM T_ORD_BIG o, M_CUS c
 WHERE o.ORD_DT >= DATE '2023-01-01' AND o.ORD_DT < DATE '2024-01-01'
   AND c.CUS_ID = o.CUS_ID
   AND EXISTS (SELECT 1
                 FROM T_ORD_BIG x, M_CUS d
                WHERE x.ORD_DT >= DATE '2024-01-01' AND x.ORD_DT < DATE '2025-01-01'
                  AND d.CUS_ID = x.CUS_ID
                  AND d.CUS_ID = c.CUS_ID
                  AND d.CUS_NM = c.CUS_NM
                  AND d.MBL_NO = c.MBL_NO
                  AND d.RGN_ID = c.RGN_ID);

실행계획 학습용 예시

xplan

[원래 SQL · INTERSECT]
---------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |      1 |        |   2000 |00:00:00.52 |   11712 |          |
|   1 |  INTERSECTION          |           |      1 |        |   2000 |00:00:00.52 |   11712 |          |
|   2 |   SORT UNIQUE          |           |      1 |   2000 |   2000 |00:00:00.26 |    5856 |  178K (0)|
|*  3 |    HASH JOIN           |           |      1 |    333K|    333K|00:00:00.13 |    5856 | 1398K (0)|
|   4 |     TABLE ACCESS FULL  | M_CUS     |      1 |   2000 |   2000 |00:00:00.01 |      30 |          |
|*  5 |     TABLE ACCESS FULL  | T_ORD_BIG |      1 |    333K|    333K|00:00:00.05 |    5826 |          |
|   6 |   SORT UNIQUE          |           |      1 |   2000 |   2000 |00:00:00.26 |    5856 |  178K (0)|
|*  7 |    HASH JOIN           |           |      1 |    333K|    333K|00:00:00.13 |    5856 | 1398K (0)|
|   8 |     TABLE ACCESS FULL  | M_CUS     |      1 |   2000 |   2000 |00:00:00.01 |      30 |          |
|*  9 |     TABLE ACCESS FULL  | T_ORD_BIG |      1 |    333K|    333K|00:00:00.05 |    5826 |          |
---------------------------------------------------------------------------------------------------------------

[조민우의 SQL · EXISTS + DISTINCT]
---------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |      1 |        |   1937 |00:00:00.31 |   11712 |          |
|   1 |  HASH UNIQUE           |           |      1 |   2000 |   1937 |00:00:00.31 |   11712 | 1254K (0)|
|*  2 |   HASH JOIN RIGHT SEMI |           |      1 |    333K|    323K|00:00:00.24 |   11712 | 1543K (0)|
|   3 |    VIEW                | VW_SQ_1   |      1 |    333K|    333K|00:00:00.11 |    5856 |          |
|*  4 |     HASH JOIN          |           |      1 |    333K|    333K|00:00:00.10 |    5856 | 1398K (0)|
|   5 |      TABLE ACCESS FULL | M_CUS     |      1 |   2000 |   2000 |00:00:00.01 |      30 |          |
|*  6 |      TABLE ACCESS FULL | T_ORD_BIG |      1 |    333K|    333K|00:00:00.05 |    5826 |          |
|*  7 |    HASH JOIN           |           |      1 |    333K|    333K|00:00:00.10 |    5856 | 1398K (0)|
|   8 |     TABLE ACCESS FULL  | M_CUS     |      1 |   2000 |   2000 |00:00:00.01 |      30 |          |
|*  9 |     TABLE ACCESS FULL  | T_ORD_BIG |      1 |    333K|    333K|00:00:00.05 |    5826 |          |
---------------------------------------------------------------------------------------------------------------

Predicate Information (조민우의 SQL):
---------------------------------------------------
   2 - access("ITEM_1"="C"."CUS_ID" AND "ITEM_2"="C"."CUS_NM" AND "ITEM_3"="C"."MBL_NO" AND "ITEM_4"="C"."RGN_ID")
   4 - access("D"."CUS_ID"="X"."CUS_ID")
   6 - filter(("X"."ORD_DT">=TO_DATE(' 2024-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "X"."ORD_DT"<TO_DATE(' 2025-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
   7 - access("C"."CUS_ID"="O"."CUS_ID")
   9 - filter(("O"."ORD_DT">=TO_DATE(' 2023-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."ORD_DT"<TO_DATE(' 2024-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
(원래 SQL의 3번 · 5번, 7번 · 9번 줄도 같은 조인 조건과 연도 조건이다)

문제

조민우의 SQL에서 63명이 빠진 이유와, 처음 고친 SQL이 32만 줄을 낸 이유를 INTERSECT와 EXISTS가 행을 같다고 보는 기준의 차이로 설명하시오.
명단에 맞는 뜻이 어느 쪽인지 정하고, 정렬 없이 그 뜻대로 나오도록 SQL을 고치시오.

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

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

모범답안

모범 답안 SQL

sql

-- 고객 한 명에 한 줄인 M_CUS 에서 출발해, 키(CUS_ID)로만 두 해의 주문이 있는지 묻는다
--   이름 · 휴대폰 · 지역은 마스터의 값을 그대로 보여 줄 뿐이라 비교하지 않는다 (NULL 이어도 남는다)
SELECT c.CUS_ID, c.CUS_NM, c.MBL_NO, c.RGN_ID
  FROM M_CUS c
 WHERE EXISTS (SELECT 1 FROM T_ORD_BIG o
                WHERE o.CUS_ID = c.CUS_ID
                  AND o.ORD_DT >= DATE '2023-01-01' AND o.ORD_DT < DATE '2024-01-01')
   AND EXISTS (SELECT 1 FROM T_ORD_BIG x
                WHERE x.CUS_ID = c.CUS_ID
                  AND x.ORD_DT >= DATE '2024-01-01' AND x.ORD_DT < DATE '2025-01-01');

-- 키가 아닌 컬럼까지 맞춰 봐야 하는 집합 연산을 옮길 때는 NULL 안전 비교를 쓴다
--   ... AND DECODE(d.MBL_NO, c.MBL_NO, 1, 0) = 1      -- DECODE 는 NULL 끼리 같다고 본다

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

xplan

---------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |      1 |        |   2000 |00:00:00.18 |   11682 |          |
|*  1 |  HASH JOIN SEMI        |           |      1 |   2000 |   2000 |00:00:00.18 |   11682 | 1398K (0)|
|*  2 |   HASH JOIN SEMI       |           |      1 |   2000 |   2000 |00:00:00.09 |    5856 | 1398K (0)|
|   3 |    TABLE ACCESS FULL   | M_CUS     |      1 |   2000 |   2000 |00:00:00.01 |      30 |          |
|*  4 |    TABLE ACCESS FULL   | T_ORD_BIG |      1 |    333K|    333K|00:00:00.05 |    5826 |          |
|*  5 |   TABLE ACCESS FULL    | T_ORD_BIG |      1 |    333K|    333K|00:00:00.05 |    5826 |          |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("X"."CUS_ID"="C"."CUS_ID")
   2 - access("O"."CUS_ID"="C"."CUS_ID")
   4 - filter(("O"."ORD_DT">=TO_DATE(' 2023-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."ORD_DT"<TO_DATE(' 2024-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
   5 - filter(("X"."ORD_DT">=TO_DATE(' 2024-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "X"."ORD_DT"<TO_DATE(' 2025-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

모범 답안 서술

두 SQL이 다른 답을 내는 까닭은 INTERSECT와 EXISTS가 행을 같다고 보는 기준이 다르기 때문이다. 하나는 NULL이다. INTERSECT는 두 갈래의 행을 값으로 비교하면서 NULL끼리 같은 값으로 본다. 그래서 휴대폰이 NULL인 고객도 두 해의 (고객번호, 이름, NULL, 지역)이 같아 명단에 남았다. EXISTS 안의 d.MBL_NO = c.MBL_NO는 보통의 비교라서 한쪽이라도 NULL이면 참도 거짓도 아닌 UNKNOWN이 되고, 서브쿼리는 짝을 하나도 찾지 못한다. 파기 요청으로 휴대폰을 지운 63명이 이렇게 빠져 2,000 → 1,937명이 됐다. 다른 하나는 중복이다. INTERSECT는 갈래마다 SORT UNIQUE로 한 줄씩 만든 뒤 비교하지만(2번, 6번 줄), EXISTS는 바깥 행을 걸러 낼 뿐 합치지 않는다. 바깥이 2023년 주문과 고객의 조인이라 주문마다 한 줄, 약 32만 줄이 나왔고, 그래서 붙인 DISTINCT가 1번 줄 HASH UNIQUE로 집계를 다시 불렀다. 정렬 두 번을 없애려던 수정이 해시 집계 하나를 남긴 셈이다. 명단에 맞는 뜻을 정한다. 명단은 2년 연속 구매한 고객을 한 명에 한 줄씩 담고, 휴대폰이 없는 고객은 우편으로 감사 인사를 받는다. 그러므로 63명을 남기는 INTERSECT의 뜻이 맞다. 처음 고친 SQL은 중복에서, 지금 SQL은 NULL에서 원래 명단과 달라졌다. 고친 SQL은 고객 한 명에 한 줄이 나오는 M_CUS에서 출발해 키(CUS_ID)로만 두 해의 주문이 있는지 묻는다. 이름, 휴대폰, 지역은 마스터의 값을 그대로 보여 줄 뿐이라 비교할 필요가 없다. 비교하지 않으니 NULL 문제가 없고, 바깥이 고객 2,000명이라 DISTINCT도 필요 없다. 두 EXISTS는 해시 세미 조인으로 풀린다. Buffers는 약 11,700으로 원래와 같지만 SORT UNIQUE 두 번이 없어져 A-Time이 0.52 → 0.18초가 된다. 키가 아닌 컬럼까지 맞춰 봐야 하는 집합 연산을 EXISTS로 옮길 때는 DECODE(d.MBL_NO, c.MBL_NO, 1, 0) = 1처럼 NULL끼리 같다고 보는 비교를 써야 한다.

해설

비교 기준 두 가지: UNION, INTERSECT, MINUS, DISTINCT, GROUP BY는 NULL끼리 같은 값으로 묶는다. 조인과 서브쿼리의 =는 NULL과 비교하면 UNKNOWN이다. 집합 연산을 조인이나 EXISTS로 옮기면 이 차이가 결과에 나타난다(교재 08화 §7 NOT IN과 NULL).
NULL 안전 비교의 대가: DECODE, NVL(…, '없음'), (a = b OR a IS NULL AND b IS NULL)은 NULL끼리 같다고 본다. 다만 그 컬럼으로는 인덱스 접근이나 해시 조인 키를 쓰기 어려우므로 키로 짝을 찾은 뒤 나머지 컬럼을 거르는 자리에 쓴다. NVL에 넣는 값은 실제 데이터에 없는 값이어야 한다.
INTERSECT를 남기는 안: 결과가 맞고 SQL도 짧다. 매일 아침 한 번 받는 명단이라 0.52초는 급한 문제가 아니었다. 고칠 때는 일량보다 결과가 같은지를 먼저 건수와 MINUS 양방향으로 대조해야 한다(교재 08화 정합성 회귀 체크리스트).
바깥을 무엇으로 두나: 명단처럼 '한 명에 한 줄'인 결과는 그 단위의 마스터에서 출발하면 DISTINCT가 필요 없다. 주문처럼 여러 줄인 쪽에서 출발하면 중복을 지우는 일이 따라온다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황