JOIN-030

2년 치 월별 매장 매출을 다시 만드는 배치가 판매상품 테이블을 24번 통째로 읽는다

중급PLSQL유통180 / 700

상황

지난주 판매 데이터 정정이 있어서 2022년 1월부터 2023년 12월까지의 「월별 매장 매출」 요약을 다시 만들어야 한다. 처음 작성자는 "한 번에 하면 무거우니 한 달씩 나눠서" 월 루프를 짰다. 배치는 40초 가까이 걸리고, 그동안 다른 배치의 디스크 읽기가 느려진다. 조민우는 "한 달 SQL은 해시 조인이고 한 번에 1초 남짓이라 괜찮아 보인다"라고 한다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · CF10_PK (SLE_NO) · IX_CF10_SLE_DT (SLE_DT) (03화) · 한 달 약 2만 1천 건 · 판매 매장 5곳
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · 배치 시간대에는 버퍼 캐시에 오래 남지 않아 읽을 때마다 디스크에서 읽는다
CF_SHP_MON_SLS 월별 매장 매출 요약 (이 문제에서 쓰는 파생 테이블): SLE_YM · SHP_NO · SLE_CNT · SLE_AMT · 120행 (24개월 × 5곳) · 인덱스 없음
통계 최신 · 아래는 루프 한 바퀴(2023년 6월)의 실행계획과 배치 전체의 TKPROF 요약

원본 SQL

sql

BEGIN
  DELETE FROM CF_SHP_MON_SLS WHERE SLE_YM BETWEEN '202201' AND '202312';
  FOR m IN (SELECT TO_CHAR(ADD_MONTHS(DATE '2022-01-01', LEVEL - 1), 'YYYYMM') AS ym
              FROM DUAL CONNECT BY LEVEL <= 24) LOOP
    INSERT INTO CF_SHP_MON_SLS (SLE_YM, SHP_NO, SLE_CNT, SLE_AMT)
    SELECT m.ym, a.SHP_NO, COUNT(DISTINCT a.SLE_NO), SUM(b.SLE_QTY * b.SLE_UPRC)
      FROM CF10 a, CF11 b
     WHERE a.SLE_DT LIKE m.ym || '%'
       AND b.SLE_NO = a.SLE_NO
     GROUP BY a.SHP_NO;
  END LOOP;
  COMMIT;
END;

실행계획 학습용 예시 · 한 달 실행계획은 교재 03화 실측 규모

xplan

[루프 한 바퀴 · 2023년 6월]
------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                       |                |      1 |        |      0 |00:00:01.63 |    8566 |  8074 |
|   1 |  LOAD TABLE CONVENTIONAL               | CF_SHP_MON_SLS |      1 |        |      0 |00:00:01.63 |    8566 |  8074 |
|   2 |   SORT GROUP BY                        |                |      1 |      5 |      5 |00:00:01.63 |    8552 |  8074 |
|*  3 |    HASH JOIN                           |                |      1 |   5865 |  45935 |00:00:01.52 |    8552 |  8074 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |   2673 |  20953 |00:00:00.02 |     478 |     0 |
|*  5 |      INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |   2673 |  20953 |00:00:00.01 |      62 |     0 |
|   6 |     TABLE ACCESS FULL                  | CF11           |      1 |   1096K|   1096K|00:00:01.21 |    8074 |  8074 |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("B"."SLE_NO"="A"."SLE_NO")
   5 - access("A"."SLE_DT" LIKE :B1||'%')
       filter("A"."SLE_DT" LIKE :B1||'%')

[TKPROF · 루프 안의 INSERT]
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     24      5.02      39.14     193776     205248        171         120
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       25      5.02      39.14     193776     205248        171         120

문제

한 달 실행계획과 TKPROF 요약을 함께 보고, 배치 전체의 일량이 어디서 나오는지 설명하시오. "한 달씩 나누면 가볍다"는 판단이 이 SQL에서 왜 틀렸는지도 서술하시오.
같은 결과를 내도록 배치를 고치시오. 한 번에 처리할 때 메모리가 감당되는지 근거를 쓰고, 루프를 두고 고치는 다른 안과 비교하시오.

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

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

모범답안

모범 답안 SQL

sql

BEGIN
  DELETE FROM CF_SHP_MON_SLS WHERE SLE_YM BETWEEN '202201' AND '202312';
  -- 달을 나누지 않는다: 두 테이블을 한 번씩만 읽고, 월은 GROUP BY 에 넣는다
  INSERT INTO CF_SHP_MON_SLS (SLE_YM, SHP_NO, SLE_CNT, SLE_AMT)
  SELECT SUBSTR(a.SLE_DT, 1, 6), a.SHP_NO, COUNT(DISTINCT a.SLE_NO), SUM(b.SLE_QTY * b.SLE_UPRC)
    FROM CF10 a, CF11 b
   WHERE a.SLE_DT BETWEEN '20220101' AND '20231231'
     AND b.SLE_NO = a.SLE_NO
   GROUP BY SUBSTR(a.SLE_DT, 1, 6), a.SHP_NO;
  COMMIT;
END;

개선 후 실행계획 학습용 예시 · 한 달 실행계획은 교재 03화 실측 규모

xplan

[한 문장 · 2년]
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads |  OMem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT          |                |      1 |        |      0 |00:00:03.24 |   12598 | 12584 |       |          |
|   1 |  LOAD TABLE CONVENTIONAL  | CF_SHP_MON_SLS |      1 |        |      0 |00:00:03.24 |   12598 | 12584 |       |          |
|   2 |   SORT GROUP BY           |                |      1 |    120 |    120 |00:00:03.23 |   12584 | 12584 |    44M|   41M (0)|
|*  3 |    HASH JOIN              |                |      1 |   1096K|   1096K|00:00:02.12 |   12584 | 12584 |    29M|   33M (0)|
|*  4 |     TABLE ACCESS FULL     | CF10           |      1 |    500K|    500K|00:00:00.38 |    4510 |  4510 |       |          |
|   5 |     TABLE ACCESS FULL     | CF11           |      1 |   1096K|   1096K|00:00:01.22 |    8074 |  8074 |       |          |
---------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("B"."SLE_NO"="A"."SLE_NO")
   4 - filter(("A"."SLE_DT">='20220101' AND "A"."SLE_DT"<='20231231'))

모범 답안 서술

한 달 실행계획의 6번 줄을 보면 6월 한 달을 처리하는 데 CF11 2년 치(8,074블록)를 FULL로 다 읽는다. 해시 조인은 한 달 판매 2만 건을 build로 올리고, CF11 110만 줄을 probe로 끝까지 흘려 그달 줄만 짝을 찾는다. 루프가 24번 돌므로 CF11을 24번 통째로 읽었다(24 × 8,074 = 193,776블록). TKPROF의 query 205,248과 disk 193,776이 그 합이고, 39초의 대부분이 이 디스크 읽기다. "한 달씩 나누면 가볍다"는 NL 조인이나 인덱스로 범위를 찾아가는 SQL에서는 맞는 말이다. 해시 조인에서 달을 나누면 줄어드는 것은 build(한 달 판매)뿐이고, 조건이 없는 probe 쪽 CF11은 매번 전부 읽는다. 나눌수록 큰 테이블을 그만큼 다시 읽게 된다. 월 루프를 없애고 한 문장으로 2년을 한 번에 처리한다. 월은 SUBSTR(a.SLE_DT, 1, 6)으로 GROUP BY에 넣는다. 두 테이블을 한 번씩만 읽으므로 Buffers는 205,248 → 12,598, 디스크 읽기는 193,776 → 12,584로 줄고, 배치는 39초에서 3.2초가 된다. 결과는 월마다 매장 5곳씩 120행으로 같다. 한 번에 하면 무겁다는 걱정은 메모리로 확인한다. build는 2년 판매 50만 행으로 Used-Mem 33M (0), GROUP BY는 41M (0)으로 둘 다 optimal이다. 이 배치 시간대에 work area가 이보다 작게 잡힌다면 그때 나누는 기준을 다시 정하면 된다. 루프를 그대로 두고 고치는 안도 있다. 루프 안의 SQL에 b.SLE_NO >= 그달 1일 AND b.SLE_NO < 다음 달 1일처럼 판매번호 범위를 더하면(판매번호 앞 8자리가 판매일, JOIN-020), CF11을 CF11_PK 범위 스캔으로 그달만 읽어 한 번에 약 1,370블록, 24번이면 약 32,800블록이다. 원본보다 6배 낫지만 한 문장(12,598)보다 비싸고, 그 근거(판매번호와 판매일이 늘 같다)를 제약으로 지켜야 한다. 이 배치처럼 기간 전체를 다시 만드는 일은 한 문장이 맞다.

해설

나누는 기준은 조인 방식이 정한다: NL 조인은 바깥 집합을 나누면 왕복도 나뉘어 일량이 그대로다. 해시 조인은 probe 쪽을 나눌 수 없으면 나눈 횟수만큼 probe를 다시 읽는다. 배치를 나누기 전에 계획의 큰 쪽이 어느 줄인지부터 본다.
한 문장의 대가: 한 문장은 실패하면 처음부터 다시 돌려야 하고, undo도 한 번에 쌓인다. 이 배치는 120행을 넣는 일이라 undo가 작다. 큰 적재라면 나누더라도 probe 쪽을 범위로 줄일 수 있는 단위로 나눈다 (교재 14화).
COUNT(DISTINCT): 판매 건수는 판매상품과 조인한 뒤에 세므로 COUNT(DISTINCT a.SLE_NO)로 센다. 조인 전에 CF11을 판매번호로 먼저 묶으면(110만 → 50만) 1:1 조인이 되어 COUNT(*)로 셀 수 있다. 정렬하는 행이 줄지만, 이 배치에서는 그 차이가 크지 않다.
정정 반영: 요약을 다시 만든 뒤 월별 합계를 원장(CF11 합계)과 대조한다 (교재 08화 §10). 루프 하나를 한 문장으로 바꿀 때 결과가 같은지 확인하는 것은 교재 15화의 원칙이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 한 달 실행계획은 교재 03화 실측 규모 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 한 달 실행계획은 교재 03화 실측 규모 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황