매장이 두 배로 늘면 인덱스가 깊어진다며 판매 테이블을 연도별로 쪼개자고 한다

기초SELECT유통143 / 700

상황

유통 본사는 내년에 매장 다섯 곳을 더 연다. 판매는 지금의 두 배(하루 약 1,400건)가 될 것으로 본다. 조민우는 이렇게 제안했다. "CF10이 커지면 인덱스가 깊어져서 「일 시간대별 판매」가 느려진다. 해가 바뀔 때마다 CF10_2024처럼 연도별 테이블로 나누고, 화면에서 연도를 골라 읽게 하자." 「일 시간대별 판매」는 날짜 하나를 고르면 시간대별 판매 건수를 보여 주는 화면으로, 매장과 본사에서 하루 수백 번 연다. 박팀장은 판매가 두 배, 열 배가 되면 이 화면이 몇 블록을 읽게 되는지부터 어림해 오라고 했다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · 판매 날짜순으로 쌓였다 · 2022-01-01 ~ 2023-12-31 · 하루 596 ~ 793건 · 2023-06-01 706건이 13블록에 모여 있다
IX_CF10_SLE_DT (SLE_DT): BLEVEL 2 · 리프 1,393블록(리프 한 블록에 약 359항목) · CLUSTERING_FACTOR 9,921(교재 03화 실측) · 통계 최신
복사본에서 잰 INDEX_STATS: HEIGHT 3 · 브랜치 블록 5개(루트 1 + 그 아래 4) · 브랜치 블록 하나에 아래 블록 주소가 최대 약 460개 들어간다
늘어난 판매도 지금처럼 날짜순으로 쌓인다고 본다

원본 SQL

sql

-- 「일 시간대별 판매」 · 하루 수백 번
SELECT SUBSTR(SLE_HMS, 1, 2) AS hh, COUNT(*) AS sle_cnt
  FROM CF10
 WHERE SLE_DT = :v_dt
 GROUP BY SUBSTR(SLE_HMS, 1, 2)
 ORDER BY hh

실행계획 학습용 예시 · 건수와 블록은 적재 데이터(CF10)와 교재 03화 실측

xplan

[2023-06-01 · 706건]
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |     14 |00:00:00.01 |      18 |
|   1 |  SORT GROUP BY                       |                |      1 |     14 |     14 |00:00:00.01 |      18 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |    685 |    706 |00:00:00.01 |      18 |
|*  3 |    INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |    685 |    706 |00:00:00.01 |       5 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("SLE_DT"=:V_DT)

문제

3번 줄의 5블록과 2번 줄의 13블록이 각각 무엇인지 나누어 설명하시오. 판매가 두 배, 열 배가 되면 두 줄이 각각 몇 블록이 될지 어림하시오.
인덱스 높이(HEIGHT)가 하나 늘어나는 것은 리프가 몇 개쯤일 때인지 계산하시오.
조민우의 제안을 받아들일지, 이 화면의 일량이 무엇에 비례하는지를 근거로 판단하시오.

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

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

모범답안

모범 답안 SQL

sql

-- SQL 도 테이블도 그대로 둔다 — 하루 조회의 일량은 그날 건수에 비례하고, 2년 치든 10년 치든 상관없다
SELECT SUBSTR(SLE_HMS, 1, 2) AS hh, COUNT(*) AS sle_cnt
  FROM CF10
 WHERE SLE_DT = :v_dt
 GROUP BY SUBSTR(SLE_HMS, 1, 2)
 ORDER BY hh;

-- 어림 (높이 3 · 리프 한 블록 약 359항목 · 날짜순 적재라 하루 치 행이 모여 있다)
--   지금   (하루   706건) : 수직 2 + 리프 3   + 테이블  13 = 약  18
--   두 배  (하루 1,400건) : 수직 2 + 리프 4~5 + 테이블  26 = 약  32
--   열 배  (하루 7,000건) : 수직 2 + 리프 20  + 테이블 130 = 약 152
--   높이 4 : 리프가 약 460 × 460 = 211,600 개를 넘을 때 (행 약 7,600 만)

개선 후 실행계획 학습용 예시 · 건수와 블록은 적재 데이터(CF10)와 교재 03화 실측

xplan

[어림 · 「일 시간대별 판매」 하루 조회]
               하루 건수   3번 줄(인덱스)        2번 줄(테이블)   합계
  지금           706       2 + 리프 3 = 5         13              18
  두 배        1,400       2 + 리프 4~5 = 약 6     약 26           약 32
  열 배        7,000       2 + 리프 20 = 약 22     약 130          약 152
  2년 → 10년    706       5 (높이 3 그대로)       13              18

모범 답안 서술

3번 줄 5블록은 인덱스를 내려가는 몫(루트 1 + 브랜치 1)과 2023-06-01의 706항목이 든 리프 3블록이다(706 ÷ 359 ≈ 2, 블록 경계에 걸쳐 3). 2번 줄 13블록은 그 706행이 들어 있는 테이블 블록이다. 날짜순으로 쌓여 있어 하루 치 행이 한데 모여 있고, 그래서 CLUSTERING_FACTOR(9,921)도 테이블 블록 수에 가깝다. 합해서 18블록이다. 판매가 두 배(하루 약 1,400건)가 되면 수직 탐색은 그대로 2블록이고, 그날 항목이 든 리프가 4 ~ 5블록, 그날 행이 든 테이블 블록이 약 26블록이 되어 약 32블록이다. 열 배(하루 약 7,000건)라면 2 + 20 + 130 = 약 150블록이다. 늘어나는 것은 하루 치 건수에 비례하는 리프와 테이블 몫뿐이다. 판매가 두 배라도 데이터를 10년 쌓든 2년 쌓든 하루 조회는 32블록 안팎으로 같다. 일량은 테이블 전체 크기가 아니라 그날 건수에 비례한다. 높이는 거의 늘지 않는다. 지금 루트 아래 브랜치가 4개이고, 브랜치 하나에 아래 블록 주소가 약 460개 들어가므로 높이 3으로 담을 수 있는 리프는 약 460 × 460 = 211,600개(행으로는 약 7,600만)다. 지금 리프 1,393개의 150배가 넘는다. 그때가 되어도 높이가 4가 될 뿐이라 한 번 내려갈 때 1블록이 더 늘어난다. 그래서 제안은 받아들이지 않는다. 연도별 테이블로 나눠도 한 해 치 인덱스의 높이는 3 그대로이고, 하루 조회가 읽는 리프와 테이블 블록도 같다. 남는 것은 부담뿐이다. 화면이 연도마다 다른 테이블 이름을 골라야 하고, 12월 말부터 1월 초처럼 해를 넘는 조회는 두 테이블을 UNION ALL로 이어야 하며, 테이블과 인덱스, 권한, 통계가 해마다 하나씩 는다.

해설

진짜로 쪼갤 이유가 있다면: 오래된 판매를 해 단위로 통째로 지우거나 옮겨야 한다면 판매일로 나눈 파티션이 맞다(PART 단원). 그것도 조회를 빠르게 하려는 것이 아니라 정리를 쉽게 하려는 것이고, 화면 SQL은 바꾸지 않아도 된다.
비례하는 것을 먼저 찾는다: 인덱스 범위 스캔의 일량은 '수직 탐색(높이) + 읽는 리프 수 + 테이블로 가는 횟수'다. 높이는 로그로 늘고, 리프와 테이블 몫은 조건에 걸리는 건수로 는다. 커지는 데이터가 조건에 걸리는 건수를 늘리는지부터 본다.
열 배가 되면 볼 것: 하루 7,000건이면 테이블 몫 130블록이 가장 크다. 이 화면이 쓰는 컬럼은 SLE_DT와 SLE_HMS뿐이므로, 그때는 (SLE_DT, SLE_HMS) 인덱스로 테이블에 가지 않게 하는 편이 효과가 크다(약 28블록). 그것도 하루 건수에 비례할 뿐 테이블 크기와는 상관없다.
CF가 나빴다면: 순서 없이 쌓인 테이블이라면 테이블 몫이 그날 건수만큼(1,400행이면 약 1,400블록) 든다. 그래도 테이블 전체 크기와 상관없다는 결론은 같다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 건수와 블록은 적재 데이터(CF10)와 교재 03화 실측 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 건수와 블록은 적재 데이터(CF10)와 교재 03화 실측 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황