영수증 번호 네 자리를 치면 판매 2천 건이 뜬다

모범 답안 SQL ​

-- 요구 재협상 : 화면이 영수증의 판매일자(:v_dt 'YYYYMMDD')와 뒤 네 자리(:v_no)를 같이 받는다
--   → 판매번호 전체를 만들어 정확히 찾는다 (선두 % 가 없다 · 인덱스는 PK 그대로)
SELECT SLE_NO, SLE_DT, SLE_HMS, SHP_NO
  FROM CF10
 WHERE SLE_NO = :v_dt || '-' || LPAD(:v_no, 4, '0');

-- (대안 · 날짜를 끝내 받을 수 없을 때만) 뒤 네 자리를 따로 인덱싱한다 — SQL 이 같은 식을 써야 탄다
-- CREATE INDEX IX_CF10_SER ON CF10 (SUBSTR(SLE_NO, 10, 4));
-- ... WHERE SUBSTR(SLE_NO, 10, 4) = LPAD(:v_no, 4, '0')      -- 날마다 한 건씩 730 건

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

-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CF10    |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  2 |   INDEX UNIQUE SCAN         | CF10_PK |      1 |      1 |      1 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("SLE_NO"=:V_DT||'-'||LPAD(:V_NO,4,'0'))

모범 답안 서술 ​

1번 줄은 filter("SLE_NO" LIKE '%'||:V_NO||'%')이고, access가 아니다. CF10_PK는 SLE_NO를 첫 글자부터 정렬해 두었는데, 앞이 %인 값은 첫 글자를 모르므로 정렬 위에서 시작점을 정할 수 없다. 그래서 50만 행을 전부 읽으며 패턴을 맞춰 본다(Buffers 4,587). 결과도 틀렸다. '%0308%'는 '어딘가에 0308이 있다'는 뜻이다. 그래서 일련번호가 0308인 판매 730건(날마다 한 건)에 판매일자가 3월 8일인 판매 1,325건(2022년 666건, 2023년 659건)이 섞이고, 양쪽에 다 걸리는 2건을 빼면 2,053건이 된다. 손님이 원한 것은 한 건이다. 영수증에는 판매일자도 찍혀 있다. 화면 주인과 요구를 다시 정해 판매일자와 뒤 네 자리를 함께 받고, 판매번호 전체를 만들어 SLE_NO = :v_dt || '-' || LPAD(:v_no, 4, '0')로 찾는다. 선두 %가 사라져 INDEX UNIQUE SCAN으로 바뀌고, Buffers는 4,587 → 4, 결과는 2,053 → 1건이 된다. 판매번호가 날짜로 시작하므로 날짜만 알아도 LIKE :v_dt || '-%'로 전방 일치 범위를 줄 수 있다. 날짜를 끝내 받을 수 없다면 대안은 둘이다. ① 뒷자리나 역순 값의 인덱스: SUBSTR(SLE_NO, 10, 4)나 REVERSE한 값으로 함수 기반 인덱스를 만들면 뒤에서부터 전방 일치로 찾을 수 있다. 대가는 인덱스 하나만큼의 적재 비용이고, SQL이 인덱스와 똑같은 식을 써야만 인덱스를 탄다(날마다 한 건씩 730건이 나오는 것도 그대로다). ② 전문 검색(Oracle Text의 CONTEXT 인덱스와 CONTAINS): 상품명이나 주소처럼 글 속의 단어를 찾는 도구다. 번호를 찾기에는 과하고, 인덱스 동기화가 커밋보다 늦을 수 있으며, 저장 공간과 관리 작업이 따로 든다.

해설 ​

• 함정: LIKE :v_no || '%'처럼 앞의 %만 떼면 인덱스는 타지만 답이 0건이 된다. 판매번호는 날짜로 시작하기 때문이다. 고치기 전에 값의 모양부터 본다.
• LIKE '%0308'(뒤의 %만 뗀 것)도 선두가 %라서 마찬가지다. 앞이 정해져야 범위가 된다(교재 02화 무력화 ③: city LIKE '%OUL'은 FAST FULL 56, LIKE 'SE%'는 RANGE 10).
• 인덱스가 테이블보다 작아서 INDEX FAST FULL SCAN으로 훑는 계획이 나올 때도 있다. 그래도 찾아가는 것이 아니라 전부 훑는 것이다. 이 SQL은 SLE_DT와 SHP_NO까지 읽어야 해서 테이블 FULL이 됐다.
• REVERSE는 오라클 문서에 없는 함수다. 뒷자리 검색이 꼭 필요하면 가상 컬럼(SUBSTR(SLE_NO, 10, 4))에 인덱스를 거는 쪽이 문서로 남기기 좋다.
• 요구 재협상이 첫 번째 답인 이유: 기술로 2,053건을 빨리 보여 줘도 직원은 여전히 눈으로 찾아야 한다.

관련 교재 ​

• 02화 · 무력화 6종 (③ 선두 % LIKE)