JOIN-046

재고 테이블에 원재료가 들어오기 시작한 달부터 월말 수불 점검 배치가 ORA-01400으로 멈춘다

실전INSERT유통196 / 700

상황

구매팀의 「월말 수불 점검」 배치는 품목마다 그달 판매 수량, 발주 수량, 월말 재고를 한 줄로 모아 CF_ITM_MON_CHK에 넣는다. 셋 중 어느 하나만 있는 품목도 빠지면 안 되어서 FULL OUTER JOIN 두 개로 이었다. 2023년 12월부터 매장 재고(CF18)에 원재료도 올리기 시작했는데, 그달 배치가 ORA-01400: cannot insert NULL into ("PUP"."CF_ITM_MON_CHK"."ITM_NO")로 멈췄다. 오과장은 SELECT만 따로 돌려 보니 129줄이 나왔다며 "세 집합을 합쳐도 품목은 109개인데 왜 129줄인지, 고치는 방법을 비교해서 가져와"라고 했다.

테이블과 인덱스

CF01 상품: 109행 · 5블록 · 판매상품 88 · 추가상품 1(샷추가) · 원재료 20
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · SLE_NO 앞 8자리가 판매일자 · 2023년 12월 46,322줄(판매상품 88개)
CF12 발주: 734행 · 10블록 · ORD_DT VARCHAR2(8) · 12월 62건 / CF13 발주상품: 7,102행 · 57블록 · 12월 604줄(원재료 20개, 판매상품 27개)
CF18 일별상품재고: 408행 · 6블록 · PK (SHP_NO, ITM_NO, INV_BS_DT) · 2023-12-31 기준 136행(68개 품목: 원재료 20, 판매상품 47, 마스터에서 지운 단종 품목 1)
CF_ITM_MON_CHK 점검 테이블: PK (CHK_YM, ITM_NO) · ITM_NO는 NOT NULL · 통계 최신
바인드 :v_from = '20231201' · :v_to = '20240101' · :v_last = '20231231'

원본 SQL

sql

INSERT INTO CF_ITM_MON_CHK (CHK_YM, ITM_NO, SLE_QTY, ORD_QTY, INV_QTY)
SELECT :v_ym, NVL(s.ITM_NO, o.ITM_NO), s.qty, o.qty, k.qty
  FROM (SELECT ITM_NO, SUM(SLE_QTY) AS qty
          FROM CF11
         WHERE SLE_NO >= :v_from AND SLE_NO < :v_to
         GROUP BY ITM_NO) s
  FULL OUTER JOIN
       (SELECT d.ITM_NO, SUM(d.ORD_QTY) AS qty
          FROM CF12 h, CF13 d
         WHERE h.ORD_DT BETWEEN :v_from AND :v_last
           AND d.ORD_NO = h.ORD_NO
         GROUP BY d.ITM_NO) o
    ON o.ITM_NO = s.ITM_NO
  FULL OUTER JOIN
       (SELECT ITM_NO, SUM(INV_QTY) AS qty
          FROM CF18
         WHERE INV_BS_DT = :v_last
         GROUP BY ITM_NO) k
    ON k.ITM_NO = s.ITM_NO

실행계획 학습용 예시

xplan

[SELECT만 따로 돌린 계획, 129줄]
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |          |      1 |        |    129 |00:00:00.04 |     967 |
|   1 |  VIEW                                 | VW_FOJ_1 |      1 |    129 |    129 |00:00:00.04 |     967 |
|*  2 |   HASH JOIN FULL OUTER                |          |      1 |    129 |    129 |00:00:00.04 |     967 |
|   3 |    VIEW                               |          |      1 |     68 |     68 |00:00:00.01 |       6 |
|   4 |     HASH GROUP BY                     |          |      1 |     68 |     68 |00:00:00.01 |       6 |
|*  5 |      TABLE ACCESS FULL                | CF18     |      1 |    136 |    136 |00:00:00.01 |       6 |
|   6 |    VIEW                               | VW_FOJ_0 |      1 |    108 |    108 |00:00:00.04 |     961 |
|*  7 |     HASH JOIN FULL OUTER              |          |      1 |    108 |    108 |00:00:00.04 |     961 |
|   8 |      VIEW                             |          |      1 |     47 |     47 |00:00:00.01 |      67 |
|   9 |       HASH GROUP BY                   |          |      1 |     47 |     47 |00:00:00.01 |      67 |
|* 10 |        HASH JOIN                      |          |      1 |    600 |    604 |00:00:00.01 |      67 |
|* 11 |         TABLE ACCESS FULL             | CF12     |      1 |     61 |     62 |00:00:00.01 |      10 |
|  12 |         TABLE ACCESS FULL             | CF13     |      1 |   7102 |   7102 |00:00:00.01 |      57 |
|  13 |      VIEW                             |          |      1 |     88 |     88 |00:00:00.03 |     894 |
|  14 |       HASH GROUP BY                   |          |      1 |     88 |     88 |00:00:00.03 |     894 |
|  15 |        TABLE ACCESS BY INDEX ROWID    | CF11     |      1 |  45980 |  46322 |00:00:00.02 |     894 |
|* 16 |         INDEX RANGE SCAN              | CF11_PK  |      1 |  45980 |  46322 |00:00:00.01 |     181 |
-----------------------------------------------------------------------------------------------------------
   2 - access("K"."ITM_NO"="S"."ITM_NO")
   5 - filter("INV_BS_DT"=:V_LAST)
   7 - access("O"."ITM_NO"="S"."ITM_NO")
  10 - access("D"."ORD_NO"="H"."ORD_NO")
  11 - filter(("H"."ORD_DT">=:V_FROM AND "H"."ORD_DT"<=:V_LAST))
  16 - access("SLE_NO">=:V_FROM AND "SLE_NO"<:V_TO)

문제

SELECT가 129줄을 내는 이유를 세 집합의 품목 수(판매 88, 발주 47, 재고 68)로 설명하고, ORA-01400을 일으킨 행이 무엇인지 쓰시오.
세 가지 고침을 SQL로 쓰고 비교하시오. ① 두 번째 ON과 키를 COALESCE로 고친 FULL OUTER JOIN ② UNION ALL로 쌓아 품목별로 묶기 ③ CF01을 기준으로 세 집합을 LEFT JOIN. 결과 줄 수, 읽는 블록, 부작용을 쓰시오.
채택할 안을 정하고, 배치가 맞는 결과를 넣었는지 확인하는 대사 방법을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ② 채택 : 세 집합을 같은 모양으로 쌓고 품목별로 묶는다. 조인도, 키 합치기도 없다
INSERT INTO CF_ITM_MON_CHK (CHK_YM, ITM_NO, SLE_QTY, ORD_QTY, INV_QTY)
SELECT :v_ym, ITM_NO, SUM(sle_qty), SUM(ord_qty), SUM(inv_qty)
  FROM (SELECT ITM_NO, SLE_QTY AS sle_qty,
               CAST(NULL AS NUMBER) AS ord_qty, CAST(NULL AS NUMBER) AS inv_qty
          FROM CF11
         WHERE SLE_NO >= :v_from AND SLE_NO < :v_to
        UNION ALL
        SELECT d.ITM_NO, NULL, d.ORD_QTY, NULL
          FROM CF12 h, CF13 d
         WHERE h.ORD_DT BETWEEN :v_from AND :v_last
           AND d.ORD_NO = h.ORD_NO
        UNION ALL
        SELECT ITM_NO, NULL, NULL, INV_QTY
          FROM CF18
         WHERE INV_BS_DT = :v_last)
 GROUP BY ITM_NO;

-- ① 같은 결과 : FULL OUTER JOIN 을 잇는다면 두 번째 ON 과 키에 앞 집합의 키를 모두 합쳐 쓴다
SELECT :v_ym, COALESCE(s.ITM_NO, o.ITM_NO, k.ITM_NO) AS ITM_NO, s.qty, o.qty, k.qty
  FROM (SELECT ITM_NO, SUM(SLE_QTY) AS qty FROM CF11
         WHERE SLE_NO >= :v_from AND SLE_NO < :v_to GROUP BY ITM_NO) s
  FULL OUTER JOIN
       (SELECT d.ITM_NO, SUM(d.ORD_QTY) AS qty FROM CF12 h, CF13 d
         WHERE h.ORD_DT BETWEEN :v_from AND :v_last AND d.ORD_NO = h.ORD_NO GROUP BY d.ITM_NO) o
    ON o.ITM_NO = s.ITM_NO
  FULL OUTER JOIN
       (SELECT ITM_NO, SUM(INV_QTY) AS qty FROM CF18
         WHERE INV_BS_DT = :v_last GROUP BY ITM_NO) k
    ON k.ITM_NO = COALESCE(s.ITM_NO, o.ITM_NO);

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

xplan

[② UNION ALL · 109행]
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                |                |      1 |        |      0 |00:00:00.05 |     979 |
|   1 |  LOAD TABLE CONVENTIONAL        | CF_ITM_MON_CHK |      1 |        |      0 |00:00:00.05 |     979 |
|   2 |   HASH GROUP BY                 |                |      1 |    109 |    109 |00:00:00.04 |     967 |
|   3 |    VIEW                         |                |      1 |  47062 |  47062 |00:00:00.03 |     967 |
|   4 |     UNION-ALL                   |                |      1 |        |  47062 |00:00:00.03 |     967 |
|   5 |      TABLE ACCESS BY INDEX ROWID| CF11           |      1 |  45980 |  46322 |00:00:00.02 |     894 |
|*  6 |       INDEX RANGE SCAN          | CF11_PK        |      1 |  45980 |  46322 |00:00:00.01 |     181 |
|*  7 |      HASH JOIN                  |                |      1 |    600 |    604 |00:00:00.01 |      67 |
|*  8 |       TABLE ACCESS FULL         | CF12           |      1 |     61 |     62 |00:00:00.01 |      10 |
|   9 |       TABLE ACCESS FULL         | CF13           |      1 |   7102 |   7102 |00:00:00.01 |      57 |
|* 10 |      TABLE ACCESS FULL          | CF18           |      1 |    136 |    136 |00:00:00.01 |       6 |
---------------------------------------------------------------------------------------------------------------
   6 - access("SLE_NO">=:V_FROM AND "SLE_NO"<:V_TO)
   7 - access("D"."ORD_NO"="H"."ORD_NO")
   8 - filter(("H"."ORD_DT">=:V_FROM AND "H"."ORD_DT"<=:V_LAST))
  10 - filter("INV_BS_DT"=:V_LAST)

[① FULL OUTER JOIN 고침]  HASH JOIN FULL OUTER 두 번 · 967블록 · 109행
[③ CF01 기준 LEFT JOIN]   HASH JOIN OUTER 세 번 · 972블록 · 109행 (단종 1개 빠지고 샷추가 1줄 들어감)

모범 답안 서술

129줄이 된 이유: 첫 번째 FULL OUTER JOIN(7번 줄)은 판매 88개와 발주 47개를 맞대어 108행을 만든다. 발주의 판매상품 27개는 짝을 찾고, 원재료 20개는 판매가 없어 s.ITM_NO가 NULL인 행이 된다. 두 번째 FULL OUTER JOIN(2번 줄)의 조건은 k.ITM_NO = s.ITM_NO라서 s.ITM_NO가 NULL인 원재료 20행은 재고와 짝을 지을 수 없다. 그래서 원재료 20개는 발주만 있는 행과 재고만 있는 행으로 두 줄씩 나뉘었고, 마스터에서 지운 단종 품목 1개도 재고만 있는 행이 됐다. 108 + 21 = 129줄이고, 맞는 답은 88 + 20 + 1 = 109줄이다. ORA-01400: 키를 NVL(s.ITM_NO, o.ITM_NO)로 만들었으므로 재고만 있는 21행은 s와 o가 모두 NULL이라 키가 NULL이다. 12월부터 재고에 원재료가 들어오면서 이 행이 처음 생겼고, NOT NULL인 ITM_NO에 넣다가 ORA-01400으로 멈췄다. 그 전 달까지는 재고 품목이 모두 판매 품목이어서 드러나지 않았다. ① FULL OUTER JOIN 고침: 두 번째 ON을 k.ITM_NO = COALESCE(s.ITM_NO, o.ITM_NO)로, 키를 COALESCE(s.ITM_NO, o.ITM_NO, k.ITM_NO)로 고치면 109줄이 맞게 나온다. HASH JOIN FULL OUTER 두 번에 Buffers는 967 그대로다. 다만 집합을 하나 더 붙일 때마다 ON과 키의 COALESCE를 앞 집합 전부로 늘려야 해서, 이번처럼 한 곳만 빠뜨려도 조용히 줄이 나뉜다. ② UNION ALL 고침: 세 집합을 같은 모양(품목, 판매 수량, 발주 수량, 재고 수량)으로 쌓고 품목별로 묶는다. 조인이 없으므로 키를 합칠 일도, ON을 맞출 일도 없다. 세 원천을 한 번씩 읽어 Buffers는 967로 같고, 묶는 행이 47,062행으로 늘지만 해시 GROUP BY 한 번이라 0.05초 안팎이다. 원천이 하나 늘어도 UNION ALL 한 덩어리만 더하면 된다. ③ CF01 기준 LEFT JOIN: 키 문제는 없지만 결과가 마스터의 품목 109개로 고정된다. 마스터에서 지운 단종 품목의 재고 1건이 사라지고, 대신 아무 숫자도 없는 샷추가가 한 줄 들어간다. 줄 수는 109로 같아서 건수 대사로는 잡히지 않는다. 마스터에 없는 품목을 찾아내는 것도 수불 점검의 목적이므로 이 안은 맞지 않는다. 채택과 대사: ②를 채택한다. 넣은 뒤에는 품목 수가 세 집합의 합집합(109개)과 같은지, 판매와 발주와 재고의 수량 합계가 각 원천의 합계(12월 판매상품 46,322줄, 발주상품 604줄, 재고 136행)와 같은지, 키가 NULL이거나 같은 품목이 두 줄인 행이 없는지(PK가 막는다) 확인한다.

해설

FULL OUTER JOIN은 둘까지: 두 집합이면 FULL OUTER JOIN과 COALESCE 키로 충분하다 (JOIN-040, JOIN-041). 셋 이상을 같은 키로 맞대는 일은 UNION ALL과 GROUP BY가 더 짧고 실수할 곳이 적다.
0과 NULL: ②에서 없는 수량을 0이 아니라 NULL로 쌓았다. SUM은 NULL만 있으면 NULL을 내므로 "발주가 없다"와 "발주 수량이 0이다"를 구분할 수 있다. 화면에 0으로 보여야 하면 꺼낼 때 NVL을 쓴다.
데이터가 바뀐 날 터지는 SQL: 원본은 11월까지 맞는 답을 냈다. 재고에 원재료가 들어오는 업무 변화가 키 하나 빠진 ON을 드러냈다. 원천 테이블에 새 종류의 데이터가 들어오는 일정이 있으면 그 데이터를 쓰는 배치 SQL을 미리 대사해 본다 (교재 08화 §10).
INSERT 방식: 109행이라 일반 INSERT로 충분하다. 수만 행을 넣는 배치라면 INSERT /*+ APPEND */와 커밋 시점을 함께 검토한다 (교재 14화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황