「판매 이력 없는 상품」을 지운 다음 날 발주 화면에서 원재료 20개가 사라졌다

중급DELETE유통278 / 700

상황

본사 MD팀은 상품 마스터를 정리하려고 '판매된 적이 한 번도 없는 상품'을 CF01에서 지우는 월 배치를 만들었다. 지난주 처음 돌렸더니 21개가 지워졌다. 다음 날 아침 발주 화면과 재고 화면에서 원두와 우유 같은 원재료 20개가 '상품 없음'으로 나와 구매팀이 발주를 넣지 못했다. DBA가 백업에서 21개를 되살렸고, MD팀은 SQL을 다시 짜 달라고 했다. 유통 DB에는 FK가 하나도 없어서, 지우는 순간 막아 주는 것이 없었다.

테이블과 인덱스

CF01 상품: 109행 · 5블록 · CF01_PK (ITM_NO) · 판매 상품 88개 · 샷추가 1개 · 원재료 20개
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO): BLEVEL 2 · 리프 약 4,290블록 · 원재료 20개와 샷추가는 판매상품에 한 번도 나오지 않는다
CF13 발주상품: 7,102행 · 57블록 · CF13_PK (ORD_NO, ITM_NO) · 원재료 20개는 다달이 발주된다
CF15 입출고상품: 51,497행 · 349블록 · CF15_PK (INOUT_NO, ITM_NO)(리프 약 190블록) · 원재료가 입고될 때마다 쌓인다
CF18 일별상품재고: 408행 · 6블록 · PK (SHP_NO, ITM_NO, INV_BS_DT) · CF05 상품가격: 157행 · 5블록 · PK (ITM_NO, UPRC_KN_CD, APL_DT) · 샷추가는 옵션 가격이 등록되어 있다
이 문제에서 상품번호를 참조하는 테이블은 위 다섯 개로 본다 · FK 없음 · 통계 최신

원본 SQL

sql

DELETE FROM CF01 p
 WHERE p.ITM_NO NOT IN (SELECT d.ITM_NO FROM CF11 d)

실행계획 학습용 예시 · 상품 수는 적재 데이터 그대로

xplan

----------------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT      |         |      1 |        |      0 |00:00:00.22 |    4342 |
|   1 |  DELETE               | CF01    |      1 |        |      0 |00:00:00.22 |    4342 |
|*  2 |   HASH JOIN ANTI      |         |      1 |      1 |     21 |00:00:00.21 |    4310 |
|   3 |    TABLE ACCESS FULL  | CF01    |      1 |    109 |    109 |00:00:00.01 |       5 |
|   4 |    INDEX FAST FULL SCAN| CF11_PK |      1 |   1096K|   1096K|00:00:00.20 |    4305 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("P"."ITM_NO"="D"."ITM_NO")

[결과]  21행 삭제 = 원재료 20 + 샷추가 1

문제

원래 DELETE가 원재료 20개를 지운 까닭을 SQL의 뜻으로 설명하시오. FK가 없는 DB에서 '쓰이지 않는 상품'을 판정하려면 무엇을 확인해야 하는지도 쓰시오.
어디에서도 쓰이지 않는 상품만 지우도록 DELETE를 고치고, 실행계획에서 각 안티 조인이 어떻게 처리될지 설명하시오.
이 배치를 위해 CF11 (ITM_NO) 인덱스를 만들지 판단하고 그 근거를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 상품번호를 참조하는 테이블 다섯 곳 모두에 짝이 없는 상품만 지운다
DELETE FROM CF01 p
 WHERE NOT EXISTS (SELECT 1 FROM CF13 o WHERE o.ITM_NO = p.ITM_NO)   -- 발주상품
   AND NOT EXISTS (SELECT 1 FROM CF15 t WHERE t.ITM_NO = p.ITM_NO)   -- 입출고상품
   AND NOT EXISTS (SELECT 1 FROM CF18 v WHERE v.ITM_NO = p.ITM_NO)   -- 일별상품재고
   AND NOT EXISTS (SELECT 1 FROM CF11 d WHERE d.ITM_NO = p.ITM_NO)   -- 판매상품
   AND NOT EXISTS (SELECT 1 FROM CF05 c WHERE c.ITM_NO = p.ITM_NO);  -- 상품가격
-- 먼저 같은 조건의 SELECT 로 대상 목록을 업무 담당과 확인하고, 지울 행은 백업 테이블에 옮겨 둔 뒤 지운다

개선 후 실행계획 학습용 예시 · 상품 수는 적재 데이터 그대로

xplan

----------------------------------------------------------------------------------------------
| Id  | Operation               | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT        |         |      1 |        |      0 |00:00:00.24 |    4569 |
|   1 |  DELETE                 | CF01    |      1 |        |      0 |00:00:00.24 |    4569 |
|   2 |   NESTED LOOPS ANTI     |         |      1 |      1 |      0 |00:00:00.24 |    4569 |
|*  3 |    HASH JOIN ANTI       |         |      1 |      1 |      1 |00:00:00.24 |    4568 |
|*  4 |     HASH JOIN ANTI      |         |      1 |      1 |     41 |00:00:00.02 |     263 |
|*  5 |      HASH JOIN ANTI     |         |      1 |      1 |     41 |00:00:00.02 |     257 |
|*  6 |       HASH JOIN ANTI    |         |      1 |     41 |     41 |00:00:00.01 |      62 |
|   7 |        TABLE ACCESS FULL| CF01    |      1 |    109 |    109 |00:00:00.01 |       5 |
|   8 |        TABLE ACCESS FULL| CF13    |      1 |   7102 |   7102 |00:00:00.01 |      57 |
|   9 |       INDEX FAST FULL SCAN| CF15_PK |    1 |  51497 |  51497 |00:00:00.01 |     195 |
|  10 |      TABLE ACCESS FULL  | CF18    |      1 |    408 |    408 |00:00:00.01 |       6 |
|  11 |     INDEX FAST FULL SCAN| CF11_PK |      1 |   1096K|   1096K|00:00:00.21 |    4305 |
|* 12 |    INDEX RANGE SCAN     | CF05_PK |      1 |      1 |      1 |00:00:00.01 |       1 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("D"."ITM_NO"="P"."ITM_NO")
   4 - access("V"."ITM_NO"="P"."ITM_NO")
   5 - access("T"."ITM_NO"="P"."ITM_NO")
   6 - access("O"."ITM_NO"="P"."ITM_NO")
  12 - access("C"."ITM_NO"="P"."ITM_NO")

[결과]  0행 삭제 · 발주를 거쳐 41 → 입출고·재고를 거쳐 41 → 판매를 거쳐 1(샷추가) → 가격이 있어 0

모범 답안 서술

원래 DELETE의 뜻은 '판매상품에 한 번도 나오지 않은 상품'이다. 원재료 20개는 팔지 않고 사서 쓰는 상품이라 판매상품에는 없지만, 발주상품(CF13), 입출고상품(CF15), 일별상품재고(CF18)에서 쓰인다. 판매 이력이 없다는 것과 어디에서도 쓰이지 않는다는 것은 다른 질문인데, FK가 없으니 DB가 이 차이를 막아 주지 않았고 21개가 그대로 지워졌다. FK가 없는 DB에서는 상품번호를 참조하는 테이블을 모두 찾아(USER_TAB_COLUMNS와 업무 담당 확인) 각각에 짝이 없는지 확인해야 한다. 고친 DELETE는 참조 테이블 다섯 곳에 NOT EXISTS를 하나씩 건다. 옵티마이저는 각 서브쿼리를 안티 조인으로 펼친다. CF13, CF15, CF18, CF11은 PK의 선두 컬럼이 ITM_NO가 아니라서 상품번호로 범위를 잡을 수 없으므로, 각 테이블(또는 PK 인덱스)을 한 번씩 통째로 읽어 해시 안티 조인으로 거른다. 발주상품을 거치면 매장 제조 상품 41개만 남고(6번 줄), 입출고와 재고를 거쳐도 41개, 판매상품(11번 줄, 4,305블록)을 거치면 샷추가 1개가 남는다. CF05는 PK가 ITM_NO로 시작하므로 남은 1개만 인덱스로 찾아보는 NL 안티 조인(12번 줄)이 되고, 샷추가는 옵션 가격이 있어 지워지지 않는다. 지울 상품은 0개다. 전체 4,569블록, 0.24초다. CF11 (ITM_NO) 인덱스는 만들지 않는다. 이 배치는 한 달에 한 번 돌고, CF11_PK를 한 번 빠르게 훑는 4,305블록이면 끝난다. 인덱스를 만들면 상품 109개를 NL 안티 조인으로 찾아 400블록 안팎으로 줄일 수는 있지만, 판매상품은 하루 1,500줄씩 들어오는 110만 행짜리 테이블이라 인덱스를 날마다 고쳐야 한다. 한 달에 4천 블록을 아끼려고 매일 적재 비용을 치를 까닭이 없다.

해설

지우기 전에 고르기부터: DELETE 조건을 먼저 SELECT로 돌려 대상 목록을 업무 담당에게 확인받고, 지울 행은 백업 테이블(CTAS)에 옮겨 둔 뒤 지운다. 이번 장애도 대상 21개 목록을 한 번만 봤다면 원재료가 섞인 것을 알아챌 수 있었다.
참조 테이블 찾기: USER_TAB_COLUMNS에서 ITM_NO가 들어간 컬럼을 찾되, 원재료 구성표나 옵션표처럼 이름이 다른 컬럼(MTRL_ITM_NO, OPT_ITM_NO 같은 것)도 있으므로 업무 담당과 목록을 맞춘다. FK가 있었다면 이 목록을 DB가 들고 있었을 것이다.
NOT IN을 써도 되나: 다섯 테이블의 ITM_NO는 모두 PK 컬럼이라 NULL이 없으므로 NOT IN도 같은 답과 같은 계획을 낸다. 다만 나중에 NULL 허용 컬럼이 참조 목록에 더해지면 그 NOT IN 하나가 DELETE 전체를 0건으로 만든다. NOT EXISTS로 통일해 두는 편이 안전하다.
숨기는 대안: 지우는 대신 판매 여부나 사용 여부 컬럼으로 숨기면 되살리는 비용이 없다. 마스터 정리는 지우기보다 숨기기를 기본으로 삼는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 상품 수는 적재 데이터 그대로 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 상품 수는 적재 데이터 그대로 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황