월 마감 요약 세 개의 합계가 서로 어긋나고, 마감 배치는 판매상품 테이블을 세 번 통째로 읽는다

중급INSERT유통353 / 700

상황

유통 본사 월 마감 배치는 매월 1일 새벽에 지난달 판매를 요약 테이블 세 개에 넣는다. 매장 월 매출(CF_SHP_MON_SLS), 상품 월 판매(CF_ITM_MON_SLS), 요일 월 매출(CF_DOW_MON_SLS)이다. 세 문장은 묶는 기준만 다르고 모두 같은 달의 CF10과 CF11을 조인한다. 배치 체인의 단계 3, 5, 7이라 사이에 다른 단계가 끼어 02시 05분, 21분, 38분에 따로 돈다. 재무팀이 6월 마감을 보다가 매장 월 매출의 판매 건수 합계(20,550건)와 요일 월 매출의 판매 건수 합계(20,553건)가 다르다고 알려 왔다. 매장 두 곳의 POS는 밤에 꺼져 있다가 새벽에 켜지면서 지난 판매를 몰아서 올린다. 배치 담당 오과장은 "세 문장을 하나로 줄일 수 있으면 숫자도 시간도 같이 해결되지 않겠나"라고 묻는다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · CF10_PK (SLE_NO) · SLE_DT VARCHAR2(8) · SLE_DOW 요일('1' ~ '7') · SHP_NO 매장 6곳. 2023년 6월 판매는 20,550건이고 SLE_DT로 시작하는 인덱스는 없다.
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · 6월 판매상품은 45,210줄(상품 88개)이다.
요약 테이블 셋은 배치가 넣기만 한다(인덱스는 PK뿐). 판매 건수는 판매상품과 조인한 뒤에 세므로 COUNT(DISTINCT a.SLE_NO)로 센다.
통계는 최신이다. 이 배치는 직접 경로 적재를 쓰지 않는다.

원본 SQL

sql

-- 월 마감 · :ym = '202306'
-- [단계 3 · 02:05] 매장 월 매출
INSERT INTO CF_SHP_MON_SLS (SLE_YM, SHP_NO, SLE_CNT, SLE_AMT)
SELECT :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 :ym || '%'
   AND b.SLE_NO = a.SLE_NO
 GROUP BY a.SHP_NO;
COMMIT;

-- [단계 5 · 02:21] 상품 월 판매
INSERT INTO CF_ITM_MON_SLS (SLE_YM, ITM_NO, SLE_QTY, SLE_AMT)
SELECT :ym, b.ITM_NO, SUM(b.SLE_QTY), SUM(b.SLE_QTY * b.SLE_UPRC)
  FROM CF10 a, CF11 b
 WHERE a.SLE_DT LIKE :ym || '%'
   AND b.SLE_NO = a.SLE_NO
 GROUP BY b.ITM_NO;
COMMIT;

-- [단계 7 · 02:38] 요일 월 매출
INSERT INTO CF_DOW_MON_SLS (SLE_YM, SLE_DOW, SLE_CNT, SLE_AMT)
SELECT :ym, a.SLE_DOW, COUNT(DISTINCT a.SLE_NO), SUM(b.SLE_QTY * b.SLE_UPRC)
  FROM CF10 a, CF11 b
 WHERE a.SLE_DT LIKE :ym || '%'
   AND b.SLE_NO = a.SLE_NO
 GROUP BY a.SLE_DOW;
COMMIT;

실행계획 학습용 예시

xplan

[단계 3 · 매장 월 매출 (단계 5는 2번 줄이 HASH GROUP BY, 단계 7은 같은 모양)]
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |                |      1 |        |      0 |00:00:00.91 |   12803 |          |
|   1 |  LOAD TABLE CONVENTIONAL | CF_SHP_MON_SLS |      1 |        |      0 |00:00:00.91 |   12803 |          |
|   2 |   SORT GROUP BY          |                |      1 |      6 |      6 |00:00:00.91 |   12787 | 2154K (0)|
|*  3 |    HASH JOIN             |                |      1 |  45724 |  45210 |00:00:00.84 |   12787 | 1972K (0)|
|*  4 |     TABLE ACCESS FULL    | CF10           |      1 |  20786 |  20550 |00:00:00.04 |    4583 |          |
|   5 |     TABLE ACCESS FULL    | CF11           |      1 |   1096K|   1096K|00:00:00.12 |    8204 |          |
--------------------------------------------------------------------------------------------------------------

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

[세 단계]  단계 3 · 12,803   단계 5 · 12,881   단계 7 · 12,804   합계 Buffers 38,488 · 경과 2.7초
[대조 · 6월]  단계 3 판매 건수 합 20,550 · 단계 7 판매 건수 합 20,553 · 세 요약의 금액 합이 모두 다르다

문제

세 요약의 합계가 서로 어긋나는 까닭과, 배치가 읽는 양이 어디서 세 배가 되는지 설명하시오.
같은 달을 한 번만 조인해 세 요약을 한 문장으로 채우도록 고치시오. 어느 줄을 어느 테이블에 넣을지 무엇으로 가르는지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 한 문장 · 한 시점 : 같은 달을 한 번 조인하고, 묶음 셋을 GROUPING SETS 로 만든 뒤 묶음 번호로 테이블을 고른다
INSERT ALL
  WHEN gid = 3 THEN
    INTO CF_SHP_MON_SLS (SLE_YM, SHP_NO, SLE_CNT, SLE_AMT)  VALUES (:ym, SHP_NO, SLE_CNT, SLE_AMT)
  WHEN gid = 5 THEN
    INTO CF_ITM_MON_SLS (SLE_YM, ITM_NO, SLE_QTY, SLE_AMT)  VALUES (:ym, ITM_NO, SLE_QTY, SLE_AMT)
  WHEN gid = 6 THEN
    INTO CF_DOW_MON_SLS (SLE_YM, SLE_DOW, SLE_CNT, SLE_AMT) VALUES (:ym, SLE_DOW, SLE_CNT, SLE_AMT)
SELECT a.SHP_NO, b.ITM_NO, a.SLE_DOW,
       GROUPING_ID(a.SHP_NO, b.ITM_NO, a.SLE_DOW) AS gid,   -- 3 매장(011) · 5 상품(101) · 6 요일(110)
       COUNT(DISTINCT a.SLE_NO)    AS SLE_CNT,
       SUM(b.SLE_QTY)              AS SLE_QTY,
       SUM(b.SLE_QTY * b.SLE_UPRC) AS SLE_AMT
  FROM CF10 a, CF11 b
 WHERE a.SLE_DT LIKE :ym || '%'
   AND b.SLE_NO = a.SLE_NO
 GROUP BY GROUPING SETS ((a.SHP_NO), (b.ITM_NO), (a.SLE_DOW));
COMMIT;

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

xplan

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                           |                           |      1 |        |      0 |00:00:01.02 |   13258 |
|   1 |  MULTI-TABLE INSERT                        |                           |      1 |        |      0 |00:00:01.02 |   13258 |
|   2 |   TEMP TABLE TRANSFORMATION                |                           |      1 |        |    101 |00:00:01.01 |   13213 |
|   3 |    LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6631_2C8E1F |      1 |        |      0 |00:00:00.86 |   12787 |
|*  4 |     HASH JOIN                              |                           |      1 |  45724 |  45210 |00:00:00.84 |   12787 |
|*  5 |      TABLE ACCESS FULL                     | CF10                      |      1 |  20786 |  20550 |00:00:00.04 |    4583 |
|   6 |      TABLE ACCESS FULL                     | CF11                      |      1 |   1096K|   1096K|00:00:00.12 |    8204 |
|   7 |    LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6632_2C8E1F |      1 |        |      0 |00:00:00.04 |     141 |
|   8 |     SORT GROUP BY                          |                           |      1 |      6 |      6 |00:00:00.04 |     141 |
|   9 |      TABLE ACCESS FULL                     | SYS_TEMP_0FD9D6631_2C8E1F |      1 |  45724 |  45210 |00:00:00.01 |     141 |
|  10 |    LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6632_2C8E1F |      1 |        |      0 |00:00:00.03 |     141 |
|  11 |     HASH GROUP BY                          |                           |      1 |     88 |     88 |00:00:00.03 |     141 |
|  12 |      TABLE ACCESS FULL                     | SYS_TEMP_0FD9D6631_2C8E1F |      1 |  45724 |  45210 |00:00:00.01 |     141 |
|  13 |    LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6632_2C8E1F |      1 |        |      0 |00:00:00.04 |     141 |
|  14 |     SORT GROUP BY                          |                           |      1 |      7 |      7 |00:00:00.04 |     141 |
|  15 |      TABLE ACCESS FULL                     | SYS_TEMP_0FD9D6631_2C8E1F |      1 |  45724 |  45210 |00:00:00.01 |     141 |
|  16 |    VIEW                                    |                           |      1 |    101 |    101 |00:00:00.01 |       3 |
|  17 |     TABLE ACCESS FULL                      | SYS_TEMP_0FD9D6632_2C8E1F |      1 |    101 |    101 |00:00:00.01 |       3 |
|  18 |   INTO                                     | CF_SHP_MON_SLS            |      0 |        |      0 |            |         |
|  19 |   INTO                                     | CF_ITM_MON_SLS            |      0 |        |      0 |            |         |
|  20 |   INTO                                     | CF_DOW_MON_SLS            |      0 |        |      0 |            |         |
------------------------------------------------------------------------------------------------------------------------------------

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

[대조 · 6월]  매장 월 판매 건수 합 = 요일 월 판매 건수 합 · 세 요약의 금액 합이 모두 같다

모범 답안 서술

세 문장은 체인의 단계 3, 5, 7로 02시 05분, 21분, 38분에 따로 돌고, 문장마다 자기가 시작한 순간의 데이터를 읽는다(문장 단위 읽기 일관성). 그 사이에 POS가 6월 판매를 늦게 올리면 뒤에 도는 문장만 그 판매를 센다. 6월 판매 3건이 02시 21분과 38분 사이에 들어와 요일 월 매출(단계 7)에만 잡힌 것이 20,550과 20,553의 차이다. 금액 합계도 같은 이유로 셋이 다르다. 읽는 양도 세 문장이 똑같다. 모두 6월 CF10(4,583블록)과 CF11(8,204블록)을 통째로 읽어 45,210줄을 조인한 뒤 묶는 기준만 바꾼다. 조인 한 번에 12,787블록이므로 세 번이면 38,488블록이고, 해시 조인도 세 번 한다. 고친 문장은 같은 달을 한 번 조인하고 GROUP BY GROUPING SETS ((a.SHP_NO), (b.ITM_NO), (a.SLE_DOW))로 세 묶음을 한꺼번에 만든다. INSERT ALL의 WHEN 절은 GROUPING_ID(a.SHP_NO, b.ITM_NO, a.SLE_DOW)로 묶음을 가른다. 묶음에 들어간 컬럼은 0, 굴린 컬럼은 1인 비트라서 매장 묶음은 3(011), 상품 묶음은 5(101), 요일 묶음은 6(110)이다. 한 문장이 한 시점의 데이터를 읽으므로 세 요약의 합계가 언제나 서로 맞고, 한 트랜잭션이라 중간에 실패하면 셋 다 들어가지 않는다. COUNT(DISTINCT a.SLE_NO)는 묶음마다 원본 줄에서 따로 세므로 판매 건수도 맞다. 계획은 조인 결과 45,210줄을 메모리 임시 테이블에 한 번 싣고(TEMP TABLE TRANSFORMATION), 묶음마다 그 임시 테이블을 읽는다. Buffers는 38,488 → 13,258, 시간은 2.7초 → 1.0초다.

해설

WHEN 절을 NULL로 가르면: WHEN SHP_NO IS NOT NULL처럼 써도 지금은 맞는다. 하지만 묶는 컬럼에 진짜 NULL이 들어오는 날 그 묶음이 어느 WHEN에도 걸리지 않고 조용히 빠진다. 묶음은 GROUPING_ID로 가른다(SORT-046).
가장 잘게 먼저 묶고 거기서 셋을 만드는 답: (매장, 상품, 요일)로 먼저 묶은 작업 테이블에서 셋을 만들면 금액과 수량은 맞는다. 하지만 판매 한 건이 상품 여러 줄에 걸치므로 판매 건수(COUNT(DISTINCT))는 다시 더할 수 없다. 건수까지 맞추려면 원본에서 묶음마다 세는 GROUPING SETS가 낫다.
트랜잭션 하나의 대가: 세 테이블이 한 문장으로 들어가므로 실패하면 셋 다 다시 한다. 마감 배치에는 오히려 바라는 성질이다. 체인의 단계가 하나로 줄므로 재실행 절차서(지난달 분 DELETE 뒤 재실행)도 함께 고친다.
늦게 올라오는 판매는 남는다: 한 문장이면 세 요약이 서로는 맞지만, 마감 배치 뒤에 들어온 6월 판매는 셋 모두에서 빠진다. 마감 기준 시각(예: 1일 06시)을 정하고, 그 뒤에 들어온 판매는 다음 마감에서 보정하는 규칙을 재무팀과 정한다.
조인 결과를 담는 임시 테이블은 이 규모(45,210줄)에서는 메모리 안에서 끝난다. 2년 치를 한 번에 다시 만들 때는 110만 줄이 임시 테이블로 가므로 TEMP 사용량을 함께 본다(교재 09화 §6).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황