「보류 건 제외」를 켜면 숫자가 나오고, 끄면 30일 내내 0건이 된다

기초SELECT한빛유통269 / 700

상황

정산팀의 「일자별 주문 현황」은 기간을 고르면 일자별 주문 건수와 금액을 보여 준다. 취소 건은 늘 빼고, 화면 위의 「보류 건 제외」를 켜면 보류(PENDING)도 뺀다. 지난주 화면을 새 프레임워크로 옮긴 뒤 이상한 일이 생겼다. 「보류 건 제외」를 켜면 숫자가 나오는데, 끄면 30일 내내 0건이다. 새 화면은 체크 상자가 꺼져 있으면 :v_excl에 빈 문자열('')을 넘긴다. 예전 화면은 꺼져 있을 때 그 조건 줄을 SQL에서 아예 빼고 보냈다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 2024년 6월 27,390행 = COMPLETE 24,651 · PENDING 2,190 · CANCEL 549
인덱스 IX_ORD_BIG_DT_ST (ORD_DT, ORD_ST, ORD_AMT): 리프 약 3,850블록 · 6월 구간 약 105블록
통계 최신 · 바인드 :v_from = '20240601', :v_to = '20240701'

원본 SQL

sql

SELECT ORD_DT, COUNT(*) AS ord_cnt, SUM(ORD_AMT) AS ord_amt
  FROM T_ORD_BIG
 WHERE ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD')
   AND ORD_ST NOT IN ('CANCEL', :v_excl)
 GROUP BY ORD_DT
 ORDER BY ORD_DT

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

xplan

-------------------------------------------------------------------------------------------------
| Id  | Operation            | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                  |      1 |        |      0 |00:00:00.01 |     108 |
|   1 |  SORT GROUP BY NOSORT|                  |      1 |     30 |      0 |00:00:00.01 |     108 |
|*  2 |   FILTER             |                  |      1 |        |      0 |00:00:00.01 |     108 |
|*  3 |    INDEX RANGE SCAN  | IX_ORD_BIG_DT_ST |      1 |  26812 |      0 |00:00:00.01 |     108 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_DATE(:V_TO,'YYYYMMDD')>TO_DATE(:V_FROM,'YYYYMMDD'))
   3 - access("ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD'))
       filter(("ORD_ST"<>'CANCEL' AND "ORD_ST"<>:V_EXCL))

[바인드]  :V_FROM = '20240601' · :V_TO = '20240701' · :V_EXCL = NULL (화면이 넘긴 '')
[켰을 때]  :V_EXCL = 'PENDING' · 같은 계획 · 3번 줄 A-Rows 24,651 · 결과 30행 · Buffers 108

문제

「보류 건 제외」를 끄면 0건이 되는 까닭을 NOT IN의 뜻과 오라클의 빈 문자열 처리로 설명하시오. 실행계획의 어느 줄에서 확인할 수 있는지도 쓰시오.
체크 상자를 켜든 끄든 맞는 결과가 나오도록 SQL을 고치시오.

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

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

모범답안

모범 답안 SQL

sql

-- 꺼져 있으면(NULL) 이미 빼는 값 'CANCEL' 로 바꾼다 : NOT IN ('CANCEL', 'CANCEL')
SELECT ORD_DT, COUNT(*) AS ord_cnt, SUM(ORD_AMT) AS ord_amt
  FROM T_ORD_BIG
 WHERE ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD')
   AND ORD_ST NOT IN ('CANCEL', NVL(:v_excl, 'CANCEL'))
 GROUP BY ORD_DT
 ORDER BY ORD_DT;

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

xplan

-------------------------------------------------------------------------------------------------
| Id  | Operation            | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                  |      1 |        |     30 |00:00:00.01 |     108 |
|   1 |  SORT GROUP BY NOSORT|                  |      1 |     30 |     30 |00:00:00.01 |     108 |
|*  2 |   FILTER             |                  |      1 |        |  26841 |00:00:00.01 |     108 |
|*  3 |    INDEX RANGE SCAN  | IX_ORD_BIG_DT_ST |      1 |  26812 |  26841 |00:00:00.01 |     108 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_DATE(:V_TO,'YYYYMMDD')>TO_DATE(:V_FROM,'YYYYMMDD'))
   3 - access("ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD'))
       filter(("ORD_ST"<>'CANCEL' AND "ORD_ST"<>NVL(:V_EXCL,'CANCEL')))

[꺼졌을 때]  26,841건 · 30행   [켰을 때]  24,651건 · 30행   (둘 다 Buffers 108)

모범 답안 서술

체크 상자를 끄면 화면이 빈 문자열('')을 넘기는데, 오라클은 빈 문자열을 NULL로 다룬다. 그래서 조건은 ORD_ST NOT IN ('CANCEL', NULL), 곧 ORD_ST <> 'CANCEL' AND ORD_ST <> NULL이 된다. <> NULL은 참도 거짓도 아닌 UNKNOWN이고, AND에 UNKNOWN이 하나 끼면 어떤 행도 참이 될 수 없다. NOT IN 목록에 NULL이 하나라도 있으면 결과는 0건이다. 실행계획 3번 줄을 보면 6월 구간의 인덱스 항목을 다 읽었지만(Buffers 108) filter(("ORD_ST"<>'CANCEL' AND "ORD_ST"<>:V_EXCL))에서 모두 걸러져 A-Rows가 0이다. 성능 문제가 아니라 조건이 틀린 것이다. 바인드 쪽 NULL을 이미 빼는 값으로 바꿔 ORD_ST NOT IN ('CANCEL', NVL(:v_excl, 'CANCEL'))로 쓴다. 꺼져 있으면 NOT IN ('CANCEL', 'CANCEL')이 되어 26,841건(COMPLETE 24,651 + PENDING 2,190)이 집계되고, 켜져 있으면 24,651건이 집계된다. 컬럼은 가공하지 않았으므로 인덱스와 계획은 그대로이고 Buffers도 108 그대로다.

해설

다른 정답: AND ORD_ST <> 'CANCEL' AND (:v_excl IS NULL OR ORD_ST <> :v_excl)도 맞다. 보여 줄 상태를 ORD_ST IN ('COMPLETE', 'PENDING')처럼 긍정형 목록으로 받는 방법도 있다. 목록을 화면에서 조립한다면 빈 칸을 목록에 넣지 않는다.
오라클만의 함정: 많은 DBMS에서는 ''이 NULL이 아니어서 같은 코드가 맞게 돈다. 오라클에서는 ''이 NULL이므로 = '', <> '', NOT IN (…, '')이 모두 UNKNOWN이 된다.
IN 목록과 NOT IN 목록의 NULL: IN 목록의 NULL은 그 칸과 맞는 행이 없을 뿐 결과 전체를 바꾸지 않는다. NOT IN 목록의 NULL은 결과 전체를 0건으로 만든다. 서브쿼리 결과에 NULL이 섞인 NOT IN(SUBQ-003)과 같은 원리다.
바인드 변수를 NVL로 감싸도 컬럼은 가공하지 않으므로 IX_ORD_BIG_DT_ST의 범위 스캔은 그대로다(교재 02화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

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

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황