7월 미정산 주문을 개발자 네 명이 뽑았더니 0건, 31건, 58건, 110건이 나왔다

실전SELECT한빛유통277 / 700

상황

재무팀은 매달 초 「월말 정산 대사」로 지난달 결제 완료 주문 가운데 정산 행이 하나도 없는 주문의 건수와 금액을 받는다. 8월 초 7월분을 개발자 네 명이 각자 짠 SQL로 뽑았더니 답이 0건, 31건, 58건, 110건으로 모두 달랐다. 넷 다 0.03초 안팎에 1,536블록을 읽었다. 7월부터 정산 테이블에는 전에 없던 행이 생겼다. 재무팀이 주문 없이 금액만 맞추는 수기 조정 정산을 넣기 시작했고, 금액이 정해지기 전의 정산 보류 행과 부분 취소 재정산(한 주문에 정산 행 두 줄)도 들어온다.

테이블과 인덱스

T_ORD: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · 2024년 7월 결제 4,247건 중 COMPLETE 3,836건
T_ORD_STLM: 약 10만 행 · 640블록 · 인덱스 없음 · 컬럼 제약 없음(모두 NULL 허용) · 정산 금액 STLM_AMT에는 결제 금액(PAY_AMT)을 넣는다
7월 COMPLETE 3,836건의 정산 상태: 정산 행 한 줄 3,726건 · 보류 행(STLM_AMT NULL) 한 줄 27건 · 재정산 두 줄(금액을 둘로 나눠 적음) 52건 · 정산 행 없음 31건
수기 조정 정산 3행은 ORD_SEQ가 NULL이다 · 통계 최신
재무팀의 정의: 결제 완료 주문 가운데 그 주문의 정산 행이 하나도 없는 주문(보류와 재정산은 정산 행이 있는 것으로 본다)

원본 SQL

sql

-- (A) 개발자 1 : NOT IN
SELECT COUNT(*), SUM(o.PAY_AMT)
  FROM T_ORD o
 WHERE o.PAY_DT >= DATE '2024-07-01' AND o.PAY_DT < DATE '2024-08-01'
   AND o.ORD_ST = 'COMPLETE'
   AND o.ORD_SEQ NOT IN (SELECT s.ORD_SEQ FROM T_ORD_STLM s);

-- (B) 개발자 2 : NOT EXISTS
SELECT COUNT(*), SUM(o.PAY_AMT)
  FROM T_ORD o
 WHERE o.PAY_DT >= DATE '2024-07-01' AND o.PAY_DT < DATE '2024-08-01'
   AND o.ORD_ST = 'COMPLETE'
   AND NOT EXISTS (SELECT 1 FROM T_ORD_STLM s WHERE s.ORD_SEQ = o.ORD_SEQ);

-- (C) 개발자 3 : 외부 조인 + IS NULL
SELECT COUNT(*), SUM(o.PAY_AMT)
  FROM T_ORD o
  LEFT OUTER JOIN T_ORD_STLM s
    ON s.ORD_SEQ = o.ORD_SEQ
 WHERE o.PAY_DT >= DATE '2024-07-01' AND o.PAY_DT < DATE '2024-08-01'
   AND o.ORD_ST = 'COMPLETE'
   AND s.STLM_AMT IS NULL;

-- (D) 개발자 4 : MINUS (금액까지 맞춰 본다)
SELECT COUNT(*), SUM(amt)
  FROM (SELECT o.ORD_SEQ, o.PAY_AMT AS amt
          FROM T_ORD o
         WHERE o.PAY_DT >= DATE '2024-07-01' AND o.PAY_DT < DATE '2024-08-01'
           AND o.ORD_ST = 'COMPLETE'
        MINUS
        SELECT s.ORD_SEQ, s.STLM_AMT
          FROM T_ORD_STLM s);

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

xplan

[A] NOT IN · 결과 0건
----------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |      1 |        |      1 |00:00:00.03 |    1536 |          |
|   1 |  SORT AGGREGATE       |            |      1 |      1 |      1 |00:00:00.03 |    1536 |          |
|*  2 |   HASH JOIN ANTI NA   |            |      1 |     38 |      0 |00:00:00.03 |    1536 | 1335K (0)|
|*  3 |    TABLE ACCESS FULL  | T_ORD      |      1 |   3836 |   3836 |00:00:00.01 |     896 |          |
|   4 |    TABLE ACCESS FULL  | T_ORD_STLM |      1 |    100K|    100K|00:00:00.01 |     640 |          |
   2 - access("O"."ORD_SEQ"="S"."ORD_SEQ")

[B] NOT EXISTS · 결과 31건 · 7,904,300원
|   0 | SELECT STATEMENT      |            |      1 |        |      1 |00:00:00.03 |    1536 |          |
|   1 |  SORT AGGREGATE       |            |      1 |      1 |      1 |00:00:00.03 |    1536 |          |
|*  2 |   HASH JOIN ANTI      |            |      1 |     38 |     31 |00:00:00.03 |    1536 | 1335K (0)|
|*  3 |    TABLE ACCESS FULL  | T_ORD      |      1 |   3836 |   3836 |00:00:00.01 |     896 |          |
|   4 |    TABLE ACCESS FULL  | T_ORD_STLM |      1 |    100K|    100K|00:00:00.01 |     640 |          |
   2 - access("S"."ORD_SEQ"="O"."ORD_SEQ")

[C] 외부 조인 + STLM_AMT IS NULL · 결과 58건
|   0 | SELECT STATEMENT      |            |      1 |        |      1 |00:00:00.03 |    1536 |          |
|   1 |  SORT AGGREGATE       |            |      1 |      1 |      1 |00:00:00.03 |    1536 |          |
|*  2 |   FILTER              |            |      1 |        |     58 |00:00:00.03 |    1536 |          |
|*  3 |    HASH JOIN OUTER    |            |      1 |   3836 |   3888 |00:00:00.03 |    1536 | 1335K (0)|
|*  4 |     TABLE ACCESS FULL | T_ORD      |      1 |   3836 |   3836 |00:00:00.01 |     896 |          |
|   5 |     TABLE ACCESS FULL | T_ORD_STLM |      1 |    100K|    100K|00:00:00.01 |     640 |          |
   2 - filter("S"."STLM_AMT" IS NULL)
   3 - access("S"."ORD_SEQ"="O"."ORD_SEQ")

[D] MINUS · 결과 110건
|   0 | SELECT STATEMENT      |            |      1 |        |      1 |00:00:00.05 |    1536 |          |
|   1 |  SORT AGGREGATE       |            |      1 |      1 |      1 |00:00:00.05 |    1536 |          |
|   2 |   VIEW                |            |      1 |   3836 |    110 |00:00:00.05 |    1536 |          |
|   3 |    MINUS              |            |      1 |        |    110 |00:00:00.05 |    1536 |          |
|   4 |     SORT UNIQUE       |            |      1 |   3836 |   3836 |00:00:00.01 |     896 |  178K (0)|
|*  5 |      TABLE ACCESS FULL| T_ORD      |      1 |   3836 |   3836 |00:00:00.01 |     896 |          |
|   6 |     SORT UNIQUE       |            |      1 |    100K|    100K|00:00:00.04 |     640 | 4122K (0)|
|   7 |      TABLE ACCESS FULL| T_ORD_STLM |      1 |    100K|    100K|00:00:00.01 |     640 |          |

(네 계획 모두 T_ORD 쪽 filter 는 같다 : "O"."ORD_ST"='COMPLETE' AND "O"."PAY_DT">=TO_DATE(' 2024-07-01 …') AND "O"."PAY_DT"<TO_DATE(' 2024-08-01 …'))

문제

네 SQL의 답이 0건, 31건, 58건, 110건으로 갈린 까닭을 각 SQL의 뜻과 정산 테이블의 새 행(수기 조정, 보류, 재정산)으로 설명하시오.
재무팀의 정의에 맞는 SQL을 고르고, NOT IN을 써야 한다면 어떻게 고칠지 함께 쓰시오. 고른 SQL이 맞다는 것을 무엇과 대조해 보일지도 쓰시오.
네 SQL의 일량과 부작용(NULL, 중복, 정렬)을 비교하고, 재무팀이 보류와 재정산 건수도 함께 보고 싶어 할 때 어떻게 할지 서술하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 재무팀 정의 그대로 : 결제 완료 + 그 주문의 정산 행이 하나도 없다
SELECT COUNT(*) AS unsettled_cnt, SUM(o.PAY_AMT) AS unsettled_amt
  FROM T_ORD o
 WHERE o.PAY_DT >= DATE '2024-07-01' AND o.PAY_DT < DATE '2024-08-01'
   AND o.ORD_ST = 'COMPLETE'
   AND NOT EXISTS (SELECT 1 FROM T_ORD_STLM s WHERE s.ORD_SEQ = o.ORD_SEQ);

-- 보류와 재정산은 따로 센다 (정산 행은 있지만 금액이 비었거나 두 줄 이상)
SELECT SUM(CASE WHEN x.hold_cnt > 0 THEN 1 ELSE 0 END) AS hold_orders,
       SUM(CASE WHEN x.row_cnt  > 1 THEN 1 ELSE 0 END) AS resettled_orders
  FROM T_ORD o,
       (SELECT ORD_SEQ, COUNT(*) AS row_cnt, COUNT(*) - COUNT(STLM_AMT) AS hold_cnt
          FROM T_ORD_STLM
         WHERE ORD_SEQ IS NOT NULL
         GROUP BY ORD_SEQ) x
 WHERE o.PAY_DT >= DATE '2024-07-01' AND o.PAY_DT < DATE '2024-08-01'
   AND o.ORD_ST = 'COMPLETE'
   AND x.ORD_SEQ = o.ORD_SEQ;

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

xplan

----------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |      1 |        |      1 |00:00:00.03 |    1536 |          |
|   1 |  SORT AGGREGATE       |            |      1 |      1 |      1 |00:00:00.03 |    1536 |          |
|*  2 |   HASH JOIN ANTI      |            |      1 |     38 |     31 |00:00:00.03 |    1536 | 1335K (0)|
|*  3 |    TABLE ACCESS FULL  | T_ORD      |      1 |   3836 |   3836 |00:00:00.01 |     896 |          |
|   4 |    TABLE ACCESS FULL  | T_ORD_STLM |      1 |    100K|    100K|00:00:00.01 |     640 |          |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("S"."ORD_SEQ"="O"."ORD_SEQ")
   3 - filter(("O"."ORD_ST"='COMPLETE' AND "O"."PAY_DT">=TO_DATE(' 2024-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
              AND "O"."PAY_DT"<TO_DATE(' 2024-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

[대조]  COMPLETE 3,836 = 정산 행 있는 주문 3,805 (한 줄 3,726 + 보류 27 + 재정산 52) + 미정산 31
        보류·재정산 SQL : hold_orders 27 · resettled_orders 52

모범 답안 서술

(A) NOT IN이 0건인 것은 수기 조정 정산 3행의 ORD_SEQ가 NULL이기 때문이다. o.ORD_SEQ NOT IN (…, NULL, …)은 <> NULL이 끼어 UNKNOWN이 되므로 어떤 주문도 참이 될 수 없다. 계획의 HASH JOIN ANTI NA는 'NULL이 있으면 0건'이라는 NOT IN의 뜻을 그대로 지켰다. 6월까지는 정산 테이블에 NULL이 없어서 (B)와 답이 같았을 뿐이다. (B) NOT EXISTS는 주문마다 그 주문번호의 정산 행이 있는지만 본다. NULL 주문번호는 어떤 주문과도 짝이 되지 않으므로 영향이 없고, 보류와 재정산은 정산 행이 있으므로 빠진다. 정의 그대로 31건(7,904,300원)이다. (C) 외부 조인 + s.STLM_AMT IS NULL은 IS NULL을 조인 키가 아닌 NULL 허용 컬럼에 걸었다. 짝이 없는 31건 말고도, 짝은 찾았지만 금액이 비어 있는 보류 27건까지 골라 58건이 됐다. 짝이 없는지를 보려면 s.ORD_SEQ IS NULL처럼 조인 키에 걸어야 한다. 또 재정산 52건은 정산 행 두 줄과 붙어 조인 결과가 3,888행으로 불어나므로, IS NULL 대신 금액을 더하는 SQL이었다면 이 중복이 그대로 답에 들어갔을 것이다. (D) MINUS는 고른 컬럼 전부를 한 묶음으로 비교한다. (주문번호, 결제 금액)과 (주문번호, 정산 금액)을 비교했으므로 정산 행이 없는 31건에 더해, 금액이 NULL인 보류 27건과 금액을 둘로 나눠 적은 재정산 52건까지 '다르다'고 나와 110건이 됐다. 이것은 '정산이 없는 주문'이 아니라 '금액이 한 줄로 맞지 않는 주문'이라는 다른 질문의 답이다. 그래서 (B)를 고른다. NOT IN을 꼭 써야 한다면 서브쿼리에 WHERE s.ORD_SEQ IS NOT NULL을 넣어야 31건이 된다. 맞다는 증거는 정의에서 나오는 건수 대조다. 7월 COMPLETE 3,836건 = 정산 행이 있는 주문 3,805건(한 줄 3,726 + 보류 27 + 재정산 52) + 미정산 31건이어야 하고, 미정산 31건의 주문번호를 정산 테이블의 주문번호와 MINUS로 맞춰 겹치는 것이 0건인지도 본다. 일량은 넷 다 두 테이블을 한 번씩 읽어 1,536블록으로 같다. 차이는 부작용에 있다. (A)는 정산 쪽에 NULL이 한 행만 생겨도 조용히 0이 되고, (C)는 NULL 허용 컬럼의 IS NULL로 보류를 섞으며 1:M 조인이 재정산 행을 곱한다. (D)는 정산 테이블 10만 행 전체를 SORT UNIQUE로 정렬하고(Used-Mem 약 4MB) 중복을 지운다. (B)만 정렬도 곱하기도 없이 정의를 그대로 옮긴다. 보류와 재정산을 함께 보여 주려면 (B)는 그대로 두고, 정산 테이블을 주문별로 먼저 묶어(COUNT(*)와 COUNT(*) - COUNT(STLM_AMT)) 주문에 붙이는 SQL을 하나 더 둔다(보류 27, 재정산 52). 한 SQL에 섞으면 (C)처럼 정의가 흐려진다.

해설

'지금 NULL이 없어서 같다': 6월까지는 (A), (B), (C)의 답이 같았다. 정산 테이블에 NULL과 다건 행이 없었기 때문이다. 대사 SQL은 테이블이 받아들일 수 있는 모든 행을 전제로 짜야 한다. 제약이 없는 컬럼에는 NULL과 중복이 언젠가 들어온다(교재 08화 §7).
제약으로 막는 길: 수기 조정 정산을 주문 없는 정산 전용 테이블로 옮기거나 ORD_SEQ에 NOT NULL을 걸면 (A)도 맞게 돈다. 업무 흐름을 바꾸는 일이므로 재무팀과 정한다.
(C)를 고치는 답: s.STLM_AMT IS NULL을 s.ORD_SEQ IS NULL로 바꾸면 31건이 되고, 옵티마이저는 외부 조인을 안티 조인으로 바꿔 (B)와 같은 계획을 쓴다. 그래도 재정산처럼 1:M인 쪽을 외부 조인으로 붙이는 SQL은 집계 칸이 늘면 곧바로 곱하기 함정으로 이어진다.
대조의 기준: 빠른 SQL끼리 비교하지 말고 정의에서 나오는 숫자(COMPLETE 건수 = 정산 있는 주문 + 미정산)로 맞춘다. MINUS 대조는 중복을 보지 못하므로 건수 대조와 함께 쓴다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

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

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황