루프를 한 문장으로 바꾸자 12월 주문상품이 1,206건 모자란다

중급INSERT버거435 / 700

상황

버거원 본사는 매달 초 지난달 주문상품을 DW 이관용 스테이징(DW_ORD_ITEM_M)으로 옮긴다. 지금 배치는 주문상품을 한 건씩 읽어 상품명과 상품 유형을 찾아 붙인 뒤 한 건씩 넣는데, 12월분에 1.6초가 걸렸다. 개발자가 INSERT … SELECT 한 문장으로 바꾸자 0.2초로 줄었다. 그런데 DW 쪽 건수 대조에서 1,206건이 모자랐다. 에러는 하나도 없었다.

테이블과 인덱스

ORD_ITEM: 2,472,717행 · 55,265블록 · ORD_ITEM_X01 (ORD_DT, ORD_HMS) · 2013년 12월분 106,342행
ITEM: 92행 · 5블록 · PK ITEM_ID. 2013년 11월 단종 정리 때 상품 3개를 지웠다(지우기 전 95개). 그 3개가 일부 매장 POS에 남아 12월에도 1,206행이 팔렸다.
DW_ORD_ITEM_M(이 문제용 테이블): 한 달 치만 담는 스테이징이다. 배치를 시작할 때 비운다(TRUNCATE). 인덱스는 없고, DW가 배치가 끝난 뒤에 읽는다.
DW의 규칙은 "상품 마스터에 없는 상품은 상품명 (단종), 유형 ZZ로 받는다"이다. DB는 NOARCHIVELOG다.

원본 SQL

sql

-- 지금 배치 (1.6초)
DECLARE
  v_nm ITEM.ITEM_NM%TYPE;
  v_tp ITEM.ITEM_TYPE_CD%TYPE;
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE DW_ORD_ITEM_M';
  FOR r IN (SELECT ORD_NO, ITEM_ID, ORD_ITEM_QTY, ORD_DT
              FROM ORD_ITEM
             WHERE ORD_DT BETWEEN '20131201' AND '20131231') LOOP
    BEGIN
      SELECT ITEM_NM, ITEM_TYPE_CD INTO v_nm, v_tp
        FROM ITEM WHERE ITEM_ID = r.ITEM_ID;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN v_nm := '(단종)'; v_tp := 'ZZ';
    END;
    INSERT INTO DW_ORD_ITEM_M (ORD_NO, ITEM_ID, ITEM_NM, ITEM_TYPE_CD, ORD_ITEM_QTY, ORD_DT)
    VALUES (r.ORD_NO, r.ITEM_ID, v_nm, v_tp, r.ORD_ITEM_QTY, r.ORD_DT);
  END LOOP;
  COMMIT;
END;

-- 개발자가 바꾼 판 (0.2초 · 1,206건 모자람)
INSERT INTO DW_ORD_ITEM_M (ORD_NO, ITEM_ID, ITEM_NM, ITEM_TYPE_CD, ORD_ITEM_QTY, ORD_DT)
SELECT i.ORD_NO, i.ITEM_ID, t.ITEM_NM, t.ITEM_TYPE_CD, i.ORD_ITEM_QTY, i.ORD_DT
  FROM ORD_ITEM i, ITEM t
 WHERE t.ITEM_ID = i.ITEM_ID
   AND i.ORD_DT BETWEEN '20131201' AND '20131231';

실행계획 학습용 예시

xplan

[지금 배치 — 세션 통계]
  경과 시간                 1.58 ~ 1.66 초
  recursive calls           213,104
  execute count             212,698   (상품 SELECT INTO 106,342 · INSERT 106,342)
  session logical reads     약 330,400
  redo size                 약 30.2 MB
  적재 행                    106,342

[바꾼 판 — DISPLAY_CURSOR (ALLSTATS LAST)]
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                       |               |      1 |        |      0 |00:00:00.21 |    4930 |          |
|   1 |  LOAD TABLE CONVENTIONAL               | DW_ORD_ITEM_M |      1 |        |      0 |00:00:00.21 |    4930 |          |
|*  2 |   HASH JOIN                            |               |      1 |    106K|    105K|00:00:00.06 |    2688 | 1376K (0)|
|   3 |    TABLE ACCESS FULL                   | ITEM          |      1 |     92 |     92 |00:00:00.01 |       5 |          |
|   4 |    TABLE ACCESS BY INDEX ROWID BATCHED | ORD_ITEM      |      1 |    106K|    106K|00:00:00.03 |    2683 |          |
|*  5 |     INDEX RANGE SCAN                   | ORD_ITEM_X01  |      1 |    106K|    106K|00:00:00.01 |     381 |          |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T"."ITEM_ID"="I"."ITEM_ID")
   5 - access("I"."ORD_DT">='20131201' AND "I"."ORD_DT"<='20131231')

[DW 건수 대조]  원천 ORD_ITEM 12월 106,342 · 스테이징 105,136 · 차이 1,206 (지운 상품 3개)

문제

바꾼 판에서 1,206건이 어디로 사라졌는지, 지금 배치에서는 그 행이 왜 남았는지 설명하시오.
지금 배치의 일량이 어디서 나오는지 세션 통계로 설명하시오.
빠르면서 결과도 지금 배치와 같은 한 문장으로 다시 작성하고, 끝났을 때 무엇을 확인할지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

TRUNCATE TABLE DW_ORD_ITEM_M;

-- 한 문장 · 직접 경로 (매달 비우는 스테이징 · 적재 중 읽는 세션 없음)
INSERT /*+ APPEND */ INTO DW_ORD_ITEM_M (ORD_NO, ITEM_ID, ITEM_NM, ITEM_TYPE_CD, ORD_ITEM_QTY, ORD_DT)
SELECT i.ORD_NO, i.ITEM_ID,
       CASE WHEN t.ITEM_ID IS NULL THEN '(단종)' ELSE t.ITEM_NM      END,   -- 루프의 NO_DATA_FOUND 와 같은 값
       CASE WHEN t.ITEM_ID IS NULL THEN 'ZZ'     ELSE t.ITEM_TYPE_CD END,
       i.ORD_ITEM_QTY, i.ORD_DT
  FROM ORD_ITEM i
  LEFT OUTER JOIN ITEM t ON t.ITEM_ID = i.ITEM_ID          -- 마스터에 없는 상품도 남긴다
 WHERE i.ORD_DT BETWEEN '20131201' AND '20131231';
COMMIT;

-- 끝나면 건수부터 대조한다 (원천 = 적재)
SELECT (SELECT COUNT(*) FROM ORD_ITEM WHERE ORD_DT BETWEEN '20131201' AND '20131231') AS SRC_CNT,
       (SELECT COUNT(*) FROM DW_ORD_ITEM_M)                                          AS STG_CNT
  FROM dual;

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

xplan

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                       |               |      1 |        |      0 |00:00:00.12 |    2905 |          |
|   1 |  LOAD AS SELECT                        | DW_ORD_ITEM_M |      1 |        |      0 |00:00:00.12 |    2905 |          |
|   2 |   OPTIMIZER STATISTICS GATHERING       |               |      1 |    106K|    106K|00:00:00.06 |    2688 |          |
|*  3 |    HASH JOIN RIGHT OUTER               |               |      1 |    106K|    106K|00:00:00.05 |    2688 | 1376K (0)|
|   4 |     TABLE ACCESS FULL                  | ITEM          |      1 |     92 |     92 |00:00:00.01 |       5 |          |
|   5 |     TABLE ACCESS BY INDEX ROWID BATCHED| ORD_ITEM      |      1 |    106K|    106K|00:00:00.03 |    2683 |          |
|*  6 |      INDEX RANGE SCAN                  | ORD_ITEM_X01  |      1 |    106K|    106K|00:00:00.01 |     381 |          |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T"."ITEM_ID"(+)="I"."ITEM_ID")
   6 - access("I"."ORD_DT">='20131201' AND "I"."ORD_DT"<='20131231')

[대조]  원천 106,342 = 적재 106,342 (그중 `(단종)` 1,206) · 경과 0.12초 · redo 약 0.1 MB

모범 답안 서술

개발자가 바꾼 판은 ITEM과 이너 조인을 한다. 그래서 조인 상대가 없는 주문상품, 곧 단종 정리 때 지운 상품 3개의 12월 주문상품 1,206행이 조용히 빠졌다(실행계획 2번 줄 A-Rows 105K, 원천 4번 줄 106K). 지금 배치는 상품을 못 찾으면 NO_DATA_FOUND 예외에서 (단종)과 ZZ를 넣고 그 행을 남겼다. 예외 처리가 사실상 '아우터 조인 + 기본값'이었던 셈이다. 에러가 나지 않으므로 건수를 대조하지 않으면 알 수 없다. 지금 배치의 일량은 주문상품 106,342행마다 SQL로 두 번(상품 SELECT INTO와 INSERT) 건너가는 데서 나온다. recursive calls가 213,104이고 논리 읽기가 약 33만, redo가 약 30MB다. 고친 판은 INSERT … SELECT 한 문장이되 ITEM을 LEFT OUTER JOIN으로 붙이고, 짝이 없는 행은 CASE WHEN t.ITEM_ID IS NULL로 지금 배치와 같은 기본값을 넣는다. 스테이징은 매달 비우고 적재 중에 읽는 세션이 없으므로 /*+ APPEND */로 직접 경로 적재를 한다. 경과 시간은 1.6초 → 0.12초, Buffers는 약 33만 → 2,905로 준다. 끝나면 원천 12월 건수와 적재 건수를 대조해 106,342 = 106,342인지 확인한다.

해설

함정: 루프 안의 SELECT … INTO와 WHEN NO_DATA_FOUND는 SQL로 옮기면 아우터 조인과 기본값이다. 예외 처리를 지우고 이너 조인으로 바꾸면 빨라지면서 행이 조용히 빠진다. 한 문장으로 바꿀 때는 루프의 예외 처리부터 SQL로 번역한다.
NVL(t.ITEM_NM, '(단종)')도 답이 된다. 다만 마스터에 있는 상품의 상품명이 NULL이라면 그 행도 (단종)이 되므로, '짝이 없다'를 정확히 따지려면 조인 키의 NULL 여부(t.ITEM_ID IS NULL)를 본다.
스칼라 서브쿼리((SELECT ITEM_NM FROM ITEM WHERE ITEM_ID = i.ITEM_ID))도 못 찾으면 NULL을 돌려주므로 결과가 같다. 상품이 92개라 캐시가 잘 듣지만, 칸이 둘이면 서브쿼리도 둘이 된다.
테스트 DB에 마스터와 짝이 맞는 데이터만 있으면 이 차이가 보이지 않는다. 대조는 원천 기준으로 한다(교재 08화 「정합성 회귀 체크리스트」 E: 1 쪽에 없는 키).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황