「같은 달 두 해 비교」 화면이 어느 달을 골라도 판매 인덱스를 처음부터 끝까지 읽는다

실전SELECT유통61 / 700

상황

유통 본사 「동월 비교」 화면은 달 하나(:v_mm)를 고르면 그달의 매장별 판매 건수를 해마다 나란히 보여 준다. 개발자는 연도를 따로 받지 않으려고 판매일자의 월 두 자리만 잘라 비교했다. 지금은 2022년과 2023년 두 해이고, 내년이면 2024년이 붙는다. 점장과 MD가 하루 수백 번 연다. 박팀장은 "고치는 방법 세 가지를 읽는 양, 해가 늘 때의 손질, 적재 부담으로 비교해 와라"고 했다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · SLE_DT VARCHAR2(8) '20220101' ~ '20231231'(길이 8 고정) · SHP_NO NUMBER(5) 1001 ~ 1005 · 판매일자 순으로 쌓였다 · 한 달 약 2만 1천 건(2022년 6월 20,412건, 2023년 6월 20,953건)
인덱스 CF10_PK (SLE_NO) · IX_CF10_DT_SHP (SLE_DT, SHP_NO): BLEVEL 2 · 리프 약 1,520블록(한 달 약 63블록) · 통계 최신
POS는 판매를 하루 약 700건 넣는다

원본 SQL

sql

SELECT SUBSTR(SLE_DT, 1, 4) AS yy, SHP_NO, COUNT(*) AS sle_cnt
  FROM CF10
 WHERE SUBSTR(SLE_DT, 5, 2) = :v_mm
 GROUP BY SUBSTR(SLE_DT, 1, 4), SHP_NO
 ORDER BY yy, SHP_NO

실행계획 학습용 예시

xplan

[6월(:v_mm = '06')]
----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name           | Starts | E-Rows | A-Rows | A-Time      | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                |      1 |        |     10 | 00:00:00.09 |    1532 |
|   1 |  SORT GROUP BY         |                |      1 |     10 |     10 | 00:00:00.09 |    1532 |
|*  2 |   INDEX FAST FULL SCAN | IX_CF10_DT_SHP |      1 |   5000 |  41365 | 00:00:00.07 |    1532 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(SUBSTR("SLE_DT",5,2)=:V_MM)

문제

인덱스의 선두 컬럼이 SLE_DT인데도 2번 줄이 범위 스캔이 아닌 이유를, 6월 판매가 인덱스의 어디에 있는지로 설명하시오.
① 해마다 앞부분 일치를 OR로 잇기 ② 해 목록을 행으로 만들어 조인하기 ③ 월 컬럼(가상 컬럼이나 함수 기반 인덱스) 더하기를 읽는 양, 해가 늘 때의 손질, 적재 부담으로 비교하시오.
채택한 방법을 SQL로 작성하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ② 채택 : 해 목록을 행으로 만들고(화면 기본값 = 판매가 있는 첫해 ~ 올해), 해마다 그달의 범위를 찾는다
SELECT SUBSTR(c.SLE_DT, 1, 4) AS yy, c.SHP_NO, COUNT(*) AS sle_cnt
  FROM (SELECT TO_CHAR(TO_NUMBER(:v_yy_from) + LEVEL - 1) || :v_mm AS ym
          FROM dual
       CONNECT BY LEVEL <= TO_NUMBER(:v_yy_to) - TO_NUMBER(:v_yy_from) + 1) y,
       CF10 c
 WHERE c.SLE_DT >= y.ym || '01'
   AND c.SLE_DT <= y.ym || '31'          -- 일자는 01 ~ 31 (길이 8 고정이라 문자 순서 = 날짜 순서)
 GROUP BY SUBSTR(c.SLE_DT, 1, 4), c.SHP_NO
 ORDER BY yy, c.SHP_NO

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

xplan

[6월 · :v_yy_from = '2022' · :v_yy_to = '2023']
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name           | Starts | E-Rows | A-Rows | A-Time      | Buffers |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                |      1 |        |     10 | 00:00:00.01 |     131 |
|   1 |  SORT GROUP BY                   |                |      1 |     10 |     10 | 00:00:00.01 |     131 |
|   2 |   NESTED LOOPS                   |                |      1 |   1250 |  41365 | 00:00:00.01 |     131 |
|   3 |    VIEW                          |                |      1 |      1 |      2 | 00:00:00.01 |       0 |
|   4 |     CONNECT BY WITHOUT FILTERING |                |      1 |        |      2 | 00:00:00.01 |       0 |
|   5 |      FAST DUAL                   |                |      1 |      1 |      1 | 00:00:00.01 |       0 |
|*  6 |    INDEX RANGE SCAN              | IX_CF10_DT_SHP |      2 |   1250 |  41365 | 00:00:00.01 |     131 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access("C"."SLE_DT">="Y"."YM"||'01' AND "C"."SLE_DT"<="Y"."YM"||'31')

모범 답안 서술

2번 줄은 filter(SUBSTR("SLE_DT",5,2)=:V_MM)이다. IX_CF10_DT_SHP는 SLE_DT를 연도부터 정렬해 두었으므로, 6월 판매는 한 덩어리가 아니라 2022년 6월과 2023년 6월에 따로 모여 있다. 월 두 자리를 잘라 낸 값으로는 그 두 구간의 시작점을 정할 수 없어서, 옵티마이저는 필요한 컬럼이 다 든 인덱스를 INDEX FAST FULL SCAN으로 처음부터 끝까지 훑었다(Buffers 1,532). 어느 달을 골라도 2년 치를 다 읽는다. 해마다 '그해 그달'은 인덱스의 한 범위다. 범위 목록으로 바꾸는 방법은 셋이다. ① 해마다 앞부분 일치를 OR로 잇는다(SLE_DT LIKE '2022' || :v_mm || '%' OR SLE_DT LIKE '2023' || :v_mm || '%'). OR-Expansion으로 해마다 한 범위를 찾아 약 130블록이면 된다. 하지만 연도가 SQL에 박혀 있어서 해가 늘 때마다 SQL에 OR 갈래를 더해야 한다. ② 해 목록을 행으로 만들어 조인한다. 화면이 첫해와 끝해를 넘기고 CONNECT BY로 해마다 한 행('202206', '202306')을 만든 뒤, 해마다 그달 1일부터 31일까지의 범위로 인덱스를 찾아간다(NESTED LOOPS, 6번 줄 Starts 2). 인덱스를 늘리지 않고 Buffers 1,532 → 131이 되며, 해가 늘어도 SQL은 그대로다. 화면의 기본값만 넓히면 된다. ③ 월 컬럼을 더한다. 가상 컬럼 SLE_MM(SUBSTR(SLE_DT, 5, 2))이나 같은 식의 함수 기반 인덱스에 (SLE_MM, SHP_NO)를 걸면 해가 몇 개든 한 범위로 읽는다. 대신 POS가 판매를 넣을 때마다(하루 약 700건) 인덱스를 하나 더 고쳐야 하고, 리프 약 1,500블록만큼 저장 공간이 든다. 가상 컬럼은 컬럼 목록 없이 쓴 INSERT를 깨뜨릴 수도 있다. 그래서 ②를 고른다. 읽는 양은 ①, ③과 비슷하면서 인덱스를 늘리지 않고, 해가 늘어도 SQL을 고치지 않는다.

해설

함정: SLE_DT LIKE '____' || :v_mm || '%'도 앞 네 글자가 정해지지 않아 시작점을 정할 수 없다. 앞부분이 정해져야 범위가 된다.
상한을 < 다음 달 1일로 쓰려면 12월에서 해가 넘어가는 계산이 필요하다. 문자 날짜는 길이가 8로 고정이고 일자가 31을 넘지 않으므로 <= 그달 31일로 써도 빠지는 날이 없다.
끝해를 MAX(SLE_DT)로 매번 구하면 그 조회가 또 읽는 양이 된다. 화면이 올해를 끝해로 넘기거나 업무 달력 테이블을 둔다.
같은 원리가 '작년 같은 날'이나 '매달 25일'처럼 흩어진 날짜에도 쓰인다(IDX-035). 흩어진 조건을 인덱스가 아는 값이나 범위의 목록으로 풀어 주는 것이 핵심이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황