한 달씩 나눠야 인덱스를 탄다던 월별 매장 집계 배치가 판매 테이블 크기의 두 배 반을 읽는다

중급PLSQL유통109 / 700

상황

매일 밤 「월별 매장 판매 집계」 테이블을 2년 치 모두 다시 만든다. 반품 정정이 지난 달로 거슬러 올라가 들어오는 일이 있어서다. 대시보드와 월간 보고서가 이 집계 테이블을 읽는다. 배치 소스에는 개발자의 메모가 있다. "2년을 한 번에 읽으면 FULL이 된다. 한 달씩 24번 나눠 읽어 인덱스를 태운다." 배치 로그를 보면 한 달에 약 480블록씩, 24번이다. 오과장은 "한 번은 싸 보이는데 합치면 얼마냐"고 묻는다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · 판매 날짜순으로 쌓였다 · 2022-01-01 ~ 2023-12-31(24개월) · 한 달 약 20,800건(4.2%) · 매장 다섯 곳
인덱스 IX_CF10_SLE_DT (SLE_DT): BLEVEL 2 · 리프 1,393 · CLUSTERING_FACTOR 9,921(교재 03화 실측) · 통계 최신
집계 테이블 CF10_MON_SHP (YM, SHP_NO, SLE_CNT): 이 배치가 만드는 120행(24개월 × 5매장) · 인덱스 없음

원본 SQL

sql

DECLARE
  v_ym VARCHAR2(6);
BEGIN
  DELETE FROM CF10_MON_SHP;
  FOR m IN 0 .. 23 LOOP
    v_ym := TO_CHAR(ADD_MONTHS(DATE '2022-01-01', m), 'YYYYMM');
    INSERT INTO CF10_MON_SHP (YM, SHP_NO, SLE_CNT)
    SELECT v_ym, SHP_NO, COUNT(*)
      FROM CF10
     WHERE SLE_DT BETWEEN v_ym || '01' AND v_ym || '31'
     GROUP BY SHP_NO;
  END LOOP;
  COMMIT;
END;

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

xplan

[루프 한 번 · 2023년 12월 (20,864건)]
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                      |                |      1 |        |      0 |00:00:00.01 |     483 |
|   1 |  LOAD TABLE CONVENTIONAL              | CF10_MON_SHP   |      1 |        |      0 |00:00:00.01 |     483 |
|   2 |   HASH GROUP BY                       |                |      1 |      5 |      5 |00:00:00.01 |     479 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |   1250 |  20864 |00:00:00.01 |     479 |
|*  4 |     INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |   1250 |  20864 |00:00:00.01 |      60 |
------------------------------------------------------------------------------------------------------------------
   4 - access("SLE_DT">=:B1||'01' AND "SLE_DT"<=:B1||'31')

[TKPROF · 배치 한 번]
INSERT INTO CF10_MON_SHP (YM, SHP_NO, SLE_CNT) SELECT :B1 , SHP_NO, COUNT(*) FROM CF10 ...
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     24      0.12       0.13          0      11362         96         120
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       25      0.12       0.13          0      11362         96         120

[같은 집계를 한 문장으로 · FULL]  CF10 4,583블록 한 번

문제

한 번에 약 480블록인 루프가 24번 합쳐 11,362블록이 되는 이유를 IX_CF10_SLE_DT의 리프와 CLUSTERING_FACTOR로 설명하시오. 그 합이 테이블 전체 블록 4,583보다 큰 이유도 설명하시오.
같은 결과를 내도록 배치를 고치시오. 한 달씩 인덱스로 읽는 편이 맞는 경우가 있다면 어떤 경우인지도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 24번 나눠 읽기 → 한 번에 묶는다 · 2년 전체(100%)는 FULL 한 번이 가장 싸다
BEGIN
  DELETE FROM CF10_MON_SHP;
  INSERT INTO CF10_MON_SHP (YM, SHP_NO, SLE_CNT)
  SELECT SUBSTR(SLE_DT, 1, 6), SHP_NO, COUNT(*)
    FROM CF10
   WHERE SLE_DT BETWEEN '20220101' AND '20231231'
   GROUP BY SUBSTR(SLE_DT, 1, 6), SHP_NO;
  COMMIT;
END;

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

xplan

-----------------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |              |      1 |        |      0 |00:00:00.09 |    4592 |
|   1 |  LOAD TABLE CONVENTIONAL | CF10_MON_SHP |      1 |        |      0 |00:00:00.09 |    4592 |
|   2 |   HASH GROUP BY          |              |      1 |    120 |    120 |00:00:00.09 |    4583 |
|*  3 |    TABLE ACCESS FULL     | CF10         |      1 |   500K |   500K |00:00:00.04 |    4583 |
-----------------------------------------------------------------------------------------------
   3 - filter(("SLE_DT">='20220101' AND "SLE_DT"<='20231231'))

모범 답안 서술

루프 한 번의 계획은 4번 줄 인덱스 60블록(BLEVEL 2 + 리프 약 58)과 3번 줄 테이블 약 419블록이다. 한 달은 4.2%라서 이 한 번만 보면 인덱스가 FULL 4,583보다 싸고, 옵티마이저도 인덱스를 고른다. 하지만 배치는 그 일을 24번 해서 결국 2년 치, 곧 테이블 전체를 인덱스로 읽는다. 테이블 전체를 인덱스로 읽는 비용은 리프 전체 1,393 + CF 9,921에 루프마다 BLEVEL 2씩(48)을 더해 약 11,362블록이다. TKPROF의 query 11,362가 그 합이다. 그 합이 테이블 블록 4,583보다 큰 것은 CF 9,921이 블록 수의 약 2.2배이기 때문이다. 판매는 날짜순으로 쌓였지만 이웃한 이틀의 판매가 같은 블록에 섞여 있어서(하루치 약 700건이 13블록에 걸쳐 있다), 인덱스 순서대로 다 읽으면 블록 하나를 두 날짜에서 한 번씩, 평균 두 번쯤 방문한다. 여기에 리프 1,393블록까지 따로 읽는다. FULL은 4,583블록을 한 번씩 멀티블록으로 읽고 끝난다. 손익분기는 호출 한 번이 아니라 배치가 읽는 범위 전체로 따진다. 나누어 읽어도 합치면 100%이므로 한 문장으로 묶는다. INSERT … SELECT에서 SUBSTR(SLE_DT, 1, 6)과 SHP_NO로 함께 묶으면 같은 120행이 나오고, FULL 한 번(4,583블록)과 해시 집계로 끝난다(Buffers 11,362 → 약 4,590). 실행도 24번에서 1번으로 준다. 한 달씩 인덱스로 읽는 편이 맞는 경우도 있다. 정정이 들어온 달만 다시 집계하면 된다면 그 달 하나를 인덱스로 읽는 약 480블록이 FULL 4,583보다 싸다. 어느 달이 바뀌었는지 알 수 있으면 2년 치 재집계 대신 그 달만 지우고 다시 넣는다. 이것은 배치의 요구(정정이 어디까지 거슬러 오는가)를 현업과 다시 정해야 할 문제다.

해설

함정: 실행계획을 한 번씩만 보면 24번 모두 '인덱스를 잘 탄' 계획이다. 배치는 TKPROF나 세션 통계로 합계를 보아야 일량이 보인다(교재 14화).
CF가 더 나쁜 테이블이면 차이는 훨씬 커진다. 같은 방식으로 한빛유통 T_ORD_BIG(CF 1,000,000)을 하루씩 1,095번 나눠 읽으면 약 100만 블록이고, FULL은 5,826블록이다.
집계 테이블이 작다고 DELETE 대신 TRUNCATE를 쓰면 문장마다 커밋이 일어나, 실패했을 때 대시보드가 빈 표를 본다. 120행이면 DELETE와 INSERT를 한 트랜잭션으로 두는 편이 안전하다.
반복을 한 문장으로 바꾸는 일 자체는 DML 단원의 주제다. 이 문제의 요점은 '작게 나누면 인덱스가 이긴다'는 판단이 전체 범위에서는 뒤집힌다는 것이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

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

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황