콜드브루 리콜 영수증 635장을 찾는 데 PK를 180블록 넘게 읽는다

기초SELECT유통87 / 700

상황

원두 공급처가 2023년 12월에 납품한 콜드브루 원액 한 로트를 회수했다. 매장에 안내하려면 그달 콜드브루(1010)가 팔린 영수증 번호를 모두 뽑아야 한다. 품질팀은 같은 조회를 다른 상품으로도 하루 수십 번 돌린다. CF11에는 판매 날짜 컬럼이 없어서, 개발자는 판매번호 앞 여섯 자리(연월)에 LIKE를 걸었다. 개발자는 "PK 인덱스를 타니까 괜찮다"고 한다.

테이블과 인덱스

CF11 판매상품: 1,096,708행 · 8,204블록 · SLE_NO는 'YYYYMMDD-NNNN'(앞 8자리가 판매일자) · 2023년 12월 46,322행 · 그중 콜드브루(1010) 635행
CF11_PK (SLE_NO, ITM_NO): BLEVEL 2 · 리프 약 4,290블록 · 통계 최신 · 다른 인덱스 없음
영수증 상세(SLE_NO = :v)와 판매 등록이 이 PK를 쓴다

원본 SQL

sql

SELECT SLE_NO
  FROM CF11
 WHERE SLE_NO LIKE :v_ym || '%'
   AND ITM_NO = :v_itm
 ORDER BY SLE_NO

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

xplan

---------------------------------------------------------------------------------------------
| Id  | Operation         | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |      1 |        |    635 |00:00:00.01 |     183 |
|*  1 |  INDEX RANGE SCAN | CF11_PK |      1 |    623 |    635 |00:00:00.01 |     183 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("SLE_NO" LIKE :V_YM||'%' AND "ITM_NO"=:V_ITM)
       filter(("ITM_NO"=:V_ITM AND "SLE_NO" LIKE :V_YM||'%'))

문제

PK 인덱스를 타는데도 183블록을 읽는 이유를 1번 줄의 access와 filter로 설명하시오. 이 LIKE가 어떤 조건으로 쓰였는지도 설명하시오.
읽는 양을 줄이는 인덱스를 설계해 DDL을 작성하시오. PK의 컬럼 순서를 바꾸는 방법이 왜 답이 아닌지도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 상품번호가 = 로 들어오므로 앞에, 판매번호(연월 범위)는 뒤에 — '콜드브루의 12월' 이 한 구간
CREATE INDEX IX_CF11_ITM_SLE ON CF11 (ITM_NO, SLE_NO) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- 세션에서 재고 → T2 → VISIBLE

SELECT SLE_NO                  -- SQL 은 그대로
  FROM CF11
 WHERE SLE_NO LIKE :v_ym || '%'
   AND ITM_NO = :v_itm
 ORDER BY SLE_NO;

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

xplan

---------------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |      1 |        |    635 |00:00:00.01 |       5 |
|*  1 |  INDEX RANGE SCAN | IX_CF11_ITM_SLE |      1 |    623 |    635 |00:00:00.01 |       5 |
---------------------------------------------------------------------------------------------
   1 - access("ITM_NO"=:V_ITM AND "SLE_NO" LIKE :V_YM||'%')
       filter("SLE_NO" LIKE :V_YM||'%')

모범 답안 서술

1번 줄의 access는 "SLE_NO" LIKE :V_YM||'%'로 시작한다. 앞부분이 고정된 LIKE는 인덱스에서 '202312'로 시작하는 구간을 읽는 범위 조건이다. 그 뒤의 ITM_NO는 access에도 있지만 filter에 또 나온다. 범위 조건 뒤의 컬럼이라 구간을 줄이지 못하고 읽으면서 거를 뿐이다. 그래서 12월 판매상품 46,322항목, 리프 약 181블록을 다 읽고 콜드브루 635건만 남긴다(Buffers 183). 상품번호가 =로 들어오므로 ITM_NO를 앞에, 판매번호를 뒤에 둔 (ITM_NO, SLE_NO) 인덱스를 만든다. '콜드브루의 12월 영수증'이 한 구간이 되어 리프 3블록만 읽는다(183 → 5). SLE_NO까지 인덱스에 있으므로 테이블에도 가지 않는다. 판매번호 LIKE는 이제 ITM_NO = 다음에 오는 범위라서 access로 쓰인다(바인드 LIKE라 filter에도 한 번 더 적힌다). PK를 (ITM_NO, SLE_NO)로 바꾸는 것은 답이 아니다. 영수증 상세(SLE_NO = :v)와 판매 등록이 PK의 선두 SLE_NO로 찾기 때문에, 순서를 바꾸면 그 SQL들이 선두 조건을 잃는다. 또 PK를 다시 만드는 동안 테이블이 잠긴다. 새 인덱스의 대가는 판매를 등록할 때마다 인덱스 하나를 더 고치는 적재 비용이다. 품질팀이 하루 수십 번 돌리는 조회가 183 → 5로 주는 값과 견주어 정하고, INVISIBLE로 재 본 뒤 VISIBLE로 바꾼다.

해설

앞 글자가 고정된 LIKE는 인덱스 범위 스캔이 된다(교재 03화 §2). 그래서 'PK를 탄다'는 말은 맞다. 문제는 그 범위가 한 달 치 전부라는 것이다. 범위를 탔는지보다 얼마나 읽었는지를 본다.
SLE_NO >= '20231201' AND SLE_NO < '20240101'로 고쳐 써도 범위라는 점은 같다. 뒤의 ITM_NO는 여전히 filter다. 이 SQL은 조건을 어떻게 써도 PK로는 한 달 치를 읽는다.
(ITM_NO, SLE_NO)는 연습 문제 IDX-006 해설에 나온 다른 정답과 같은 모양이다. 그쪽은 상품의 2년 치를 세는 SQL이었고, 여기서는 상품과 한 달이 함께 들어온다. 한 인덱스로 두 SQL을 받을 수 있는지 함께 재 본다.
판매일자를 SLE_NO 안에 담아 둔 설계라서 CF11의 날짜 조건은 늘 LIKE나 문자열 범위가 된다. CF10의 SLE_DT와 조인하는 방법도 있지만, 조인을 하나 더 하는 비용이 든다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

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

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황