PART-044

세금계산서 판매만 5년 남기라는 규정 때문에 2만 행 DELETE가 매달 redo 9MB를 쓴다

중급DELETE유통419 / 700

상황

유통 본사의 판매 보관 기준은 24개월이다. 다만 세금계산서를 발행한 법인 판매는 세법에 따라 5년을 보관해야 한다. 그래서 매월 1일 01:00 「보관 정리」는 24개월이 지난 달의 칸을 통째로 들어내지 못하고, 법인 판매를 뺀 나머지를 DELETE로 지운다. 이번 달 대상은 2022년 1월이다. 정리한 칸에는 행이 3%만 남는데 FULL로 읽으면 그대로 191블록이고, 정리하는 밤마다 redo가 9MB 넘게 나온다. 법인 여부는 판매 테이블의 컬럼이 아니라 세금계산서 테이블에 있어서 칸 기준으로 삼을 수도 없다. 박팀장: "남길 행이 있어도 칸 단위로 처리할 방법을 찾아 봐."

테이블과 인덱스

CF10_P 판매(파티션 복사본, 문제 설정): CF10 500,000행을 PARTITION BY RANGE (SLE_DT) 월별 24칸(P202201 ~ P202312)과 빈 PMAX로 나눴다. 컬럼은 SLE_NO · SLE_DT · SLE_HMS · SLE_DOW · SHP_NO다. P202201은 1번째 칸이고 20,831행, FULL로 191블록(세그먼트 8MB)이다.
LOCAL 인덱스 둘: PK CF10_P_PK (SLE_NO, SLE_DT) 유니크 · CF10_P_X01 (SHP_NO, SLE_DT). GLOBAL 인덱스, FK, 트리거는 없다.
CF10_TAX 세금계산서 발행 판매(문제 설정): 14,880행 · SLE_NO · TAX_NO · ISS_DT · PK CF10_TAX_PK (SLE_NO). 판매의 약 3%이고, 2022년 1월 판매 중에서는 624건이다.
판매상품 CF11의 정리는 다른 단계에서 한다(이 문제에서는 다루지 않는다). 운영은 ARCHIVELOG다. 정리 시각(01:00 ~ 01:30)에 이 칸을 읽거나 고치는 다른 작업은 없다.

원본 SQL

sql

-- 매월 1일 01:00 · 24개월이 지난 달 정리 (이번 달 = 2022년 1월)
DELETE FROM CF10_P s
 WHERE s.SLE_DT >= '20220101'
   AND s.SLE_DT <  '20220201'
   AND NOT EXISTS (SELECT 1 FROM CF10_TAX t WHERE t.SLE_NO = s.SLE_NO);
COMMIT;

실행계획 학습용 예시

xplan

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name        | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT          |             |      1 |        |      0 | 00:00:00.71 |   63412 |        |       |
|   1 |  DELETE                   | CF10_P      |      1 |        |      0 | 00:00:00.71 |   63412 |        |       |
|*  2 |   HASH JOIN RIGHT ANTI    |             |      1 |  20207 |  20207 | 00:00:00.04 |     253 |        |       |
|   3 |    INDEX FAST FULL SCAN   | CF10_TAX_PK |      1 |  14880 |  14880 | 00:00:00.01 |      62 |        |       |
|   4 |    PARTITION RANGE SINGLE |             |      1 |  20831 |  20831 | 00:00:00.01 |     191 |      1 |     1 |
|*  5 |     TABLE ACCESS FULL     | CF10_P      |      1 |  20831 |  20831 | 00:00:00.01 |     191 |      1 |     1 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T"."SLE_NO"="S"."SLE_NO")
   5 - filter(("S"."SLE_DT">='20220101' AND "S"."SLE_DT"<'20220201'))

[세션 통계 · DELETE + COMMIT]
  지운 행 20,207 · redo size 약 9.4 MB · undo change vector size 약 4.6 MB · db block changes 약 61,000

[정리 뒤 · P202201]
  남은 행 624 · FULL Buffers 191 그대로 · 세그먼트 8 MB 그대로 · 두 인덱스 칸의 리프 블록 수도 그대로

문제

지금 DELETE의 비용이 어디서 나오는지, 정리한 칸이 왜 그대로 191블록인지 설명하시오.
남길 법인 판매 624건은 지키면서 행을 하나씩 지우지 않도록, 칸 단위 작업으로 정리 SQL을 다시 작성하시오.
남길 행을 따로 빼 두고 칸을 TRUNCATE한 뒤 다시 넣는 방법과 비교해 고른 방법이 나은 까닭을 쓰고, 이 방법이 맞지 않는 경우도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- (1) 칸과 구조가 똑같은 빈 테이블에 남길 행(세금계산서 판매)만 담는다
CREATE TABLE CF10_KEEP_202201 FOR EXCHANGE WITH TABLE CF10_P;

INSERT /*+ APPEND */ INTO CF10_KEEP_202201 (SLE_NO, SLE_DT, SLE_HMS, SLE_DOW, SHP_NO)
SELECT s.SLE_NO, s.SLE_DT, s.SLE_HMS, s.SLE_DOW, s.SHP_NO
  FROM CF10_P PARTITION (P202201) s
 WHERE EXISTS (SELECT 1 FROM CF10_TAX t WHERE t.SLE_NO = s.SLE_NO);
COMMIT;                                                         -- 624행

-- (2) 칸의 로컬 인덱스와 짝이 맞게
ALTER TABLE CF10_KEEP_202201 ADD CONSTRAINT CF10_KEEP_202201_PK PRIMARY KEY (SLE_NO, SLE_DT);
CREATE INDEX CF10_KEEP_202201_X01 ON CF10_KEEP_202201 (SHP_NO, SLE_DT);

-- (3) 칸과 맞바꾼다 : 칸에는 624행만, 교환용 테이블에는 옛 2022년 1월 20,831행이 간다
--     그 칸에서 꺼낸 행만 담았으므로 범위 검증을 생략한다
ALTER TABLE CF10_P
  EXCHANGE PARTITION P202201 WITH TABLE CF10_KEEP_202201
  INCLUDING INDEXES WITHOUT VALIDATION;

-- (4) 확인 뒤 옛 달을 통째로 버린다 (행을 하나씩 지우지 않는다)
SELECT COUNT(*) FROM CF10_P PARTITION (P202201);               -- 624
SELECT index_name, status FROM user_ind_partitions
 WHERE partition_name = 'P202201' AND status <> 'USABLE';      -- 0행
DROP TABLE CF10_KEEP_202201 PURGE;

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

xplan

[세션 통계 · (1) ~ (4)]
  INSERT 624행 · 인덱스 둘 · EXCHANGE 0.05초 · DROP TABLE PURGE 0.04초
  redo size 약 9.4 MB → 약 0.3 MB · undo 약 4.6 MB → 약 0.05 MB

[정리 뒤 · P202201 FULL]
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name        | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |             |      1 |        |      1 | 00:00:00.01 |       6 |        |       |
|   1 |  SORT AGGREGATE           |             |      1 |      1 |      1 | 00:00:00.01 |       6 |        |       |
|   2 |   PARTITION RANGE SINGLE  |             |      1 |    624 |    624 | 00:00:00.01 |       6 |      1 |     1 |
|   3 |    TABLE ACCESS FULL      | CF10_P      |      1 |    624 |    624 | 00:00:00.01 |       6 |      1 |     1 |
-------------------------------------------------------------------------------------------------------------------
  (191 → 6블록 · 세그먼트 8 MB → 64 KB)

모범 답안 서술

지금 DELETE는 칸 하나만 읽는다(PARTITION RANGE SINGLE, Pstart 1, Pstop 1, 191블록). 비싼 것은 지우는 일이다. 20,207행마다 테이블 행 하나와 로컬 인덱스 키 둘(PK, X01)을 지우고, 지우기 전 모습을 undo에 적은 뒤 그 변경을 다시 redo에 남긴다. DELETE 줄의 Buffers 63,412와 redo 약 9.4MB, undo 약 4.6MB가 그 일이다. 그리고 DELETE는 행만 지우고 칸의 고수위선과 세그먼트(8MB)를 그대로 둔다. 전체 스캔은 고수위선까지 읽으므로 624행만 남은 칸도 FULL로 191블록을 읽는다. 남길 행이 3%뿐이라면 지울 2만 행이 아니라 남길 624행을 옮기는 편이 싸다. 칸과 구조가 똑같은 빈 테이블(FOR EXCHANGE WITH TABLE)에 그 칸의 세금계산서 판매만 INSERT /*+ APPEND */로 담고, 칸의 로컬 인덱스와 짝이 맞는 PK와 인덱스를 만든 뒤 EXCHANGE PARTITION P202201 … INCLUDING INDEXES로 맞바꾼다. 맞바꾸면 칸에는 남길 624행만 들어가고, 옛 2022년 1월 20,831행은 교환용 테이블로 넘어간다. 그 테이블은 확인한 뒤 DROP TABLE … PURGE로 통째로 버린다. 행을 하나씩 지우지 않으므로 redo는 약 9.4 → 0.3MB, undo는 약 4.6 → 0.05MB로 줄고, 칸은 191 → 6블록, 세그먼트는 8MB → 64KB가 된다. 교환용 테이블의 행은 모두 그 칸에서 꺼낸 것이라 범위 밖 행이 있을 수 없으므로 WITHOUT VALIDATION으로 검증을 생략해도 된다. 다른 방법은 남길 행을 CTAS로 빼 두고 TRUNCATE PARTITION 뒤 다시 넣는 것이다. 결과는 같지만, TRUNCATE와 INSERT 사이에는 법인 판매가 칸에서 사라지는 틈이 있고 TRUNCATE는 DDL이라 되돌릴 수 없다. INSERT가 실패하면 빼 둔 테이블에서 다시 넣어야 한다. 교환은 한 문장으로 바뀌고, 실패하면 칸은 그대로다. 이 방법이 맞지 않는 경우도 있다. 남길 행이 칸의 대부분이라면(예를 들어 절반 이상) 옮기는 양이 지우는 양보다 커져 이점이 사라진다. 그때는 DELETE가 낫다. 남길 기준이 판매 테이블의 컬럼이라면 처음부터 그 기준으로 서브파티션을 나누는 설계가 근본 처방이다.

해설

교환은 '지우기'에도 쓴다. 남길 행을 담은 테이블과 칸을 맞바꾸면 지울 행은 칸 밖으로 나가고, 테이블째 버리면 끝난다. 칸 경계와 보관 기준이 어긋날 때 DELETE 말고 고를 수 있는 길이다.
남길 기준이 컬럼으로 있다면 RANGE 칸 안을 LIST 서브파티션으로 나눠 조각째 비우는 설계가 낫다(PART-038). 이 문제처럼 기준이 다른 테이블에 있으면 컬럼을 더하거나 교환으로 처리한다.
교환 뒤 칸 통계는 교환용 테이블의 통계로 바뀐다. 빈 테이블에 APPEND로 넣으면 그때 통계를 모으므로 칸 통계도 624행으로 맞춰진다.
법인 판매의 판매상품(CF11)도 함께 남겨야 한다. 판매상품 정리 단계가 'CF10_P에 남은 판매번호'를 기준으로 하는지 확인한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황