「작년 같은 날」 칸 하나를 열 때마다 판매 2년 치를 다 읽는다

기초SELECT유통35 / 700

상황

유통 매장 점장 화면의 「작년 같은 날」 칸은 오늘과 월일이 같은 날의 판매를 올해와 작년으로 나눠 매장별 건수로 보여 준다. 매장 다섯 곳의 점장과 본사 영업팀이 하루 수백 번 연다. 개발자는 이렇게 말한다. "연도만 빼고 월일이 같은 날을 찾아야 해서 앞에 %를 붙였다. 날짜 인덱스는 있는데 안 탄다." 아래는 6월 1일(:v_mmdd = '0601')에 연 계획이다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록(전체 스캔 4,510) · 2022-01-01 ~ 2023-12-31 · 모든 컬럼 NOT NULL
SLE_DT VARCHAR2(8) '20230601' · 하루 596 ~ 793건(2022-06-01은 688건, 2023-06-01은 706건) · 하루치 판매는 테이블 약 26블록 안에 모여 있다
인덱스 CF10_PK (SLE_NO) · IX_CF10_SLE_DT (SLE_DT): BLEVEL 2 · 리프 1,393 · 통계 최신
화면은 올해 연도(:v_year, 지금은 '2023')와 월일(:v_mmdd)을 넘긴다

원본 SQL

sql

SELECT SLE_DT, SHP_NO, COUNT(*) AS sle_cnt
  FROM CF10
 WHERE SLE_DT LIKE '%' || :v_mmdd
 GROUP BY SLE_DT, SHP_NO
 ORDER BY SLE_DT, SHP_NO

실행계획 학습용 예시

xplan

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     10 |00:00:00.05 |    4510 |
|   1 |  SORT GROUP BY     |      |      1 |   3650 |     10 |00:00:00.05 |    4510 |
|*  2 |   TABLE ACCESS FULL| CF10 |      1 |  25000 |   1394 |00:00:00.05 |    4510 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("SLE_DT" LIKE '%'||:V_MMDD)

문제

IX_CF10_SLE_DT가 있는데도 TABLE ACCESS FULL로 처리되는 이유를 Predicate Information으로 설명하시오.
결과가 바뀌지 않게 SQL을 고쳐 두 날짜의 범위만 읽도록 하시오. 화면이 '해마다 같은 날'을 몇 년 치든 보여 달라고 바뀌면 어떻게 할지도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

SELECT SLE_DT, SHP_NO, COUNT(*) AS sle_cnt
  FROM CF10
 WHERE SLE_DT IN (:v_year || :v_mmdd,                               -- 올해 같은 날
                  TO_CHAR(TO_NUMBER(:v_year) - 1) || :v_mmdd)        -- 작년 같은 날
 GROUP BY SLE_DT, SHP_NO
 ORDER BY SLE_DT, SHP_NO

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

xplan

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |     10 |00:00:00.01 |      60 |
|   1 |  SORT GROUP BY                       |                |      1 |     10 |     10 |00:00:00.01 |      60 |
|   2 |   INLIST ITERATOR                    |                |      1 |        |   1394 |00:00:00.01 |      60 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| CF10          |      2 |   1370 |   1394 |00:00:00.01 |      60 |
|*  4 |     INDEX RANGE SCAN                 | IX_CF10_SLE_DT |      2 |   1370 |   1394 |00:00:00.01 |       8 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access(("SLE_DT"=:V_YEAR||:V_MMDD OR "SLE_DT"=TO_CHAR(TO_NUMBER(:V_YEAR)-1)||:V_MMDD))

모범 답안 서술

2번 줄의 조건은 filter("SLE_DT" LIKE '%'||:V_MMDD)이고, access가 아니다. IX_CF10_SLE_DT는 SLE_DT를 첫 글자부터 정렬해 두었는데, 앞이 %인 값은 앞 네 자리(연도)를 모르므로 정렬 위에서 시작점을 정할 수 없다. 그래서 50만 행을 다 읽으며 끝 네 자리를 맞춰 본다(Buffers 4,510). 실제로 맞는 행은 2022-06-01의 688건과 2023-06-01의 706건, 합계 1,394건이다. 연도는 화면이 이미 알고 있다. 이 칸이 보여 줄 날짜는 올해와 작년의 같은 월일, 단 두 개다. 앞자리의 종류가 이렇게 적으면 날짜를 펼쳐 SLE_DT IN (:v_year || :v_mmdd, 작년 || :v_mmdd)로 주면 된다. 결과는 LIKE 판과 같고, INLIST ITERATOR가 날짜마다 인덱스 범위를 한 번씩 찾아간다(4번 줄 Starts 2). 하루치 판매는 테이블 약 26블록에 모여 있으므로 두 날짜를 읽는 데 60블록이면 된다(Buffers 4,510 → 60). 화면이 '해마다 같은 날'을 몇 년 치든 보여 달라고 바뀌어도 연도 목록을 만들어 넘기면 된다. 데이터의 연도 범위로 목록을 만들어 IN이나 조인으로 주면 연도 수만큼 범위를 찾아가므로 여전히 싸다. 월일로 찾는 화면이 많아진다면 월일 가상 컬럼(SUBSTR(SLE_DT, 5, 4))과 인덱스를 두는 방법도 있다. 대가는 50만 행짜리 인덱스 하나만큼의 적재 비용이다.

해설

함정: SUBSTR(SLE_DT, 5, 4) = :v_mmdd로 바꾸면 %는 사라지지만 컬럼 가공이라 그대로 FULL이다(IDX-007). 컬럼은 그대로 두고 찾을 값을 펼친다.
2월 29일: 2023년에는 그날이 없으므로 IN 목록의 한 값은 0건이다. 화면이 작년 2월 28일을 보여 줘야 하는지는 요구를 확인해서 정한다.
앞 % LIKE의 대안은 상황마다 다르다. 앞자리 종류가 적으면 이 문제처럼 펼치고, 뒷자리로 찾아야 하면 뒷자리 컬럼이나 역순 인덱스를 둔다(IDX-045). 글 속의 단어를 찾는 것이라면 전문 검색을 쓴다(IDX-046).
교재 02화 무력화 ③: city LIKE '%OUL'은 FAST FULL 56, LIKE 'SE%'는 RANGE 10이었다. 앞이 정해져야 범위가 된다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황