판매상품 6개월 치를 5,000건씩 끊어 지우는 정리가 56번 모두 판매 테이블을 통째로 다시 읽는다

중급DELETE유통488 / 700

상황

본사 레거시 유통은 운영 판매 보관 기간을 2년에서 18개월로 줄이기로 했다. 처음 한 번은 2022년 1 ~ 6월을 지우고, 그 뒤로는 매달 가장 오래된 한 달을 지운다. 24시간 매장 두 곳의 POS가 새벽에도 판매를 넣으므로 테이블을 새로 만들어 바꿔 끼울 수는 없고, 언두 여유가 작아서 한 번에 지우지 말라는 DBA의 요청이 있었다. 개발자가 5,000건씩 지우고 커밋하는 루프를 짰다. 리허설에서 판매상품 루프만 1분 48초가 걸렸는데, DBA가 회차별 기록을 보더니 물었다. "5,000건 지우는 데 매번 블록을 4,600 ~ 6,600개씩 읽고, 뒤로 갈수록 더 읽네요?"

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록(블록당 약 109행) · 판매 날짜순 적재 · 2022-01-01 ~ 2023-12-31 · CF10_PK (SLE_NO) · IX_CF10_SLE_DT (SLE_DT) · 2022년 1 ~ 6월 124,318건
CF11 판매상품: 1,096,708행 · 8,204블록(블록당 약 134행) · CF11_PK (SLE_NO, ITM_NO) · 판매번호 순으로 쌓였다 · 2022년 1 ~ 6월 272,200행
SLE_NO VARCHAR2(15): 판매일자 8자리 + - + 그날의 일련번호 4자리('20220103-0127'). CF11과 CF10 사이에 FK는 없다.
T_JOB_LOG (JOB_NM, PIECE, ROWS_DONE, DONE_AT) 작업 기록 테이블이 있다. 통계는 최신이다.

원본 SQL

sql

-- 보관 기간 단축 · 처음 한 번 2022년 1 ~ 6월 (개발팀)
DECLARE
  v_cnt NUMBER;
BEGIN
  LOOP                                                   -- ① 판매상품
    DELETE FROM CF11
     WHERE SLE_NO IN (SELECT SLE_NO FROM CF10 WHERE SLE_DT < '20220701')
       AND ROWNUM <= 5000;
    v_cnt := SQL%ROWCOUNT;
    COMMIT;
    EXIT WHEN v_cnt = 0;
  END LOOP;

  LOOP                                                   -- ② 판매
    DELETE FROM CF10
     WHERE SLE_DT < '20220701'
       AND ROWNUM <= 5000;
    v_cnt := SQL%ROWCOUNT;
    COMMIT;
    EXIT WHEN v_cnt = 0;
  END LOOP;
END;

실행계획 학습용 예시

xplan

[① 판매상품 루프 — 54회차 DELETE (DISPLAY_CURSOR · ALLSTATS LAST)]
-------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT       |      |      1 |        |      0 |00:00:02.08 |   21730 |
|   1 |  DELETE                | CF11 |      1 |        |      0 |00:00:02.08 |   21730 |
|*  2 |   COUNT STOPKEY        |      |      1 |        |   5000 |00:00:00.41 |    6582 |
|*  3 |    HASH JOIN RIGHT SEMI|      |      1 |   5000 |   5000 |00:00:00.41 |    6582 |
|*  4 |     TABLE ACCESS FULL  | CF10 |      1 |    124K|    124K|00:00:00.09 |    4583 |
|   5 |     TABLE ACCESS FULL  | CF11 |      1 |   1096K|   5000 |00:00:00.03 |    1999 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<=5000)
   3 - access("SLE_NO"="SLE_NO")
   4 - filter("SLE_DT"<'20220701')

[회차별 기록 — ① 판매상품]
  회차   지운 행   행을 찾는 데 읽은 블록   경과
   1     5,000       4,621              1.6초
   2     5,000       4,658              1.6초
  27     5,000       5,583              1.8초
  54     5,000       6,582              2.1초
  55     2,200       6,620              1.4초
  56         0       6,620              0.9초   (0행임을 알려고 한 번 더 다 읽는다)
  합계  272,200     약 316,000             1분 48초 · user commits 56

[② 판매 루프] 25회차 · 행을 찾는 데 읽은 블록 약 15,000 · 3초
[세션 통계 — ①과 ② 합계] redo size 약 96 MB · undo change vector size 약 41 MB

문제

5,000건을 지우는 데 매번 4,600블록이 넘게 들고 뒤로 갈수록 더 읽는 이유를 실행계획의 줄 번호로 설명하시오.
5,000건씩 끊어 커밋하는 이유(언두)는 살리면서, 청크마다 자기 몫만 읽도록 다시 작성하시오. 그 방법이 기대는 전제와 확인 방법도 쓰시오.
청크로 나눠 지우면 무엇이 줄고 무엇이 그대로인지, 중간에 실패하면 어떻게 다시 돌리는지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ⓪ 전제 확인: 판매번호 앞 8자리 = 판매일 (0 이어야 판매번호 범위가 곧 날짜 범위다)
SELECT COUNT(*) FROM CF10 WHERE SUBSTR(SLE_NO, 1, 8) <> SLE_DT;

-- ① ② 하루가 한 청크 · 판매번호 범위는 CF11_PK 와 CF10_PK 의 첫 컬럼이라 그 범위만 읽는다
DECLARE
  v_d    DATE := DATE '2022-01-01';
  v_from VARCHAR2(8);
  v_to   VARCHAR2(8);
  v_det  NUMBER;
  v_hdr  NUMBER;
BEGIN
  WHILE v_d < DATE '2022-07-01' LOOP
    v_from := TO_CHAR(v_d, 'YYYYMMDD');
    v_to   := TO_CHAR(v_d + 1, 'YYYYMMDD');

    DELETE FROM CF11 WHERE SLE_NO >= v_from AND SLE_NO < v_to;    -- 그날 판매상품 약 1,500행
    v_det := SQL%ROWCOUNT;
    DELETE FROM CF10 WHERE SLE_NO >= v_from AND SLE_NO < v_to;    -- 그날 판매 약 690행
    v_hdr := SQL%ROWCOUNT;

    INSERT INTO T_JOB_LOG (JOB_NM, PIECE, ROWS_DONE, DONE_AT)
    VALUES ('CF_PURGE', v_from, v_det + v_hdr, SYSDATE);
    COMMIT;                                  -- 하루마다: 언두는 그날 몫만 쥔다 · 판매상품과 판매가 함께 확정된다
    v_d := v_d + 1;
  END LOOP;
END;

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

xplan

[청크 하나 — 2022-03-15 판매상품]
-------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |         |      1 |        |      0 |00:00:00.05 |    4712 |
|   1 |  DELETE            | CF11    |      1 |        |      0 |00:00:00.05 |    4712 |
|*  2 |   INDEX RANGE SCAN | CF11_PK |      1 |   1504 |   1504 |00:00:00.01 |      16 |
-------------------------------------------------------------------------------------
   2 - access("SLE_NO">=:B2 AND "SLE_NO"<:B1)

[작업 전체 — 181청크]
  지운 행: 판매상품 272,200 · 판매 124,318
  행을 찾는 데 읽은 블록: 약 316,000 + 15,000 → 약 3,400 (청크마다 16 ~ 20블록)
  경과: 1분 51초 → 19초 · user commits 181
  redo size 약 96 MB · undo change vector size 약 41 MB (그대로) · 한 청크가 쥐는 언두 약 0.3 MB

모범 답안 서술

원본 루프는 회차마다 '지울 행 고르기'를 처음부터 다시 한다. 4번 줄은 서브쿼리 쪽 CF10을 전체 스캔해 2022년 상반기 판매 124K건을 해시 테이블로 올린다. 판매(CF10)는 ② 루프에서야 지우므로 이 일은 1회차든 56회차든 똑같이 4,583블록이다. 그다음 5번 줄이 CF11을 처음부터 읽다가 5,000건이 차면 멈추는데(2번 줄 COUNT STOPKEY), CF11은 판매번호 순으로 쌓여 있어 앞 회차들이 지운 자리가 테이블 앞쪽에 빈 블록으로 남는다. 54회차의 1,999블록 가운데 살아 있는 행이 있는 블록은 끝의 37개 남짓뿐이다. 그래서 회차당 4,621블록에서 시작해 6,620블록까지 늘고, 마지막에는 0행임을 확인하려고 한 번 더 다 읽는다. 56회 합계 약 31만 6천 블록을 행을 찾는 데만 썼다. ② 판매 루프도 같은 모양으로 앞쪽 빈 블록을 회차마다 다시 훑는다. 나누는 기준을 행 수에서 키 범위로 바꾼다. 판매번호는 판매일자 8자리로 시작하므로 '그날의 판매번호 범위'가 곧 '그날의 판매'다. SLE_NO >= '20220315' AND SLE_NO < '20220316'은 CF11_PK와 CF10_PK의 첫 컬럼 범위 조건이라 인덱스 범위 스캔으로 그날의 행만 찾는다(16블록). 청크마다 자기 범위만 읽고 앞서 지운 자리를 다시 보지 않으며, CF10과 조인할 필요도 없다. 행을 찾는 데 읽은 블록은 약 33만 1천 → 약 3,400, 경과는 1분 51초 → 19초로 준다. 이 방법은 '판매번호 앞 8자리 = 판매일'이라는 전제에 기대므로, 먼저 SUBSTR(SLE_NO, 1, 8) <> SLE_DT인 행이 0건인지 확인한다. 청크로 나눠 지워도 지우는 행은 같으므로 redo(약 96MB)와 undo(약 41MB)의 총량은 그대로다. 줄어드는 것은 한 트랜잭션이 쥐는 언두(하루 약 0.3MB)와, 대상을 다시 찾느라 읽던 양이다. 하루마다 판매상품과 판매를 함께 지우고 작업 기록과 같이 커밋하므로, 중간에 실패하면 그날 청크만 롤백된다. 이미 지운 날은 다시 돌려도 0행이라 그대로 처음부터 다시 돌려도 남은 날만 지운다. 기록을 보고 실패한 날부터 시작해도 된다.

해설

ROWNUM 청크의 두 가지 비용: 대상을 고르는 일(조인, 서브쿼리)을 회차마다 되풀이하는 것, 그리고 앞 회차가 비운 블록을 다음 회차가 다시 훑는 것이다. 회차가 많을수록 둘 다 커진다. 청크는 '다음에 읽을 자리'가 정해지는 키 범위로 나눈다(교재 13화 「커밋 주기 기준」의 '키 범위로 쪼갠다').
전제가 없을 때: 판매번호에 날짜가 들어 있지 않다면 판매일 인덱스로 그날의 판매번호를 먼저 고르고(SLE_NO IN (SELECT SLE_NO FROM CF10 WHERE SLE_DT = :d)) 판매상품은 CF11_PK로 찾아간다. 대상 고르기를 한 번만 하려면 지울 키를 임시 테이블에 모아 두고 키 범위로 나눠 지우는 방법도 있다.
ROWID 범위로 나누는 방법(DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID)도 청크마다 자기 블록만 읽는다. 다만 조건에 맞는 행이 테이블 앞쪽에 몰려 있으면 뒤쪽 청크는 읽기만 하고 지울 행이 없다. 날짜순으로 쌓인 테이블에서는 키 범위가 더 맞는다.
지울 비율이 크면: 이번 정리는 판매상품의 25%다. 절반을 넘게 지운다면 남길 행을 옮기는 쪽이 싸지만(DML-044), 운영 중이라 바꿔 끼울 수 없다는 조건이 있었다. 매달 한 달씩 지우는 일이 계속된다면 판매일 기준 파티션과 칸 단위 정리가 근본 처방이다(PART 단원).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황