PART-003

매달 한 달 치를 DELETE로 지우는데, 지운 칸이 그대로 무겁다

실전DDL버거378 / 700

상황

매달 1일 03:00에 도는 「보관 기간 정리」 배치는 보관 규정 24개월이 지난 가장 오래된 달을 지운다. 배치 담당 오과장: "파티션 테이블인데 왜 DELETE하냐고 물었더니, 처음 만든 사람이 그렇게 했다고만 한다." 지운 뒤에도 전체 칸을 읽는 조회(분기 리포트 등)가 가벼워지지 않고, 정리하는 밤마다 redo가 20MB 넘게 아카이브로 나간다(운영은 ARCHIVELOG). 이번 달 정리 대상은 2012년 1월(P201201)이다.

테이블과 인덱스

ORD_RANGE: 1,000,000행 · PARTITION BY RANGE (ORD_YM) 월별 25칸(P201201 ~ P201312 + 빈 P_DEFAULT (MAXVALUE)) · 칸 하나 약 16MB · FULL ≈ 1,980블록
매일 밤 그날 주문이 적재된다. 2014년 1월 칸은 아직 없어서, 오늘(1월 1일) 밤 적재분부터 P_DEFAULT로 들어간다.
2012년 1월은 42,408행이다.
LOCAL 인덱스 ORD_RANGE_PK (ORD_NO, ORD_YM) · ORD_RANGE_X01 (ORD_DT, ORD_HMS)
GLOBAL 유니크 인덱스 ORD_RANGE_GX01 (ORD_NO): 영수증 재조회용(하루 약 6만 번, 4블록)
다른 테이블이 ORD_RANGE를 FK로 참조하지 않는다. 정리 시각에 다른 작업은 없다.

원본 SQL

sql

-- 매월 1일 03:00 · 보관 24 개월이 지난 달
DELETE FROM ORD_RANGE
 WHERE ORD_YM = '201201';
COMMIT;

실행계획 학습용 예시 · 교재 14화 규모

xplan

[DELETE 계획 — 2012년 1월 한 칸]
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Starts | E-Rows | A-Rows |    A-Time   | Buffers | Pstart | Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT         |           |      1 |        |      0 | 00:00:00.42 |    172K |        |       |
|   1 |  DELETE                  | ORD_RANGE |      1 |        |      0 | 00:00:00.42 |    172K |        |       |
|   2 |   PARTITION RANGE SINGLE |           |      1 |  42408 |  42408 | 00:00:00.02 |    1980 |      1 |     1 |
|*  3 |    TABLE ACCESS FULL     | ORD_RANGE |      1 |  42408 |  42408 | 00:00:00.02 |    1980 |      1 |     1 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("ORD_YM"='201201')

[세션 통계 — DELETE + COMMIT 한 번]
  지운 행                    42,408
  db block changes           약 170,000
  redo size                  약 26 MB
  undo change vector size    약 14 MB
  경과                       약 0.4 초

[정리 뒤 — 같은 칸 · 같은 테이블]
  SELECT COUNT(*) FROM ORD_RANGE PARTITION (P201201)    0 행 · Buffers 1,980 (그대로)
  P201201 세그먼트                                      16 MB (그대로)
  전체 칸을 읽는 조회 (분기 리포트 · SUBSTR 조건)        47,460 → 47,460

문제

이 DELETE의 비용을 셋(행마다 하는 일, redo/undo, 공간)으로 나눠 위 숫자로 설명하고, 지운 칸이 왜 그대로 무거운지 쓰시오.
같은 정리를 파티션 DDL로 바꾸고, 글로벌 인덱스와 영수증 재조회가 어떻게 되는지 SQL과 함께 설명하시오.
DROP과 TRUNCATE(보관이 필요할 때의 방법까지)를 비교하고, 파티션 DDL로 바꿀 때의 부작용과 확인할 것을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- (보관 규정이 있으면 먼저) 칸을 빈 보관 테이블과 맞바꿔 빼낸다 — 데이터는 옮기지 않고 사전만 바꾼다
--   CREATE TABLE ORD_ARC_201201 FOR EXCHANGE WITH TABLE ORD_RANGE;
--   ALTER TABLE ORD_RANGE EXCHANGE PARTITION P201201 WITH TABLE ORD_ARC_201201 UPDATE GLOBAL INDEXES;

-- 보관 기간이 지난 달 : 행을 지우지 않고 칸을 통째로 들어낸다 · 글로벌 인덱스는 같이 고친다
ALTER TABLE ORD_RANGE DROP PARTITION P201201 UPDATE GLOBAL INDEXES;

-- 확인 : 글로벌 인덱스 상태 · 남은 칸과 경계
SELECT index_name, status, orphaned_entries
  FROM user_indexes
 WHERE table_name = 'ORD_RANGE';
SELECT partition_name, high_value
  FROM user_tab_partitions
 WHERE table_name = 'ORD_RANGE'
 ORDER BY partition_position;

개선 후 실행계획 학습용 예시 · 교재 14화 규모

xplan

[세션 통계 — DROP PARTITION P201201 UPDATE GLOBAL INDEXES]
  redo size                  약 0.01 MB
  undo change vector size    약 0.01 MB
  db block changes           약 100
  경과                       약 0.04 초

[정리 뒤]
  P201201 세그먼트 16 MB · 로컬 인덱스 두 칸         사라짐 (공간 반납)
  전체 칸을 읽는 조회                                 47,460 → 약 45,480 (데이터 칸 24 → 23)
  ORD_RANGE_GX01                                     VALID · ORPHANED_ENTRIES = YES (지운 칸의 키는 나중에 정리)
  영수증 재조회 (ORD_NO 하나)                         4 → 4

[UPDATE GLOBAL INDEXES 를 빼먹었다면]
  ORD_RANGE_GX01                                     UNUSABLE
  영수증 재조회                                       에러 없이 전체 칸 스캔 (교재 14화 같은 모양 · 3 → 3,505)
  오늘 밤 주문 적재 (INSERT)                          ORA-01502 (유니크 인덱스는 건너뛰지 못한다)

모범 답안 서술

DELETE의 비용은 셋으로 나눌 수 있다. ① 행마다 하는 일: 칸을 고르는 일(PARTITION RANGE SINGLE, 1,980블록)은 싸지만, 42,408행마다 테이블 행 하나와 인덱스 키 셋(로컬 둘, 글로벌 하나)을 지운다. db block changes 약 17만과 DELETE 줄의 Buffers 172K가 그 일이다. ② redo와 undo: 지우기 전 행과 키를 undo에 적고(약 14MB) 그 변경을 다시 redo에 적는다(약 26MB). 운영은 ARCHIVELOG라서 그 redo가 매달 아카이브로 나간다. ③ 공간: DELETE는 행만 지우고 칸의 고수위선(분필 선)과 16MB는 그대로 둔다. 빈 칸이라도 전체 스캔은 분필 선까지 읽으므로 전체 칸 조회는 47,460 → 47,460으로 그대로이고, 지운 칸 하나만 세어도 0행에 1,980블록을 읽는다. 파티션 테이블은 칸째 들어낼 수 있다. ALTER TABLE ORD_RANGE DROP PARTITION P201201 UPDATE GLOBAL INDEXES는 행을 하나씩 지우지 않고 사전에서 칸을 떼어 낸다. 로컬 인덱스의 그 칸도 같이 사라진다. redo와 undo는 약 0.01MB, 블록 변경은 약 100, 경과 시간은 0.04초이고, 세그먼트 16MB를 반납해 전체 칸 조회는 약 45,480으로 준다. 글로벌 인덱스 ORD_RANGE_GX01에는 그 칸 4만 행의 키가 들어 있다. UPDATE GLOBAL INDEXES를 빼면 인덱스가 UNUSABLE이 된다. 그러면 영수증 재조회는 에러 없이 전체 칸 스캔으로 떨어지고(4 → 수만 블록), 유니크 인덱스라서 오늘 밤 주문 적재(INSERT)는 ORA-01502로 멈춘다. 이 절을 붙이면 인덱스는 VALID로 남는다(23ai는 지운 키를 고아 항목으로 두었다가 나중에 정리하므로 DDL 자체는 싸다). DROP은 칸 이름과 경계까지 없앤다. TRUNCATE PARTITION은 칸을 남기고 비운다(분필 선이 내려가고 경계는 유지된다). 같은 칸에 다시 적재할 일이 있으면 TRUNCATE를 쓴다. 둘 다 DDL이라 롤백이 없다. 보관 규정이 있으면 먼저 EXCHANGE PARTITION으로 빈 보관 테이블과 맞바꿔 빼내고(데이터 이동 없음), 그다음 빈 칸을 DROP한다. 바꾸기 전에 확인할 것은 세 가지다. 대상 칸이 정말 그 달인지(HIGH_VALUE), 정리 시각에 그 테이블을 오래 읽는 쿼리가 없는지(DDL은 잠금을 잡고, 읽던 칸이 사라지면 진행 중인 쿼리가 실패할 수 있다), 새 달이 P_DEFAULT (MAXVALUE) 한 칸에 쌓이지 않게 다음 달 칸을 미리 만드는지 확인한다. 오늘 밤 적재 전에 SPLIT PARTITION으로 P201401을 떼어 내거나 INTERVAL 파티션으로 바꾼다.

해설

교재 14화 실측(같은 모양, 한빛 T_ORD_LOG 한 달 17,235행, 인덱스 1개): DELETE는 redo 4.84MB, undo 2.32MB, 블록 변경 36,691이었고, 그 뒤 전체 스캔은 3,602 → 3,602로 그대로였다. DROP PARTITION은 redo 0.01MB, 블록 변경 96이었고 전체 스캔은 3,630 → 3,505로 줄었다. 이 문제의 숫자는 그 실측을 행 수와 인덱스 수에 맞춰 키운 어림값이다.
SHRINK로 매달 청소하면 분필 선은 내려가지만 남은 행 전부를 옮겨 쓴다(교재 14화: 지운 한 달보다 3.7배 비싼 redo). 칸째 정리하면 청소할 일이 생기지 않는다.
UPDATE GLOBAL INDEXES의 비용은 버전마다 다르다. 예전 방식은 DDL이 그 칸의 키를 하나하나 지워서 redo가 다시 커진다. 23ai는 고아 항목으로 남기고 나중에 정리한다(비동기 글로벌 인덱스 유지보수). 다른 버전에서는 다시 재 본다.
DELETE가 맞는 때: 칸 경계와 맞지 않는 정리(예: '취소 주문만 3개월 뒤 삭제')는 칸째 들어낼 수 없다. 그때는 DELETE와 주기적 청소를 쓰거나 파티션 키를 다시 설계한다.
교재 05화 「블록이라는 그릇」에서 본 것처럼, 전체 스캔이 읽는 범위는 '행이 어디까지 있나'가 아니라 '어디까지 써 봤나'로 정해진다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 교재 14화 규모 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 교재 14화 규모 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황