협력사 월 납품 실적 보고 체인 여섯 단계가 결과 617행을 만드는 데 12.7초와 redo 35MB를 쓴다

실전INSERT유통493 / 700

상황

본사 레거시 유통의 월 마감에는 외주 개발사가 2018년에 만든 「협력사 월 납품 실적 보고」 체인이 있다. 판매상품을 작업 테이블에 옮기고, 행마다 협력사와 분류를 채우고, 자체 상품을 지우고, 묶고, 이름을 붙여 리포트 테이블에 넣는 여섯 단계이고 단계마다 커밋한다. 결과는 617행이다. 월 마감 창을 줄이는 작업을 맡은 오과장이 물었다. "단계마다 하나씩 튜닝하면 될까요, 아예 다시 짤까요? 다시 짜면 숫자가 지금과 똑같이 나와야 합니다. 협력사들이 이 숫자로 납품 대금을 맞춰 봐요."

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · 판매 날짜순 적재 · 2023년 6월 20,953건 · IX_CF10_SLE_DT (SLE_DT) · SHP_NO 매장 5곳
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · 6월분 45,912행
CF01 상품: 109행 · 5블록 · ITM_NO · CTG_NO · PTNR_NO(협력사, 자체 상품은 'P000') · 6월에 팔린 상품 중 2개는 단종 정리로 마스터에서 지웠다(그 판매상품 87행).
CF02 카테고리: 18행 · 4블록 · CF07 매장: 6행 · 4블록
W_SLE_1, W_SLE_2: 이 체인만 쓰는 영구 작업 테이블(LOGGING, 인덱스 없음) · RPT_PTNR_MON: 리포트 테이블(이 문제용), 협력사 화면이 읽는다.
운영은 ARCHIVELOG다.

원본 SQL

sql

PROCEDURE P_PTNR_MON(p_ym VARCHAR2) IS            -- p_ym = '202306'
  v_ptnr CF01.PTNR_NO%TYPE;
  v_ctg  CF01.CTG_NO%TYPE;
  v_cnm  CF02.CTG_NM%TYPE;
  v_snm  CF07.SHP_NM%TYPE;
BEGIN
  -- ① 그 달 판매상품을 작업 테이블로
  INSERT INTO W_SLE_1 (SLE_NO, SHP_NO, ITM_NO, SLE_QTY, SLE_UPRC)
  SELECT h.SLE_NO, h.SHP_NO, d.ITM_NO, 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';
  COMMIT;

  -- ② 행마다 협력사와 분류를 채운다
  FOR r IN (SELECT ROWID AS rid, ITM_NO FROM W_SLE_1) LOOP
    BEGIN
      SELECT PTNR_NO, CTG_NO INTO v_ptnr, v_ctg FROM CF01 WHERE ITM_NO = r.ITM_NO;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN v_ptnr := NULL; v_ctg := NULL;      -- 마스터에 없는 상품은 비워 둔다
    END;
    UPDATE W_SLE_1 SET PTNR_NO = v_ptnr, CTG_NO = v_ctg WHERE ROWID = r.rid;
  END LOOP;
  COMMIT;

  -- ③ 자체 상품은 보고 대상이 아니다
  DELETE FROM W_SLE_1 WHERE PTNR_NO = 'P000';
  COMMIT;

  -- ④ 협력사 · 분류 · 매장으로 묶는다
  INSERT INTO W_SLE_2 (PTNR_NO, CTG_NO, SHP_NO, SLE_QTY, SLE_AMT)
  SELECT PTNR_NO, CTG_NO, SHP_NO, SUM(SLE_QTY), SUM(SLE_QTY * SLE_UPRC)
    FROM W_SLE_1
   GROUP BY PTNR_NO, CTG_NO, SHP_NO;
  COMMIT;

  -- ⑤ 이름을 붙여 리포트에
  FOR r IN (SELECT * FROM W_SLE_2) LOOP
    BEGIN
      SELECT CTG_NM INTO v_cnm FROM CF02 WHERE CTG_NO = r.CTG_NO;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN v_cnm := '(미분류)';
    END;
    SELECT SHP_NM INTO v_snm FROM CF07 WHERE SHP_NO = r.SHP_NO;
    INSERT INTO RPT_PTNR_MON (SLE_YM, PTNR_NO, CTG_NO, CTG_NM, SHP_NO, SHP_NM, SLE_QTY, SLE_AMT)
    VALUES (p_ym, r.PTNR_NO, r.CTG_NO, v_cnm, r.SHP_NO, v_snm, r.SLE_QTY, r.SLE_AMT);
  END LOOP;
  COMMIT;

  -- ⑥ 작업 테이블 비우기
  DELETE FROM W_SLE_1;
  DELETE FROM W_SLE_2;
  COMMIT;
END;

실행계획 학습용 예시

xplan

[단계별 — 2023년 6월분 · 운영 · 단독 실행]
  단계                            처리 행   경과    redo      recursive calls   비고
  ① W_SLE_1 적재                   45,912   0.9초    6.1 MB              12   CF10 · CF11 해시 조인 12,787블록
  ② 협력사 · 분류 채우기             45,912   9.8초   14.2 MB          91,830   행마다 CF01 SELECT INTO + ROWID UPDATE
  ③ 자체 상품 지우기                  6,017   0.4초    2.9 MB               6
  ④ W_SLE_2 집계                       617   0.2초    0.1 MB               4
  ⑤ 리포트 적재                        617   0.3초    0.2 MB           1,858   행마다 CF02 · CF07 SELECT INTO + INSERT
  ⑥ 작업 테이블 비우기               40,512   1.1초   11.6 MB               4   W_SLE_1 39,895 + W_SLE_2 617
  합계                                     12.7초   35.1 MB          93,714   커밋 6번

[6월 리포트에 든 것]
  617행 · 그중 PTNR_NO 가 빈 행 3행 (마스터에 없는 상품 87행이 '(미분류)'로 묶인 것)

문제

12.7초와 redo 35.1MB가 어디서 나오는지 단계별 표로 설명하고, 작업 테이블 두 개가 각각 몇 번 쓰이고 몇 번 읽히는지 쓰시오.
체인 전체를 한 문장으로 다시 작성하시오. 결과가 지금과 똑같으려면 ②, ③, ⑤의 어떤 동작을 빠짐없이 옮겨야 하는지 쓰시오.
작업 테이블을 남기는 편이 나은 경우는 언제인지 쓰고, 바꾸기 전에 결과가 같은지 확인하는 방법을 SQL로 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

PROCEDURE P_PTNR_MON(p_ym VARCHAR2) IS
BEGIN
  DELETE FROM RPT_PTNR_MON WHERE SLE_YM = p_ym;       -- 다시 돌려도 그 달은 한 벌

  INSERT INTO RPT_PTNR_MON (SLE_YM, PTNR_NO, CTG_NO, CTG_NM, SHP_NO, SHP_NM, SLE_QTY, SLE_AMT)
  SELECT p_ym, i.PTNR_NO, i.CTG_NO, NVL(g.CTG_NM, '(미분류)'), h.SHP_NO, s.SHP_NM,
         SUM(d.SLE_QTY), SUM(d.SLE_QTY * d.SLE_UPRC)
    FROM CF10 h
    JOIN CF11 d      ON d.SLE_NO = h.SLE_NO
    JOIN CF07 s      ON s.SHP_NO = h.SHP_NO
    LEFT JOIN CF01 i ON i.ITM_NO = d.ITM_NO           -- ② 마스터에 없는 상품도 남긴다 (NO_DATA_FOUND → NULL)
    LEFT JOIN CF02 g ON g.CTG_NO = i.CTG_NO           -- ⑤ 분류가 없으면 '(미분류)'
   WHERE h.SLE_DT BETWEEN p_ym || '01' AND p_ym || '31'
     AND LNNVL(i.PTNR_NO = 'P000')                    -- ③ 은 'P000' 만 지웠다: 협력사가 빈 행은 남는다
   GROUP BY i.PTNR_NO, i.CTG_NO, g.CTG_NM, h.SHP_NO, s.SHP_NM;

  COMMIT;                                             -- 한 번 · 지우기와 넣기가 함께 확정된다
END;

-- 바꾸기 전 검증: 옛 체인 결과를 RPT_PTNR_MON_OLD 에 떠 두고 같은 달을 새 문장으로 만든 뒤 양쪽으로 (둘 다 0 행)
SELECT PTNR_NO, CTG_NO, CTG_NM, SHP_NO, SHP_NM, SLE_QTY, SLE_AMT FROM RPT_PTNR_MON     WHERE SLE_YM = '202306'
MINUS
SELECT PTNR_NO, CTG_NO, CTG_NM, SHP_NO, SHP_NM, SLE_QTY, SLE_AMT FROM RPT_PTNR_MON_OLD WHERE SLE_YM = '202306';

SELECT PTNR_NO, CTG_NO, CTG_NM, SHP_NO, SHP_NM, SLE_QTY, SLE_AMT FROM RPT_PTNR_MON_OLD WHERE SLE_YM = '202306'
MINUS
SELECT PTNR_NO, CTG_NO, CTG_NM, SHP_NO, SHP_NM, SLE_QTY, SLE_AMT FROM RPT_PTNR_MON     WHERE SLE_YM = '202306';

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

xplan

-------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT          |              |      1 |        |      0 |00:00:00.31 |   12825 |
|   1 |  LOAD TABLE CONVENTIONAL  | RPT_PTNR_MON |      1 |        |      0 |00:00:00.31 |   12825 |
|   2 |   HASH GROUP BY           |              |      1 |    640 |    617 |00:00:00.30 |   12806 |
|*  3 |    HASH JOIN RIGHT OUTER  |              |      1 |  45912 |  39895 |00:00:00.27 |   12806 |
|   4 |     TABLE ACCESS FULL     | CF02         |      1 |     18 |     18 |00:00:00.01 |       4 |
|*  5 |     FILTER                |              |      1 |        |  39895 |00:00:00.25 |   12802 |
|*  6 |      HASH JOIN RIGHT OUTER|              |      1 |  45912 |  45912 |00:00:00.22 |   12802 |
|   7 |       TABLE ACCESS FULL   | CF01         |      1 |    109 |    109 |00:00:00.01 |       5 |
|*  8 |       HASH JOIN           |              |      1 |  45912 |  45912 |00:00:00.18 |   12797 |
|   9 |        TABLE ACCESS FULL  | CF07         |      1 |      6 |      6 |00:00:00.01 |       4 |
|* 10 |        HASH JOIN          |              |      1 |  45912 |  45912 |00:00:00.15 |   12793 |
|* 11 |         TABLE ACCESS FULL | CF10         |      1 |  20953 |  20953 |00:00:00.03 |    4586 |
|  12 |         TABLE ACCESS FULL | CF11         |      1 |   1096K|   1096K|00:00:00.09 |    8207 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("G"."CTG_NO"(+)="I"."CTG_NO")
   5 - filter(LNNVL("I"."PTNR_NO"='P000'))
   6 - access("I"."ITM_NO"(+)="D"."ITM_NO")
   8 - access("S"."SHP_NO"="H"."SHP_NO")
  10 - access("D"."SLE_NO"="H"."SLE_NO")
  11 - filter(("H"."SLE_DT">=:P_YM||'01' AND "H"."SLE_DT"<=:P_YM||'31'))

  12.7초 → 0.31초 · redo 35.1 MB → 약 0.2 MB · recursive calls 93,714 → 수십 · 커밋 6번 → 1번
  검증: 6월 MINUS 양쪽 0행 · 617행 · 협력사가 빈 3행도 그대로

모범 답안 서술

단계별 표를 보면 시간은 ②가, redo는 작업 테이블이 만든다. ②는 W_SLE_1의 45,912행마다 CF01을 SELECT INTO로 한 번, ROWID UPDATE로 한 번, 모두 두 번 SQL로 건너가 recursive calls 91,830과 9.8초를 쓴다. ⑤도 617행마다 SQL 세 번이다. redo 35.1MB 가운데 리포트 617행에 해당하는 몫은 0.2MB뿐이고, 나머지는 작업 테이블에 45,912행을 쓰고(①), 다시 고치고(②), 일부를 지우고(③), 끝에 다 지우는(⑥) 기록이다. W_SLE_1은 한 번 채워 ②③④에서 차례로 고치고 읽은 뒤 버리고, W_SLE_2는 ④에서 한 번 쓰고 ⑤에서 한 번 읽고 버린다. 어느 작업 테이블도 같은 중간 결과를 여러 단계가 공유하거나 재시작 지점으로 쓰이지 않는다. 단계마다 튜닝해도 '작업 테이블에 쓰고 지우는 일'은 남으므로 체인 전체를 다시 짠다. 다시 짠 판은 원장에서 바로 조인하고 묶어 리포트에 넣는 한 문장이다. CF10과 CF11을 한 번씩 읽어 해시 조인하고(12,793블록), 작은 마스터 셋을 붙인 뒤 HASH GROUP BY로 617행을 만든다. 결과가 지금과 똑같으려면 세 동작을 옮겨야 한다. 첫째, ②는 마스터에 없는 상품을 NO_DATA_FOUND에서 NULL로 두고 행을 남겼다. 그래서 CF01은 LEFT JOIN이어야 하고, 이너 조인으로 쓰면 단종 상품 87행이 에러 없이 사라진다. 둘째, ③은 PTNR_NO = 'P000'인 행만 지웠으므로 협력사가 빈 행은 남았다. 한 문장에서 i.PTNR_NO <> 'P000'이라고 쓰면 NULL과의 비교가 UNKNOWN이 되어 그 행을 걸러 낸다. 그래서 LNNVL(i.PTNR_NO = 'P000')로 쓴다. 셋째, ⑤는 분류가 없으면 '(미분류)'를 넣었으므로 CF02도 LEFT JOIN하고 NVL(g.CTG_NM, '(미분류)')로 채운다. 숫자로는 12.7초 → 0.31초, redo 35.1MB → 약 0.2MB, recursive calls 93,714 → 수십, 커밋 6번 → 1번이다. 그 달 리포트를 지우고 넣는 일을 한 트랜잭션으로 묶었으므로 다시 돌려도 한 벌이고, 실패하면 옛 리포트가 그대로 남는다. 작업 테이블은 같은 중간 결과를 여러 단계나 여러 리포트가 되풀이해 읽을 때, 또는 중간 결과를 남겨 그다음 단계부터 다시 시작해야 할 만큼 앞 단계가 비쌀 때만 둔다. 이 체인처럼 한 번 쓰고 한 번 읽는 작업 테이블은 redo와 왕복만 늘린다. 바꾸기 전에는 옛 체인의 6월 결과를 떠 두고 새 문장으로 같은 달을 만든 뒤 MINUS를 양쪽으로 돌려 둘 다 0행인지 본다. 한쪽만 보면 새 문장이 행을 덜 만든 경우를 놓친다. 협력사가 빈 3행처럼 경계에 있는 행이 양쪽에 같이 있는지도 따로 확인한다.

해설

단계마다 고치면: ②를 MERGE 한 문장으로, ⑤를 조인으로, ⑥을 TRUNCATE로 바꾸면 3초 안쪽까지는 준다. 하지만 작업 테이블에 4만 6천 행을 쓰고 고치는 redo는 남는다. 여러 단계 배치는 단계가 아니라 데이터의 흐름(누가 무엇을 몇 번 쓰고 읽는가)을 먼저 그린다.
NULL과 부정 조건: 절차형 코드의 '이 값이면 지운다'를 집합형의 '이 값이 아니면 남긴다'로 뒤집을 때 NULL 행이 빠진다. <>, NOT IN은 NULL을 남기지 않는다. 뒤집은 조건은 늘 NULL 행으로 시험한다(DML-019, DML-025).
작업 테이블을 남겨야 하는 체인: 같은 달의 협력사별 판매를 리포트 다섯 개가 함께 쓴다면 그 중간 결과는 한 번 만들어 두는 편이 낫다(DML-061). 그때도 영구 테이블을 DELETE로 비우는 대신 임시 테이블이나 TRUNCATE를 쓴다(DML-058, DML-032).
검증의 기준: 옛 체인의 결과가 틀렸을 수도 있다. 옛 결과와 같다는 것은 '바꾸면서 새로 틀리지 않았다'는 뜻이고, 맞는지는 원장 합계(6월 판매 금액에서 자체 상품을 뺀 값)와 따로 대조한다(교재 08화 체크리스트 A).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황