매장 POS의 「시각으로 영수증 찾기」에 다른 매장 영수증이 섞여 나온다

기초SELECT유통66 / 700

상황

유통 매장 POS의 「시각으로 영수증 찾기」는 손님이 영수증을 잃어버리고 "오늘 오후 2시쯤 샀다"고 할 때 쓴다. 계산원이 날짜와 시각 범위를 넣으면 그 시간대 영수증이 뜬다. 매장 다섯 곳에서 하루 수백 번 쓴다. 지난주 1003 매장에서 다른 매장 영수증으로 반품을 받을 뻔한 일이 있었다. 개발자는 "실행계획을 보면 점장 앱용 인덱스를 잘 탄다. 성능은 문제없다"고 한다. 아래는 1003 매장 POS에서 2023-06-01 14시대를 찾았을 때다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · 판매 날짜순으로 쌓였다 · SLE_DT VARCHAR2(8) · SLE_HMS VARCHAR2(6) · SHP_NO NUMBER(5) 1001 ~ 1005 · 모든 컬럼 NOT NULL
2023-06-01: 706건 · 14:00:00 ~ 14:59:59 판매는 전 매장 58건, 그중 1003 매장 12건
인덱스 CF10_PK (SLE_NO) · IX_CF10_SHP_DT_HMS (SHP_NO, SLE_DT, SLE_HMS): 점장 앱 「시간대 판매」용 · BLEVEL 2 · 리프 약 1,600블록 · 통계 최신
POS 프로그램은 로그인할 때 받은 매장 번호를 세션 변수 :v_shp로 들고 있다

원본 SQL

sql

SELECT h.SLE_NO, h.SLE_HMS, h.SHP_NO
  FROM CF10 h
 WHERE h.SLE_DT  = :v_dt
   AND h.SLE_HMS BETWEEN :v_hms_from AND :v_hms_to
 ORDER BY h.SLE_HMS

실행계획 학습용 예시

xplan

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                    |      1 |        |     58 |00:00:00.01 |      22 |
|   1 |  SORT ORDER BY                       |                    |      1 |     44 |     58 |00:00:00.01 |      22 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| CF10               |      1 |     44 |     58 |00:00:00.01 |      22 |
|*  3 |    INDEX SKIP SCAN                   | IX_CF10_SHP_DT_HMS |      1 |     44 |     58 |00:00:00.01 |      16 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("H"."SLE_DT"=:V_DT AND "H"."SLE_HMS">=:V_HMS_FROM AND "H"."SLE_HMS"<=:V_HMS_TO)
       filter(("H"."SLE_DT"=:V_DT AND "H"."SLE_HMS">=:V_HMS_FROM AND "H"."SLE_HMS"<=:V_HMS_TO))

문제

실행계획의 INDEX SKIP SCAN이 이 SQL에 대해 무엇을 알려 주는지 설명하고, 다른 매장 영수증이 섞여 나온 이유를 설명하시오.
결과를 바로잡고 인덱스를 범위 스캔으로 쓰도록 SQL을 고치시오. 고친 뒤 정렬이 어떻게 되는지도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

SELECT h.SLE_NO, h.SLE_HMS, h.SHP_NO
  FROM CF10 h
 WHERE h.SHP_NO  = :v_shp                            -- POS 의 매장 (빠져 있던 업무 조건)
   AND h.SLE_DT  = :v_dt
   AND h.SLE_HMS BETWEEN :v_hms_from AND :v_hms_to
 ORDER BY h.SLE_HMS

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

xplan

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |      1 |        |     12 |00:00:00.01 |       6 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CF10               |      1 |      9 |     12 |00:00:00.01 |       6 |
|*  2 |   INDEX RANGE SCAN          | IX_CF10_SHP_DT_HMS |      1 |      9 |     12 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("H"."SHP_NO"=:V_SHP AND "H"."SLE_DT"=:V_DT AND "H"."SLE_HMS">=:V_HMS_FROM AND "H"."SLE_HMS"<=:V_HMS_TO)

모범 답안 서술

3번 줄의 INDEX SKIP SCAN은 "이 인덱스의 선두 컬럼 조건이 SQL에 없다"는 표시다. IX_CF10_SHP_DT_HMS는 SHP_NO가 선두인데 WHERE에는 날짜와 시각뿐이다. 그래서 옵티마이저는 매장 다섯 곳마다 한 번씩 들어가 그날 그 시간대 범위를 찾았고, 다섯 매장의 판매 58건이 모두 나왔다. 1003 매장 계산원 화면에 1003 매장 12건과 다른 네 매장 46건이 섞여 뜬 것이다. 성능 문제가 아니라 업무 조건이 빠진 버그다. 선두 값이 다섯 개뿐이라 SKIP SCAN은 다섯 번 들어가고 끝난다(인덱스 16블록, 전체 22블록). 그래서 성능 점검에서는 걸리지 않았다. 실행계획에서 SKIP SCAN을 보면, 빠진 선두 컬럼이 원래 있어야 하는 조건인지부터 확인한다. POS가 들고 있는 매장 번호로 SHP_NO = :v_shp를 더한다. 세 조건이 모두 access가 되어 1003 매장의 그날 14시대 12건만 찾아간다(Buffers 22 → 6). 매장과 날짜가 등치로 정해지면 그 안의 항목은 시각 순으로 놓여 있으므로, 인덱스 순서대로 읽은 결과가 이미 ORDER BY SLE_HMS 순서다. 그래서 SORT ORDER BY도 없어진다.

해설

본사 「전 매장 영수증 찾기」처럼 정말로 매장을 묻지 않는 화면이 같은 SQL을 쓰고 있다면 화면과 SQL을 나눈다. 한 SQL에 (:v_shp IS NULL OR SHP_NO = :v_shp)처럼 묶으면 다시 선두 조건이 흐려진다(IDX-051의 옵션 조건).
매장이 다섯 곳이라 SKIP SCAN이 싸게 끝나 버그를 숨겼다. 같은 실수가 매장 200곳인 버거원에서 났다면 200번 들어가 바로 느려졌을 것이다(IDX-065).
반품 화면에도 "영수증의 매장 = 반품받는 매장"을 확인하는 검사를 넣는다. 조회 SQL을 고치는 것과 별개로 업무 규칙을 한 번 더 지킨다.
SQL에 조건을 더할 때는 결과가 줄어드는 것이 맞는지 현업과 확인한다. 여기서는 POS의 요구가 "우리 매장 영수증"이라 줄어드는 것이 정답이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황