보관을 마친 작년 주문 5만 건을 지우는데 TKPROF에 주문상세 DELETE가 5만 번 찍힌다

기초DELETE한빛유통471 / 700

상황

매년 1월 첫 일요일 보관 배치는 지난해 주문과 주문상세를 보관 테이블로 옮긴 뒤 운영 테이블에서 지운다. 올해 리허설에서 옮기는 단계는 1초 안쪽이었는데 지우는 단계만 8.9초였다. 오과장: "DELETE는 주문 한 문장뿐인데 TKPROF에 주문상세 DELETE가 5만 번 찍혀요." 주문상세 FK는 2017년 「주문 삭제」 화면을 만들 때 ON DELETE CASCADE로 바꿨다. 그 화면은 2020년에 없어졌고, 지금은 취소도 상태(CANCEL)로만 남긴다. 주문을 지우는 것은 이 보관 배치뿐이다.

테이블과 인덱스

T_ORD: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · ORD_DT 2023-01-01 ~ 2024-12-31 · 2023년 주문 50,060건
T_ORD_DET: 200,000행 · 896블록(전체 스캔 807) · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) · 2023년 주문의 상세 100,140행 · FK_T_ORD_DET_ORD (ORD_SEQ) REFERENCES T_ORD ON DELETE CASCADE
보관 테이블 T_ORD_ARC, T_ORD_DET_ARC에 2023년분을 넣고 건수 대조까지 끝냈다. T_ORD를 참조하는 다른 FK는 없다.
연습 DB 복사본(NOARCHIVELOG)이다. 보관 배치가 도는 동안 두 테이블에 쓰는 다른 세션은 없다.

원본 SQL

sql

-- 매년 1월 첫 일요일 · 보관 배치 ③ 운영에서 지우기 (보관과 건수 대조가 끝난 뒤)
DELETE FROM T_ORD
 WHERE ORD_DT >= DATE '2023-01-01' AND ORD_DT < DATE '2024-01-01';   -- 상세는 CASCADE 가 지운다
COMMIT;

실행계획 학습용 예시 · 2023년 주문과 상세 건수는 DML-036과 같은 규모

xplan

----------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |       |      1 |        |      0 |00:00:08.91 |    812K |
|   1 |  DELETE            | T_ORD |      1 |        |      0 |00:00:08.91 |    812K |
|*  2 |   TABLE ACCESS FULL| T_ORD |      1 |  50060 |  50060 |00:00:00.02 |     885 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("ORD_DT">=TO_DATE(' 2023-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "ORD_DT"<TO_DATE(' 2024-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

[세션 통계 — ③ 한 번]
  경과 시간                  8.91 초
  CPU used by this session   7.62 초
  recursive calls            50,214
  execute count              50,108
  session logical reads      812,430
  redo size                  96.8 MB
  undo change vector size    41.2 MB

[TKPROF — 재귀 SQL (dep=1)]
delete from "SQLP"."T_ORD_DET" where "ORD_SEQ" = :1
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Execute  50060      4.12       4.48          0     150188     411562      100140

문제

주문 DELETE 한 문장에서 주문상세 DELETE가 5만 번 실행된 이유를 설명하시오.
같은 결과를 내도록 지우는 단계를 고치시오. FK는 어떻게 할지와 그 근거를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① CASCADE 를 붙인 이유(2017년 「주문 삭제」 화면)가 사라졌다 → 평범한 FK 로 되돌린다
ALTER TABLE T_ORD_DET DROP CONSTRAINT FK_T_ORD_DET_ORD;
ALTER TABLE T_ORD_DET ADD CONSTRAINT FK_T_ORD_DET_ORD
  FOREIGN KEY (ORD_SEQ) REFERENCES T_ORD (ORD_SEQ);

-- ② 보관 배치 ③ — 자식을 한 문장으로 먼저, 부모를 한 문장으로
DELETE FROM T_ORD_DET
 WHERE ORD_SEQ IN (SELECT ORD_SEQ FROM T_ORD
                    WHERE ORD_DT >= DATE '2023-01-01' AND ORD_DT < DATE '2024-01-01');
DELETE FROM T_ORD
 WHERE ORD_DT >= DATE '2023-01-01' AND ORD_DT < DATE '2024-01-01';
COMMIT;

개선 후 실행계획 학습용 예시 · 2023년 주문과 상세 건수는 DML-036과 같은 규모

xplan

[자식 DELETE]
----------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT       |           |      1 |        |      0 |00:00:01.62 |    404K |
|   1 |  DELETE                | T_ORD_DET |      1 |        |      0 |00:00:01.62 |    404K |
|*  2 |   HASH JOIN RIGHT SEMI |           |      1 |    100K|    100K|00:00:00.06 |    1692 |
|*  3 |    TABLE ACCESS FULL   | T_ORD     |      1 |  50060 |  50060 |00:00:00.01 |     885 |
|   4 |    TABLE ACCESS FULL   | T_ORD_DET |      1 |    200K|    200K|00:00:00.02 |     807 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ORD_SEQ"="ORD_SEQ")
   3 - filter(("ORD_DT">=TO_DATE(' 2023-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "ORD_DT"<TO_DATE(' 2024-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

[부모 DELETE] TABLE ACCESS FULL T_ORD → DELETE · Buffers 237K · 1.4 초
              (자식 확인은 PK_T_ORD_DET 를 SQL 엔진 안에서 직접 본다 · 재귀 SQL 없음)
[두 문장 합계] 경과 8.91 → 3.08 초 · recursive calls 50,214 → 46 · logical reads 812,430 → 641,300
               redo 96.8 → 95.9 MB (지우는 행이 같으므로 거의 그대로)
[FK 다시 걸기] ENABLE VALIDATE 0.2 초 (T_ORD_DET 10만 행을 부모 PK 로 확인)

모범 답안 서술

ON DELETE CASCADE는 부모 한 행을 지울 때마다 그 행을 가리키는 자식을 지우는 DELETE를 재귀 SQL로 한 번씩 실행한다. 주문 DELETE가 2023년 주문 50,060행을 지우는 동안 delete from T_ORD_DET where ORD_SEQ = :1이 50,060번 실행됐고(TKPROF Execute 50060), 한 번에 상세 두 행 남짓을 PK_T_ORD_DET로 찾아 지웠다. 재귀 실행 5만 번이 recursive calls 50,214와 CPU 7.62초의 대부분이다. 고친 방법은 자식을 먼저 한 문장으로 지우고 부모를 한 문장으로 지우는 것이다. 주문상세 DELETE는 2023년 주문 번호를 해시 세미 조인으로 한 번에 골라 100,140행을 지운다. 그런데 CASCADE가 남아 있으면 부모를 지울 때 주문마다 재귀 DELETE가 0행으로 그대로 돈다. 그래서 FK에서 CASCADE를 떼어 평범한 FK로 다시 건다. 평범한 FK는 부모를 지울 때 자식이 남아 있는지를 SQL 엔진 안에서 PK_T_ORD_DET로 직접 확인하므로 재귀 SQL이 없다. 두 문장 합계는 8.91 → 3.08초, recursive calls는 50,214 → 46으로 준다. redo는 96.8 → 95.9MB로 거의 그대로다. 지우는 행 수(주문 50,060, 상세 100,140)가 같고, 행마다 테이블과 인덱스에서 지우는 기록을 남기는 일도 같기 때문이다. 이 처방이 줄이는 것은 행마다 문장을 실행하는 비용이다. CASCADE를 떼도 되는 근거는 붙인 이유가 사라진 데 있다. 2017년 「주문 삭제」 화면은 2020년에 없어졌고, 지금 주문을 지우는 것은 이 보관 배치뿐이다. 떼고 나면 누가 실수로 주문을 지워도 상세가 함께 사라지지 않고 ORA-02292로 막힌다. FK를 다시 걸 때 T_ORD_DET를 부모 PK로 확인하는 데 0.2초가 들고 그동안 T_ORD_DET에 잠금이 걸리므로, 다른 세션이 없는 보관 배치 창에서 한 번만 한다.

해설

함정: 자식을 먼저 지우는 문장만 더하고 CASCADE를 그대로 두면 재귀 DELETE 5만 번은 0행인 채로 남는다. 시간이 절반쯤만 준다.
자식 FK 칸이 맨 앞인 인덱스가 없었다면 재귀 DELETE 한 번마다 T_ORD_DET 807블록을 통째로 읽어 5만 번 × 807블록이 됐을 것이다. 여기서는 PK_T_ORD_DET의 맨 앞 칸이 ORD_SEQ라서 피했다(교재 12화의 인덱스 없는 FK).
지울 행이 테이블의 절반이다. 해마다 반복한다면 주문일 기준 연도 파티션과 참조 파티션으로 바꿔 파티션째 정리하는 쪽이 redo까지 줄인다(DML-003, 교재 14화 §11).
CASCADE는 편하지만 '부모 한 줄 지우기'가 얼마나 많은 행을 지우는지 문장에 드러나지 않는다. 지우는 범위는 SQL에 보이게 쓰는 편이 안전하다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 2023년 주문과 상세 건수는 DML-036과 같은 규모 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 2023년 주문과 상세 건수는 DML-036과 같은 규모 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황