작업 테이블을 임시 테이블로 바꾼 첫 달, 월 판매 요약이 에러 없이 0건으로 끝났다

기초PLSQL유통483 / 700

상황

본사 레거시 유통의 월 마감에는 「매장 상품 월 판매 요약」 단계가 있다. 지난달까지는 영구 작업 테이블 CF11_WRK_MON을 DELETE로 비우고 다시 채웠는데, 배치 담당자가 "작업 테이블은 임시 테이블로 바꾸면 redo도 줄고 비울 일도 없다"는 말을 듣고 전역 임시 테이블 GT_SLE_MON으로 바꿨다. 사내 배치 표준의 "단계가 끝날 때마다 커밋한다"는 그대로 지켰다. 12월 1일 02:10에 돈 11월분은 0.4초 만에 SUCCEEDED로 끝났다. 그런데 MD팀 화면의 11월 매장 상품 판매가 한 줄도 나오지 않는다. 에러 로그는 비어 있다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · 2022-01-01 ~ 2023-12-31 · 판매 날짜순 적재 · 2023년 11월 20,712건 · CF10_PK (SLE_NO) · IX_CF10_SLE_DT (SLE_DT)
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · 11월분 45,359행
CF01 상품: 109행 · 5블록 · PK ITM_NO · CTG_NO
GT_SLE_MON(이 문제용 전역 임시 테이블): 원본 SQL 맨 위의 DDL로 만들었다. 11월에 팔린 매장 × 상품 조합은 531개다(매장 5곳).
CF11_SHP_MON_SUM(요약, 이 문제용): PK (SLE_YM, SHP_NO, ITM_NO) · MD팀 화면이 아침에 읽는다.
T_BAT_LOG(배치 기록): JOB_NM · STEP · ROWS_DONE · DONE_AT

원본 SQL

sql

-- 지난달 바꾼 것: 영구 작업 테이블 CF11_WRK_MON 대신 임시 테이블
CREATE GLOBAL TEMPORARY TABLE GT_SLE_MON (
  SHP_NO  NUMBER(5),
  ITM_NO  VARCHAR2(10),
  SLE_QTY NUMBER,
  SLE_AMT NUMBER
);

PROCEDURE P_SHP_ITM_MON(p_ym VARCHAR2) IS            -- p_ym = '202311'
  v_rows NUMBER;
BEGIN
  -- ⓪ 그 달 요약을 비운다
  DELETE FROM CF11_SHP_MON_SUM WHERE SLE_YM = p_ym;
  COMMIT;                                            -- 사내 배치 표준: 단계가 끝날 때마다 커밋

  -- ① 매장 × 상품 합계를 작업 테이블에
  INSERT INTO GT_SLE_MON (SHP_NO, ITM_NO, SLE_QTY, SLE_AMT)
  SELECT h.SHP_NO, d.ITM_NO, SUM(d.SLE_QTY), SUM(d.SLE_QTY * d.SLE_UPRC)
    FROM CF10 h, CF11 d
   WHERE d.SLE_NO = h.SLE_NO
     AND h.SLE_DT BETWEEN p_ym || '01' AND p_ym || '31'
   GROUP BY h.SHP_NO, d.ITM_NO;
  v_rows := SQL%ROWCOUNT;
  INSERT INTO T_BAT_LOG (JOB_NM, STEP, ROWS_DONE, DONE_AT) VALUES ('SHP_ITM_MON', '1', v_rows, SYSDATE);
  COMMIT;

  -- ② 상품 분류를 붙여 요약 테이블에
  INSERT INTO CF11_SHP_MON_SUM (SLE_YM, SHP_NO, ITM_NO, CTG_NO, SLE_QTY, SLE_AMT)
  SELECT p_ym, g.SHP_NO, g.ITM_NO, i.CTG_NO, g.SLE_QTY, g.SLE_AMT
    FROM GT_SLE_MON g, CF01 i
   WHERE i.ITM_NO = g.ITM_NO;
  v_rows := SQL%ROWCOUNT;
  INSERT INTO T_BAT_LOG (JOB_NM, STEP, ROWS_DONE, DONE_AT) VALUES ('SHP_ITM_MON', '2', v_rows, SYSDATE);
  COMMIT;
END;

실행계획 학습용 예시

xplan

[배치 기록 T_BAT_LOG — 12월 1일 · 11월분]
  STEP   ROWS_DONE   DONE_AT
  1            531   02:10:03
  2              0   02:10:03
  스케줄러 상태: SUCCEEDED

[② 문장 — DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST')]
------------------------------------------------------------------------------------------------
| Id  | Operation                | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |                  |      1 |        |      0 |00:00:00.01 |       2 |
|   1 |  LOAD TABLE CONVENTIONAL | CF11_SHP_MON_SUM |      1 |        |      0 |00:00:00.01 |       2 |
|*  2 |   HASH JOIN              |                  |      1 |      1 |      0 |00:00:00.01 |       0 |
|   3 |    TABLE ACCESS FULL     | GT_SLE_MON       |      1 |      1 |      0 |00:00:00.01 |       0 |
|   4 |    TABLE ACCESS FULL     | CF01             |      0 |    109 |      0 |00:00:00.00 |       0 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("I"."ITM_NO"="G"."ITM_NO")
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

[딕셔너리]
  SELECT table_name, temporary, duration FROM user_tables WHERE table_name = 'GT_SLE_MON';
  GT_SLE_MON   Y   SYS$TRANSACTION

문제

①은 531행을 넣었는데 ②가 0행을 읽은 이유를 DURATION과 원본의 커밋 위치로 설명하시오. 에러 없이 SUCCEEDED로 끝난 이유도 쓰시오.
같은 결과(11월 531행)를 내도록 프로시저를 고치시오. 임시 테이블을 ON COMMIT PRESERVE ROWS로 바꾸는 방법과 비교하고, 실패한 뒤 다시 돌릴 때 어디서부터 돌려야 하는지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 임시 테이블 DDL 은 그대로 (ON COMMIT DELETE ROWS · 이 트랜잭션 안에서만 쓰는 작업장)
PROCEDURE P_SHP_ITM_MON(p_ym VARCHAR2) IS
  v_rows NUMBER;
  v_sum  NUMBER;
BEGIN
  -- ⓪ ① ② 를 한 트랜잭션으로 — 임시 테이블을 채우고 쓰는 사이에 COMMIT 을 두지 않는다
  DELETE FROM CF11_SHP_MON_SUM WHERE SLE_YM = p_ym;

  INSERT INTO GT_SLE_MON (SHP_NO, ITM_NO, SLE_QTY, SLE_AMT)
  SELECT h.SHP_NO, d.ITM_NO, SUM(d.SLE_QTY), SUM(d.SLE_QTY * d.SLE_UPRC)
    FROM CF10 h, CF11 d
   WHERE d.SLE_NO = h.SLE_NO
     AND h.SLE_DT BETWEEN p_ym || '01' AND p_ym || '31'
   GROUP BY h.SHP_NO, d.ITM_NO;
  v_rows := SQL%ROWCOUNT;
  INSERT INTO T_BAT_LOG (JOB_NM, STEP, ROWS_DONE, DONE_AT) VALUES ('SHP_ITM_MON', '1', v_rows, SYSDATE);

  INSERT INTO CF11_SHP_MON_SUM (SLE_YM, SHP_NO, ITM_NO, CTG_NO, SLE_QTY, SLE_AMT)
  SELECT p_ym, g.SHP_NO, g.ITM_NO, i.CTG_NO, g.SLE_QTY, g.SLE_AMT
    FROM GT_SLE_MON g, CF01 i
   WHERE i.ITM_NO = g.ITM_NO;
  v_sum := SQL%ROWCOUNT;

  IF v_sum = 0 OR v_sum <> v_rows THEN                     -- 0 행 성공을 성공으로 보지 않는다
    ROLLBACK;
    RAISE_APPLICATION_ERROR(-20001, p_ym || ' 요약 적재 건수 이상: 작업 ' || v_rows || ' / 요약 ' || v_sum);
  END IF;
  INSERT INTO T_BAT_LOG (JOB_NM, STEP, ROWS_DONE, DONE_AT) VALUES ('SHP_ITM_MON', '2', v_rows, SYSDATE);

  COMMIT;            -- 한 번. 이 커밋에서 임시 테이블의 행도 지워진다 (따로 비울 일이 없다)
EXCEPTION
  WHEN OTHERS THEN ROLLBACK; RAISE;
END;

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

xplan

[배치 기록 — 고친 뒤 11월분 다시 실행]
  STEP   ROWS_DONE
  1            531
  2            531        요약 CF11_SHP_MON_SUM 11월 531행 · 커밋 1번

[② 문장]
------------------------------------------------------------------------------------------------
| Id  | Operation                | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |                  |      1 |        |      0 |00:00:00.01 |      21 |
|   1 |  LOAD TABLE CONVENTIONAL | CF11_SHP_MON_SUM |      1 |        |      0 |00:00:00.01 |      21 |
|*  2 |   HASH JOIN              |                  |      1 |    531 |    531 |00:00:00.01 |       9 |
|   3 |    TABLE ACCESS FULL     | GT_SLE_MON       |      1 |    531 |    531 |00:00:00.01 |       4 |
|   4 |    TABLE ACCESS FULL     | CF01             |      1 |    109 |    109 |00:00:00.01 |       5 |
------------------------------------------------------------------------------------------------
   2 - access("I"."ITM_NO"="G"."ITM_NO")

모범 답안 서술

CREATE GLOBAL TEMPORARY TABLE에 ON COMMIT 절을 적지 않으면 기본값 ON COMMIT DELETE ROWS가 된다. 딕셔너리의 DURATION이 SYS$TRANSACTION인 것이 그 표시다. 이 임시 테이블의 행은 넣은 트랜잭션이 끝나는 순간, 곧 커밋하는 순간 지워진다. 원본은 ①에서 531행을 넣은 뒤 사내 표준대로 COMMIT했고, 그 커밋에 531행이 모두 사라졌다. 그래서 ②의 3번 줄 A-Rows가 0이고, 빌드 쪽이 비었으니 4번 줄 CF01은 아예 읽지도 않았다(Starts 0). 에러가 없었던 것은 빈 테이블을 읽은 INSERT … SELECT가 실패가 아니라 0행 성공이기 때문이다. 배치 기록에도 ② 0행이 그대로 적혔지만 아무도 보지 않았고, 스케줄러는 SUCCEEDED를 찍었다. 더구나 ⓪의 DELETE를 따로 커밋했으므로, 이미 있던 달을 다시 돌렸다면 옛 요약까지 지운 채 빈 달이 남았을 것이다. 고친 프로시저는 ⓪①②를 한 트랜잭션으로 묶어 커밋을 끝에 한 번만 한다. 임시 테이블을 채우고 쓰는 사이에 커밋이 없으니 ②가 531행을 읽고, 끝의 커밋에서 임시 테이블이 저절로 비워진다. ②의 처리 건수가 0이거나 ①과 다르면 ROLLBACK한 뒤 RAISE로 멈춘다. 0행 성공을 성공으로 보지 않는 확인이다. ON COMMIT PRESERVE ROWS로 다시 만드는 방법도 결과는 같다. 행이 커밋에는 남고 세션이 끝날 때 사라진다. 대신 같은 세션에서 다시 돌리면 지난 행이 쌓이므로 시작할 때 TRUNCATE(자기 세션의 행만 비운다)나 DELETE로 비워야 하고, 커밋으로 나눈 단계 사이에 실패하면 반쯤 반영이 남는다. 다시 돌릴 때는 어느 방법이든 ①부터 돌린다. 임시 테이블의 행은 새 세션에는 없고, 한 트랜잭션으로 묶은 판은 실패하면 ⓪까지 함께 되돌아가기 때문이다. 이 배치는 0.4초짜리라 단계마다 커밋해서 재시작 지점을 둘 이유가 없다.

해설

표준과 도구가 부딪힌 자리: "단계마다 커밋"은 영구 작업 테이블을 쓸 때의 표준이다. DELETE ROWS 임시 테이블은 '한 트랜잭션 안의 작업장'이라서 둘을 함께 쓰면 단계 사이의 결과가 커밋에 버려진다. 임시 테이블로 바꿀 때는 커밋 위치를 같이 본다(교재 13화 「커밋 주기 기준」 #1).
TRUNCATE도 커밋이다: 단계 사이에 EXECUTE IMMEDIATE 'TRUNCATE TABLE …' 같은 DDL을 두면 DDL 앞뒤의 암시적 커밋으로 똑같이 비워진다. 프로시저 안에서 부르는 로그 프로시저가 COMMIT하는 경우도 같다.
다른 정답: 이 정도 크기면 임시 테이블 없이 INSERT INTO CF11_SHP_MON_SUM … SELECT … FROM CF10, CF11, CF01 … GROUP BY 한 문장이 가장 단순하다. 임시 테이블은 같은 중간 결과를 두 번 이상 읽을 때 값을 한다.
임시 테이블의 통계: 이 판의 E-Rows 1은 동적 샘플링이 빈 임시 테이블을 본 값이다. 채운 뒤에 읽는 문장은 하드 파싱 때 표본을 다시 보므로 대개 맞지만, 임시 테이블 통계와 동적 샘플링의 함정은 OPT 단원에서 따로 다룬다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황