이번 달 메뉴 순위 칸이 2년 치 판매상품을 다 읽는다

기초SELECT유통07 / 700

상황

유통 본사 대시보드의 「이번 달 메뉴 순위」 칸은 5분마다 새로 고쳐지고, MD들이 화면을 열 때마다 한 번 더 호출된다. 한 번에 8천 블록 넘게 읽어서 대시보드 전체가 굼뜨다는 말이 나왔다. 개발자는 이렇게 설명한다. "판매상품(CF11)에는 판매일자 컬럼이 없어서, 판매번호 앞 여섯 자리로 달을 잘랐다." 아래는 2023년 6월(:v_ym = '202306')을 조회했을 때다.

테이블과 인덱스

CF11 판매상품: 1,096,708행 · 8,204블록(블록당 약 134행) · 판매번호 순으로 거의 정렬되어 적재되어 있다
SLE_NO VARCHAR2(15): 판매일자 8자리 + - + 그날의 일련번호 4자리('20230601-0488'). 판매번호 앞 8자리는 판매(CF10.SLE_DT)의 판매일자와 전부 같다
2023년 6월 판매상품: 45,935행(4.19%) · 팔린 메뉴 88종 · 이 행들은 테이블 371블록에 모여 있다
인덱스 CF11_PK (SLE_NO, ITM_NO): BLEVEL 2 · 리프 약 4,290블록 · 통계 최신 · 다른 인덱스 없음

원본 SQL

sql

SELECT ITM_NO, SUM(SLE_QTY) AS qty, SUM(SLE_QTY * SLE_UPRC) AS amt
  FROM CF11
 WHERE SUBSTR(SLE_NO, 1, 6) = :v_ym
 GROUP BY ITM_NO
 ORDER BY amt DESC

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

xplan

-------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |     88 |00:00:00.11 |    8206 |          |
|   1 |  SORT ORDER BY      |      |      1 |     88 |     88 |00:00:00.11 |    8206 | 6144  (0)|
|   2 |   HASH GROUP BY     |      |      1 |     88 |     88 |00:00:00.11 |    8206 | 1288K (0)|
|*  3 |    TABLE ACCESS FULL| CF11 |      1 |  10967 |  45935 |00:00:00.08 |    8206 |          |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(SUBSTR("SLE_NO",1,6)=:V_YM)

문제

CF11_PK의 선두 컬럼이 SLE_NO인데도 테이블 전체를 읽는 이유를 실행계획의 어느 줄에서 알 수 있는지 설명하시오.
결과가 바뀌지 않게 SQL을 고쳐 CF11_PK로 한 달 치 범위만 읽도록 하시오. 고친 조건이 원래 조건과 같은 행을 고르는 이유도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

SELECT ITM_NO, SUM(SLE_QTY) AS qty, SUM(SLE_QTY * SLE_UPRC) AS amt
  FROM CF11
 WHERE SLE_NO LIKE :v_ym || '%'          -- 컬럼은 그대로 두고, 앞 여섯 자리가 같은 범위만
 GROUP BY ITM_NO
 ORDER BY amt DESC;

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

xplan

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |         |      1 |        |     88 |00:00:00.03 |     885 |          |
|   1 |  SORT ORDER BY                        |         |      1 |     88 |     88 |00:00:00.03 |     885 | 6144  (0)|
|   2 |   HASH GROUP BY                       |         |      1 |     88 |     88 |00:00:00.03 |     885 | 1288K (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| CF11    |      1 |   1520 |  45935 |00:00:00.02 |     885 |          |
|*  4 |     INDEX RANGE SCAN                  | CF11_PK |      1 |   1520 |  45935 |00:00:00.01 |     182 |          |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("SLE_NO" LIKE :V_YM||'%')
       filter("SLE_NO" LIKE :V_YM||'%')

모범 답안 서술

3번 줄의 조건은 filter(SUBSTR("SLE_NO",1,6)=:V_YM)이고 access가 아니다. CF11_PK에는 판매번호 원래 값이 정렬되어 있는데, SUBSTR로 잘라 낸 값은 인덱스 어디에도 없으므로 정렬 위에서 범위를 지목할 수 없다. 그래서 109만 행을 다 읽고 그중 45,935행만 남긴다(Buffers 8,206). E-Rows 10,967은 함수를 씌운 등치 조건의 기본 선택도 1%에서 나온 값이라 실제의 4분의 1에도 못 미친다. 판매번호는 판매일자 8자리로 시작한다. 그래서 'SUBSTR(SLE_NO, 1, 6)이 202306이다'는 '판매번호가 202306으로 시작한다'와 같은 뜻이고, SLE_NO LIKE :v_ym || '%'로 바꿔도 결과는 그대로다. 앞부분이 정해진 LIKE라서 정렬 위에서 '202306'으로 시작하는 첫 항목부터 마지막 항목까지를 범위로 읽을 수 있다(access). 고친 뒤에는 4번 줄 INDEX RANGE SCAN이 리프 약 180블록을 읽고, 6월 행이 모여 있는 테이블 블록을 찾아간다. Buffers는 8,206 → 885로 준다. 5분마다 새로 고치는 대시보드라 하루로 치면 약 236만 → 25만 블록이다.

해설

함정: 앞에도 %를 붙여 LIKE '%' || :v_ym || '%'로 쓰면 인덱스를 쓰지 못하고 답도 틀린다(IDX-003). %는 뒤에만 둔다.
고친 조건은 '판매번호가 판매일자로 시작한다'는 채번 규칙에 기댄다. 규칙이 바뀔 수 있다면 CF10과 조인해 SLE_DT로 거르는 쪽이 모델에 맞지만 조인 비용이 든다. 규칙이 지켜지는지는 SELECT COUNT(*) FROM CF10 WHERE SUBSTR(SLE_NO, 1, 8) <> SLE_DT(지금 0건)로 확인해 둔다.
함수 기반 인덱스 (SUBSTR(SLE_NO, 1, 6))도 답이 되지만, SQL의 식과 글자 하나까지 같아야 쓰이고 판매를 넣을 때마다 고칠 인덱스가 하나 늘어난다. 이미 있는 PK로 풀리는 문제에 인덱스를 더할 이유가 없다.
고친 계획의 E-Rows 1,520은 실제보다 훨씬 작다. 오라클은 문자 컬럼의 LIKE 범위를 문자열을 수치로 바꿔 추정하기 때문이다(교재 03화 §2.2). 여기서는 인덱스가 맞는 길이라 괜찮지만, 추정이 틀어지면 계획이 흔들릴 수 있다.
남은 885블록의 대부분은 테이블 블록이다. 집계 컬럼까지 담은 인덱스로 더 줄이는 것은 ACC 단원의 주제다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

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

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황