판매 테이블에서 잘 되던 월 조회 LIKE를 DW 집계 테이블에 썼더니 전체를 읽는다

기초SELECT유통27 / 700

상황

유통 본사 DW팀이 판매(CF10, CF11)를 매일 밤 집계해 「일별 상품 판매」 테이블을 만들었다. 기획팀의 「매장별 월 매출」 화면은 이 테이블에서 한 달을 골라 합계를 낸다. 개발자는 판매 테이블에서 쓰던 방식(SLE_DT LIKE '202306%', 교재 03화에서 인덱스 범위 스캔이 되던 모양)을 그대로 가져왔다. 그런데 이 화면은 인덱스가 있는데도 테이블 전체를 읽는다.

테이블과 인덱스

DW_SLE_DD 일별 상품 판매(이 문제에서 쓰는 파생 테이블, 판매와 판매상품을 매장 · 일 · 상품별로 집계): 292,000행 · 1,460블록 · 날짜순 적재
SLE_YMD NUMBER(8) (20220101 ~ 20231231) · SHP_NO 5곳 · ITM_NO · SLE_QTY · SLE_AMT · 한 달 약 12,000행
인덱스 IX_DW_SLE_DD_YMD (SLE_YMD): BLEVEL 1 · 리프 650블록 · 통계 최신

원본 SQL

sql

SELECT SHP_NO, SUM(SLE_QTY) AS qty, SUM(SLE_AMT) AS amt
  FROM DW_SLE_DD
 WHERE SLE_YMD LIKE :v_ym || '%'
 GROUP BY SHP_NO
 ORDER BY amt DESC

실행계획 학습용 예시

xplan

[2023년 6월 · :v_ym = '202306']
------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |      1 |        |      5 |00:00:00.04 |    1464 |
|   1 |  SORT ORDER BY      |           |      1 |      5 |      5 |00:00:00.04 |    1464 |
|   2 |   HASH GROUP BY     |           |      1 |      5 |      5 |00:00:00.04 |    1464 |
|*  3 |    TABLE ACCESS FULL| DW_SLE_DD |      1 |  14600 |  12000 |00:00:00.03 |    1464 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(TO_CHAR("SLE_YMD") LIKE :V_YM||'%')

문제

판매 테이블에서는 범위 스캔이던 LIKE '202306%'가 이 테이블에서는 왜 TABLE ACCESS FULL이 되는지 3번 줄로 설명하시오.
같은 결과를 내면서 인덱스 범위 스캔이 되도록 조건을 고치시오.

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

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

모범답안

모범 답안 SQL

sql

-- 숫자 컬럼은 숫자 범위로 : '202306' → 20230600 이상 20230699 이하 (일자는 01 ~ 31이라 한 달이 다 들어간다)
SELECT SHP_NO, SUM(SLE_QTY) AS qty, SUM(SLE_AMT) AS amt
  FROM DW_SLE_DD
 WHERE SLE_YMD BETWEEN TO_NUMBER(:v_ym) * 100 AND TO_NUMBER(:v_ym) * 100 + 99
 GROUP BY SHP_NO
 ORDER BY amt DESC

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

xplan

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                  |      1 |        |      5 |00:00:00.01 |      88 |
|   1 |  SORT ORDER BY                          |                  |      1 |      5 |      5 |00:00:00.01 |      88 |
|   2 |   HASH GROUP BY                         |                  |      1 |      5 |      5 |00:00:00.01 |      88 |
|*  3 |    FILTER                               |                  |      1 |        |  12000 |00:00:00.01 |      88 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED | DW_SLE_DD        |      1 |   2854 |  12000 |00:00:00.01 |      88 |
|*  5 |      INDEX RANGE SCAN                   | IX_DW_SLE_DD_YMD |      1 |   2854 |  12000 |00:00:00.01 |      28 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(TO_NUMBER(:V_YM)*100<=TO_NUMBER(:V_YM)*100+99)
   5 - access("SLE_YMD">=TO_NUMBER(:V_YM)*100 AND "SLE_YMD"<=TO_NUMBER(:V_YM)*100+99)

모범 답안 서술

3번 줄 조건은 filter(TO_CHAR("SLE_YMD") LIKE :V_YM||'%')이다. LIKE는 문자열 연산이라, 숫자 컬럼 SLE_YMD에 LIKE를 쓰면 오라클은 묵시적 형변환으로 컬럼 쪽을 TO_CHAR로 바꾼다. 인덱스에는 숫자가 정렬되어 있으므로 바뀐 문자열로는 범위를 지목하지 못하고 29만 행을 다 읽는다(Buffers 1,464). 판매 테이블의 SLE_DT는 VARCHAR2였다. 그래서 LIKE '202306%'가 형변환 없이 문자 범위가 됐다. 같은 모양의 조건이라도 컬럼 타입이 다르면 결과가 정반대가 된다. 숫자 날짜는 숫자 범위로 준다. 여덟 자리 숫자에서 2023년 6월은 20230600 이상 20230699 이하에 모두 들어간다. SLE_YMD BETWEEN TO_NUMBER(:v_ym) * 100 AND TO_NUMBER(:v_ym) * 100 + 99로 고치면 계산은 바인드 쪽에서 일어나고 컬럼은 그대로라 access로 한 달치만 읽는다(Buffers 1,464 → 88).

해설

같은 모양, 다른 타입: 문자 날짜(VARCHAR2(8))에서는 LIKE '202306%'가 교정형이고, 숫자 날짜(NUMBER(8))에서는 같은 모양이 컬럼 가공이다. 조건을 옮겨 쓸 때는 컬럼 타입부터 확인한다.
>= :v_ym * 100 + 1 AND < (:v_ym + 1) * 100처럼 써도 된다. 12월도 20231300 미만이라 맞다. 다만 BETWEEN 20230601 AND 20230630처럼 끝 날짜를 손으로 적으면 31일이 있는 달을 놓치기 쉽다.
E-Rows가 2,854로 실제 12,000건보다 적다. 숫자 날짜는 20221231 다음 값이 20230101이라 값 사이에 빈 구간이 커서, 옵티마이저가 범위를 작게 본다. 문자 날짜와 마찬가지로 DATE보다 추정이 흐리다(교재 03화 §2.2).
새로 만드는 집계 테이블이라면 날짜는 DATE로 둔다. 숫자 날짜와 문자 날짜는 모두 이런 함정을 하나씩 안고 있다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황