일·월·연 단위를 한 SQL로 받는 판매 현황이 어느 단위로 골라도 판매 전체를 읽는다

모범 답안 SQL ​

-- 일 · 월 : 입력값이 SLE_DT의 앞부분이므로 전방 일치 하나로 받는다
SELECT SHP_NO, COUNT(*) AS sle_cnt
  FROM CF10
 WHERE SLE_DT LIKE :v_key || '%'
 GROUP BY SHP_NO;

-- 연 : 테이블의 절반 → 화면에서 SQL을 나눠 FULL로 읽는다 (조건은 같고 커서가 다르다)
SELECT /*+ FULL(CF10) */ SHP_NO, COUNT(*) AS sle_cnt
  FROM CF10
 WHERE SLE_DT LIKE :v_key || '%'
 GROUP BY SHP_NO;

개선 후 실행계획 학습용 예시 · 건수는 적재 데이터, Buffers는 교재 03화 실측 ​

[월 단위 — '202306']
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |      5 |00:00:00.02 |     478 |
|   1 |  HASH GROUP BY                       |                |      1 |      5 |      5 |00:00:00.02 |     478 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |    693 |  20953 |00:00:00.02 |     478 |
|*  3 |    INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |    693 |  20953 |00:00:00.01 |      62 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("SLE_DT" LIKE :V_KEY||'%')
       filter("SLE_DT" LIKE :V_KEY||'%')

모범 답안 서술 ​

2번 줄 filter를 보면 DECODE 안에 SLE_DT와 SUBSTR(SLE_DT)가 들어 있다. :v_unit은 바인드 변수지만 DECODE 식 전체의 값은 행마다 SLE_DT로 계산해야 나온다. 컬럼이 식 안에 들어가 가공된 셈이라 인덱스에서 범위를 지목할 수 없고, 어느 단위로 골라도 50만 행을 다 읽는다(Buffers 4,510). E-Rows 5,000은 함수 조건의 기본 선택도 1%다. 세 단위의 입력값은 모두 'YYYYMMDD'인 SLE_DT 값의 앞부분이다. 앞 8자리가 같다, 앞 6자리가 같다, 앞 4자리가 같다는 모두 'SLE_DT가 입력값으로 시작한다'와 같은 뜻이므로 SLE_DT LIKE :v_key || '%' 하나로 세 단위를 다 받는다. SLE_DT는 길이 8로 고정이라 전방 일치가 날짜 범위와 정확히 같다. 앞부분이 정해진 LIKE라서 INDEX RANGE SCAN으로 그 범위만 읽는다. 월 단위는 Buffers 4,510 → 478, 일 단위는 약 20블록이다. 하지만 연 단위는 249,971건으로 테이블의 절반이다. 인덱스 리프 약 700블록에 테이블 블록 방문 약 5천 번(CF 9,921의 절반)이 더해져 FULL(4,510, 멀티블록)보다 많이 읽는다. 하나의 SQL을 공유하면 처음 파싱한 값에 맞춘 계획을 세 단위가 함께 쓰므로, 화면에서 연 단위만 SQL을 나눠 FULL로 읽게 한다.

해설 ​

• 함정: LIKE로 고치기만 하고 단위를 나누지 않으면, 바인드 변수 피킹 때문에 처음 들어온 값(예: 일 단위)에 맞춘 인덱스 계획을 연 단위도 그대로 쓴다. 반대로 연 단위가 먼저 파싱되면 일 단위까지 FULL로 읽는다. 선택도가 크게 다른 입력이 한 SQL로 들어오면 SQL을 나누는 것이 가장 확실하다(바인드 피킹과 ACS는 PARSE 단원).
• 월 단위를 SLE_DT >= :v_key || '01' AND SLE_DT < 다음 달로 써도 맞다. 하지만 세 단위의 상한을 각각 계산해야 하므로 이 화면에서는 LIKE가 더 간단하다.
• (SLE_DT, SHP_NO) 인덱스를 두면 테이블에 가지 않고 인덱스만으로 셀 수 있다(교재 03화에서 (SLE_DT, SLE_NO) 커버링은 6월 한 달 102블록). 연 단위도 인덱스 FAST FULL SCAN으로 테이블보다 적게 읽는다. 대신 판매를 넣을 때마다 고칠 인덱스가 하나 늘어난다.
• DECODE와 CASE 자체가 문제가 아니다. SLE_DT >= DECODE(:v_unit, ...)처럼 바인드 쪽에만 쓰면 컬럼은 가공되지 않는다. 컬럼이 식 안에 들어가는지를 본다.

관련 교재 ​

• 03화 · 문자 날짜가 범위 비교되는 조건 (LIKE 교정형)
• 11화 · 바인드 피킹