건수는 그대로인데 1년 새 1.5배가 된 주문 이력, 보관 기간 단축도 DELETE 한 문장으로 올라왔다

실전DELETE한빛유통469 / 700

상황

주문상태이력 T_ORD_LOG는 매일 밤 그날 이력을 더하고, 매달 첫 일요일에 가장 오래된 한 달을 지운다. 보관 기간이 900일이라 건수는 50만 행 안팎에서 그대로인데, 1년 새 테이블이 1.5배가 됐고 상담 화면의 이력 집계도 그만큼 느려졌다. 이번 달에는 보관 기간을 900일에서 365일로 줄이라는 지침이 내려왔고, 개발팀은 DELETE 한 문장을 올렸다. 박팀장: "세 작업을 각각 어떻게 할지 숫자와 함께 정리해 주세요."

테이블과 인덱스

T_ORD_LOG 주문상태이력(교재 05화 생성식): LOG_ID · ORD_SEQ · LOG_DT DATE · LOG_ST · LOG_MSG · LOG_DTL · 500,214행 · 하루 약 555행이 LOG_DT 순으로 쌓인다(블록당 약 139행) · 인덱스 IX_ORD_LOG_SEQ (ORD_SEQ) 하나(상담 화면용) · FK와 트리거는 없다.
처음 지었을 때는 3,602블록이었다. 지금 USER_TABLES.BLOCKS는 5,514이고, 그중 행이 하나도 없는 블록이 약 1,600개다(지난 13개월에 지운 달들의 자리).
T_ORD_LOG_STG: 그날 상태 변경 이력, 약 555행. 주문계에서 받는다.
상담 화면(09:00 ~ 18:00)은 T_ORD_LOG를 읽기만 하고, 쓰는 것은 야간 배치뿐이다. 일요일 22:00 ~ 04:00에 체인 작업 창이 있다.
파티셔닝은 유상 옵션이라 아직 승인받지 못했다(2021년 강도현 커밋).

원본 SQL

sql

-- (가) 매일 23:00 · 그날 이력 더하기
INSERT /*+ APPEND */ INTO T_ORD_LOG
SELECT * FROM T_ORD_LOG_STG;
COMMIT;

-- (나) 매달 첫 일요일 · 가장 오래된 한 달 지우기
DELETE FROM T_ORD_LOG
 WHERE LOG_DT < TRUNC(SYSDATE) - 900;
COMMIT;

-- (다) 이번 한 번 · 보관 기간 900일 → 365일 (개발팀 제출)
DELETE FROM T_ORD_LOG
 WHERE LOG_DT < TRUNC(SYSDATE) - 365;
COMMIT;

실행계획 학습용 예시 · (나)의 숫자는 교재 14화 실측

xplan

[(나) 지난달 — DELETE 한 달 (교재 14화 실측 · 17,235행 · 인덱스 1개)]
  redo size                  4.84 MB
  undo change vector size    2.32 MB
  db block changes           36,691
  경과                       0.06 초
  그 뒤 전체 스캔            그대로 (고수위선 아래 블록은 줄지 않는다)

[T_ORD_LOG 크기 — 지난 13개월 (USER_TABLES · 매달 첫 일요일 수집)]
  시점        NUM_ROWS    BLOCKS   상담 화면 이력 집계(FULL) Buffers
  작년 11월    500,000     3,602    3,606
  올해 5월     500,301     4,566    4,570
  이번 달      500,214     5,514    5,518

[(다) 개발팀 추정 — 지울 행 296,925 (59%) · 한 달 실측의 17.2배로 어림]
  redo 약 83 MB · undo 약 40 MB · db block changes 약 63만 · 고수위선 5,514 그대로

문제

건수가 그대로인데 테이블이 3,602블록에서 5,514블록으로 커진 이유를 (가)와 (나)의 조합으로 설명하시오.
(가), (나), (다)를 각각 어떻게 처리할지 SQL로 작성하고, 고른 근거를 넣거나 지울 행의 비율과 위 숫자로 쓰시오.
(다)를 처리하는 동안과 처리한 뒤에 확인해야 할 것과 그 방법의 대가를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- (가) 매일 23:00 · 555행(테이블의 0.1%)은 일반 INSERT — 지운 달의 빈 블록을 다시 쓰고, 직접 경로의 이득도 없다
INSERT INTO T_ORD_LOG
SELECT * FROM T_ORD_LOG_STG;
COMMIT;

-- (나) 매달 · 한 달(전체의 3.4%)은 DELETE 그대로 — redo 4.84MB · 0.06초. 비운 자리는 (가)가 다시 쓴다
DELETE FROM T_ORD_LOG
 WHERE LOG_DT < TRUNC(SYSDATE) - 900;          -- (다) 뒤에는 365
COMMIT;

-- (다) 이번 한 번 · 일요일 작업 창 · 59%를 지우는 대신 41%를 옮겨 담고, 테이블을 비운 뒤 되돌려 넣는다
-- ① 남길 행을 사본으로 (CTAS · 직접 경로) — NOLOGGING 은 쓰지 않는다: ③ 뒤에는 이 사본이 유일본이다
CREATE TABLE T_ORD_LOG_KEEP AS
SELECT * FROM T_ORD_LOG WHERE LOG_DT >= TRUNC(SYSDATE) - 365;

-- ② 건수 대조 — 둘 다 203,289 행이어야 다음으로 간다
SELECT COUNT(*) FROM T_ORD_LOG_KEEP;
SELECT COUNT(*) FROM T_ORD_LOG WHERE LOG_DT >= TRUNC(SYSDATE) - 365;

-- ③ 비우고(고수위선도 처음으로) 인덱스를 끈 뒤 날짜 순으로 되돌려 넣고 다시 짓는다
TRUNCATE TABLE T_ORD_LOG;
ALTER INDEX IX_ORD_LOG_SEQ UNUSABLE;
INSERT /*+ APPEND */ INTO T_ORD_LOG
SELECT * FROM T_ORD_LOG_KEEP ORDER BY LOG_DT;
COMMIT;
ALTER INDEX IX_ORD_LOG_SEQ REBUILD;
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_ORD_LOG')

-- ④ 건수 · 인덱스 상태 · 상담 화면을 확인하고, 한 주 두었다가 DROP TABLE T_ORD_LOG_KEEP PURGE

개선 후 실행계획 학습용 예시 · (나)의 숫자는 교재 14화 실측

xplan

[(다) 같은 작업 창 · 학습용 어림 (연습 DB · 14화 실측 규모)]
  단계                                         redo       경과
  ① CTAS 203,289행                             약 0.1 MB  약 0.3 초
  ③ TRUNCATE · UNUSABLE · APPEND · REBUILD      약 0.5 MB  약 1 초
  (비교) DELETE 296,925행 한 문장              약 83 MB   (고수위선 5,514 그대로)

[그 뒤]
  T_ORD_LOG   NUM_ROWS 203,289 · BLOCKS 약 1,465 · IX_ORD_LOG_SEQ VALID
  상담 화면 이력 집계   Buffers 5,518 → 약 1,470
  (가)를 일반 INSERT 로 바꾼 뒤 석 달   BLOCKS 1,465 → 1,470 안팎 (매달 지운 자리를 다음 달 이력이 다시 쓴다)

모범 답안 서술

테이블이 커진 것은 (가)와 (나)가 서로의 공간을 쓰지 않기 때문이다. (가)의 /*+ APPEND */는 직접 경로 적재라서 고수위선 위의 새 블록에만 쓰고, 고수위선 아래의 빈 블록은 거들떠보지 않는다. (나)의 DELETE는 가장 오래된 한 달을 지워 고수위선 아래에 빈 블록(한 달 약 124블록)을 만들지만 고수위선은 내리지 않는다. 비운 자리는 아무도 다시 쓰지 않고, 매달 새 이력은 고수위선 위로 쌓인다. 그래서 건수는 50만 행 그대로인데 블록은 한 달에 약 147블록씩 늘어 13개월 만에 3,602 → 5,514가 됐고(빈 블록 약 1,600), 전체 스캔인 상담 화면 집계도 3,606 → 5,518로 늘었다. 세 작업은 넣거나 지우는 행의 비율로 방법을 고른다. (가)는 하루 555행, 테이블의 0.1%다. 이 정도에서는 직접 경로의 이득이 없으므로 APPEND를 빼고 일반 INSERT로 넣는다. 일반 INSERT는 고수위선 아래의 빈 블록부터 다시 쓰므로 (나)가 비운 자리가 채워진다. redo는 하루 0.2MB 안팎으로 조금 늘 뿐이다. (나)는 한 달, 전체의 3.4%다. 교재 14화 실측으로 DELETE가 redo 4.84MB, 0.06초이므로 DELETE 그대로 둔다. (가)를 일반 INSERT로 바꾸면 비운 자리를 다음 달 이력이 다시 쓰므로 테이블이 더 커지지 않는다. 월별 파티션으로 바꾸면 DROP PARTITION 한 줄(redo 0.01MB)이 가장 싸지만 유상 옵션이라 승인이 필요하다. 이 표의 숫자를 예산 요청에 붙인다. (다)는 296,925행, 59%를 지운다. DELETE 한 문장이면 redo 약 83MB, undo 약 40MB가 들고, 그래도 고수위선은 5,514에 그대로 남는다. 남길 행은 41%(203,289행)뿐이므로 지우지 말고 옮긴다. 남길 행을 CTAS로 사본에 담고, 건수를 원본과 대조한 뒤, T_ORD_LOG를 TRUNCATE해 고수위선을 처음으로 되돌린다. 인덱스를 끈 채 /*+ APPEND */로 날짜 순으로 되돌려 넣고 인덱스를 다시 짓는다. redo는 1MB 안쪽이고, 테이블은 약 1,465블록이 되어 상담 화면 집계가 5,518 → 약 1,470으로 준다. CTAS 뒤 이름을 바꿔 끼우는 대신 TRUNCATE 뒤 되돌려 넣는 쪽을 고른 것은, 테이블 객체가 그대로라 권한, 인덱스 정의, 이 테이블을 쓰는 PL/SQL이 깨지지 않기 때문이다. (다)의 대가와 확인할 것은 이렇다. TRUNCATE는 되돌릴 수 없으므로 ② 사본 건수 대조를 통과한 뒤에만 TRUNCATE한다. TRUNCATE하는 순간부터 사본 T_ORD_LOG_KEEP이 유일본이므로 사본은 NOLOGGING으로 만들지 않고, 되돌려 넣은 뒤에도 한 주 남겨 둔다. 작업하는 동안 T_ORD_LOG는 비어 있으므로 상담 화면이 쓰지 않는 일요일 작업 창에 하고, 같은 창의 (가), (나)와 겹치지 않게 순서를 정한다. 끝나면 건수(203,289), 인덱스 상태(VALID), 통계, 상담 화면의 실행계획을 확인한다.

해설

다른 정답: (다)를 CTAS + 이름 바꾸기(DML-003)로 해도 같은 크기가 된다. 대신 권한, 시노님, 인덱스와 제약 이름, 통계를 새 테이블에 다시 챙겨야 하고 이 테이블을 쓰는 PL/SQL이 INVALID가 된다. TRUNCATE 뒤 되돌려 넣기는 그 일이 없는 대신 TRUNCATE 순간부터 사본이 유일본이 된다.
DELETE 뒤 SHRINK SPACE는 서비스 중에도 할 수 있지만, 59%를 지우는 redo(약 83MB)에 남은 행을 옮기는 redo가 또 붙는다(교재 14화: 한 달 SHRINK가 DELETE의 3.7배).
ORDER BY LOG_DT로 되돌려 넣으면 이력이 날짜 순으로 쌓인 지금의 모양이 유지된다. 그래야 다음 달 (나)가 지우는 한 달이 연속된 블록에 모여 있어, 통째로 빈 블록이 생기고 (가)가 그 블록을 깔끔하게 다시 쓴다.
직접 경로 적재와 행 단위 DELETE를 같은 테이블에 함께 쓰면 테이블이 매달 커진다(교재 14화 §4.2: DELETE 뒤 되채움 일반 6,016 · APPEND 7,990블록). 적재 방식을 배치 머리에 적어 두는 이유다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · (나)의 숫자는 교재 14화 실측 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · (나)의 숫자는 교재 14화 실측 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황