영수증 찾기 화면이 판매번호로 찾든 판매일자로 찾든 판매 50만 건을 다 읽는다

기초SELECT유통52 / 700

상황

유통 매장 POS의 「영수증 찾기」는 위쪽 콤보에서 '판매번호'나 '판매일자'를 고르고 값을 친다. 화면은 고른 구분을 :v_tp('NO' 또는 'DT')로, 친 값을 :v_key로 넘긴다. 개발자는 SQL을 하나로 쓰려고 비교할 컬럼을 DECODE로 고르게 했다. 매장 다섯 곳에서 하루 수백 번 쓰는데, 찾을 때마다 계산대가 멈칫한다고 한다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · SLE_NO VARCHAR2(15) '20230601-0308'(판매일자 8자리 + - + 일련번호) · SLE_DT VARCHAR2(8) · SLE_HMS · SHP_NO · 판매번호 순(날짜 순)으로 쌓였다 · 2023-06-01 판매 706건
인덱스 CF10_PK (SLE_NO) · IX_CF10_SLE_DT (SLE_DT): 둘 다 BLEVEL 2 · 통계 최신

원본 SQL

sql

SELECT SLE_NO, SLE_DT, SLE_HMS, SLE_DOW, SHP_NO
  FROM CF10
 WHERE DECODE(:v_tp, 'NO', SLE_NO, 'DT', SLE_DT) = :v_key
 ORDER BY SLE_NO DESC

실행계획 학습용 예시

xplan

[판매번호 '20230601-0308'로 찾았을 때 · 판매일자 '20230601'로 찾아도 계획은 같다(706건, Buffers 4,587)]
---------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows | A-Time      | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      1 | 00:00:00.06 |    4587 |
|   1 |  SORT ORDER BY      |      |      1 |   5000 |      1 | 00:00:00.06 |    4587 |
|*  2 |   TABLE ACCESS FULL | CF10 |      1 |   5000 |      1 | 00:00:00.06 |    4587 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(DECODE(:V_TP,'NO',"SLE_NO",'DT',"SLE_DT")=:V_KEY)

문제

판매번호에도 판매일자에도 인덱스가 있는데 어느 구분으로 찾아도 FULL인 이유를 설명하시오.
문장 하나(커서 하나)를 유지하면서 고른 구분에 맞는 인덱스로 찾아가도록 SQL을 고치시오. 화면에서 SQL을 둘로 나눠 부르는 방법과도 비교하시오.

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

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

모범답안

모범 답안 SQL

sql

SELECT SLE_NO, SLE_DT, SLE_HMS, SLE_DOW, SHP_NO
  FROM CF10
 WHERE :v_tp = 'NO'
   AND SLE_NO = :v_key            -- 판매번호 갈래 : CF10_PK
UNION ALL
SELECT SLE_NO, SLE_DT, SLE_HMS, SLE_DOW, SHP_NO
  FROM CF10
 WHERE :v_tp = 'DT'
   AND SLE_DT = :v_key            -- 판매일자 갈래 : IX_CF10_SLE_DT
 ORDER BY SLE_NO DESC

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

xplan

[판매번호로 찾았을 때]
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name           | Starts | E-Rows | A-Rows | A-Time      | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                |      1 |        |      1 | 00:00:00.01 |       4 |
|   1 |  SORT ORDER BY                          |                |      1 |    686 |      1 | 00:00:00.01 |       4 |
|   2 |   UNION-ALL                             |                |      1 |        |      1 | 00:00:00.01 |       4 |
|*  3 |    FILTER                               |                |      1 |        |      1 | 00:00:00.01 |       4 |
|   4 |     TABLE ACCESS BY INDEX ROWID         | CF10           |      1 |      1 |      1 | 00:00:00.01 |       4 |
|*  5 |      INDEX UNIQUE SCAN                  | CF10_PK        |      1 |      1 |      1 | 00:00:00.01 |       3 |
|*  6 |    FILTER                               |                |      1 |        |      0 | 00:00:00.01 |       0 |
|   7 |     TABLE ACCESS BY INDEX ROWID BATCHED | CF10           |      0 |    685 |      0 | 00:00:00.01 |       0 |
|*  8 |      INDEX RANGE SCAN                   | IX_CF10_SLE_DT |      0 |    685 |      0 | 00:00:00.01 |       0 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(:V_TP='NO')
   5 - access("SLE_NO"=:V_KEY)
   6 - filter(:V_TP='DT')
   8 - access("SLE_DT"=:V_KEY)

[판매일자 '20230601'로 찾았을 때 · 같은 커서] 3 ~ 5번 Starts 0 · 7 ~ 8번 Starts 1 · 706건 · Buffers 13

모범 답안 서술

2번 줄의 조건은 filter(DECODE(:V_TP,'NO',"SLE_NO",'DT',"SLE_DT")=:V_KEY)이다. DECODE의 결과는 구분에 따라 SLE_NO이기도 하고 SLE_DT이기도 한 값이고, 그런 값으로 정렬된 인덱스는 없다. 컬럼을 함수로 감싼 것과 같아서 CF10_PK로도 IX_CF10_SLE_DT로도 범위를 정할 수 없으므로, 어느 구분으로 찾든 판매 50만 건을 다 읽는다(Buffers 4,587). 구분마다 컬럼을 그대로 비교하는 갈래를 따로 쓰고 UNION ALL로 잇는다. 갈래마다 :v_tp = 'NO', :v_tp = 'DT'처럼 바인드만 보는 조건을 붙이면 계획에 FILTER 줄이 생기고, 실행할 때 고른 구분의 갈래만 돈다. 판매번호로 찾으면 3번 FILTER 아래만 돌고 6번 FILTER 아래는 Starts 0이다. 판매번호 갈래는 CF10_PK로 한 건을 찾고(Buffers 4,587 → 4), 판매일자 갈래는 IX_CF10_SLE_DT로 그날 706건을 찾는다(4,587 → 약 13). 문장이 하나라 커서도 하나이고 화면 코드는 그대로다. 화면에서 구분에 따라 SQL 두 개를 따로 부르게 해도 된다. 계획은 더 단순하지만 화면 코드와 SQL을 두 벌 관리해야 하고, 구분이 늘 때마다 화면을 고쳐야 한다. 한 문장의 UNION ALL은 갈래를 더하면 되므로 데이터베이스 쪽에서 끝난다.

해설

OR로 써도 된다. (:v_tp = 'NO' AND SLE_NO = :v_key) OR (:v_tp = 'DT' AND SLE_DT = :v_key)이면 옵티마이저가 OR-Expansion으로 같은 두 갈래를 만들 수 있다. 다만 펼칠지는 비용으로 정하므로, 갈래가 반드시 나뉘어야 하는 화면이라면 UNION ALL로 써 두는 편이 확실하다.
DECODE로 단위를 골랐던 IDX-014와 같은 함정이다. 그때는 입력값이 같은 컬럼의 앞부분이라 전방 일치 하나로 합칠 수 있었지만, 여기서는 비교할 컬럼이 달라 갈래를 나눠야 한다.
판매번호가 판매일자로 시작하므로 판매일자 갈래를 SLE_NO LIKE :v_key || '-%'로 쓰면 PK 하나로 두 구분을 다 받을 수 있다. 다만 판매번호 규칙에 기대는 SQL이 되므로, 규칙이 바뀌면 함께 고쳐야 한다.
바인드 조건으로 갈래를 고르는 방식은 옵션 조건이 여럿인 검색 화면에서도 쓴다. 갈래끼리 서로 겹치지 않게 조건을 짜는 것이 핵심이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황