2년 치 주문을 지웠는데 전체 조회는 그대로 22,422블록

실전DELETE버거428 / 700

상황

무대는 버거원 POS 본사 DB다. 2012 ~ 2013년 두 해의 주문을 DW로 이관했고, 운영에는 2013년 12월 한 달만 남기기로 했다(매장 화면은 이번 달만 본다). 개발팀이 토요일 밤에 반영할 작업을 넘겼고, 테스트 DB에서 ORD 쪽을 먼저 돌려 봤다. 매장 정산 담당: "지운 뒤에 매장별 집계 화면을 눌러 봤는데 하나도 안 빨라졌어요." 반영 창은 토요일 23:00 ~ 01:00이고, 그 시간에는 매장 영업이 없다. 앞으로 매달 지난달을 이관하고 지울 계획이다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_DT VARCHAR2(8) '20120101' ~ '20131231' · 날짜순 적재 · 2013년 12월 42,408행(4.2%)
인덱스는 ORD_PK (ORD_NO)와 ORD_X01 (ORD_DT, ORD_HMS)이다. ORD를 가리키는 FK는 없다. POS 연동 계정 POS_IF에 SELECT와 INSERT 권한이 있다.
ORD_ITEM 주문상품: 2,472,717행 · 55,265블록. 같은 방식으로 지운다(12월분 약 10만 행).
매장별 집계 화면은 SELECT SHOP_NO, COUNT(*) FROM ORD GROUP BY SHOP_NO다(인덱스 없이 FULL).
파티션 옵션은 있다(14화의 ORD_RANGE가 월별 파티션 복사본이다).

원본 SQL

sql

-- 토요일 23:00 반영 예정 (개발팀)
DELETE FROM ORD      WHERE ORD_DT < '20131201';
DELETE FROM ORD_ITEM WHERE ORD_DT < '20131201';
COMMIT;

실행계획 학습용 예시

xplan

[세션 통계 — 테스트 DB · DELETE FROM ORD WHERE ORD_DT < '20131201' 한 번 · 인덱스 2개]
  경과 시간                  7분 12초 (그중 대부분이 리두 로그 교체 대기)
  삭제 행                    957,592
  session logical reads      약 6,700,000
  db block changes           약 5,700,000
  redo size                  약 740 MB
  undo change vector size    약 360 MB
  user commits               1

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |      |      1 |        |      0 |00:07:11.84 |   6702K |
|   1 |  DELETE            | ORD  |      1 |        |      0 |00:07:11.84 |   6702K |
|*  2 |   TABLE ACCESS FULL| ORD  |      1 |    957K|    957K|00:00:00.61 |   22422 |
-------------------------------------------------------------------------------------
   2 - filter("ORD_DT"<'20131201')

[매장별 집계 화면 — 같은 문장 · 지우기 전과 뒤 (통계는 지운 뒤 다시 모았다)]
                 TABLE ACCESS FULL A-Rows    Buffers    USER_TABLES
  지우기 전      1,000,000                   22,426     NUM_ROWS 1,000,000 · BLOCKS 22,422
  DELETE 뒤         42,408                   22,426     NUM_ROWS    42,408 · BLOCKS 22,422

문제

이 DELETE의 비용(redo, undo, 시간)이 행마다 무엇을 쓰는 데서 나오는지 쓰고, 지운 뒤에도 집계 화면이 22,426블록인 이유를 설명하시오.
같은 결과(12월분만 남는다)를 내는 다른 방법을 SQL로 순서대로 작성하시오. 그 방법에서 원래 테이블로부터 '따라오지 않는 것'과 작업 창 동안 멈춰야 하는 것도 적으시오.
앞으로 매달 반복한다면 무엇을 바꿀지 제안하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 토요일 23:00 작업 창 · 매장 영업이 끝나고 POS 입력을 멈춘 뒤 (본사 공지)
-- ① 남길 한 달만 새 테이블로 — 직접 경로 · undo 거의 없음 · 약 950 블록
CREATE TABLE ORD_N AS
SELECT * FROM ORD WHERE ORD_DT >= '20131201';

-- ② 원본과 같은 인덱스 · 제약 (원본 DDL 은 미리 DBMS_METADATA.GET_DDL 로 뽑아 둔다)
CREATE UNIQUE INDEX ORD_N_PK ON ORD_N (ORD_NO);
ALTER TABLE ORD_N ADD CONSTRAINT ORD_N_PK PRIMARY KEY (ORD_NO) USING INDEX ORD_N_PK;
CREATE INDEX ORD_N_X01 ON ORD_N (ORD_DT, ORD_HMS);

-- ③ 바꿔 끼운다 — 옛 테이블은 ORD_OLD 로 남겨 되돌릴 길을 둔다
ALTER TABLE ORD RENAME TO ORD_OLD;
ALTER TABLE ORD_OLD RENAME CONSTRAINT ORD_PK TO ORD_OLD_PK;
ALTER INDEX ORD_PK  RENAME TO ORD_OLD_PK;
ALTER INDEX ORD_X01 RENAME TO ORD_OLD_X01;
ALTER TABLE ORD_N RENAME TO ORD;
ALTER TABLE ORD RENAME CONSTRAINT ORD_N_PK TO ORD_PK;
ALTER INDEX ORD_N_PK  RENAME TO ORD_PK;
ALTER INDEX ORD_N_X01 RENAME TO ORD_X01;

-- ④ 따라오지 않는 것 — 권한 · 통계 (원본의 권한 목록을 미리 뽑아 둔 대로)
GRANT SELECT, INSERT ON ORD TO POS_IF;
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORD')

-- ⑤ 건수 대조 (42,408) · 집계 화면 확인 뒤 POS 재개 · ORD_OLD 는 한 주 뒤 DROP TABLE ORD_OLD PURGE
-- ORD_ITEM 도 같은 순서로 (2,472,717 → 약 10만)

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

xplan

[같은 작업 창 — 남길 행을 옮겨 바꿔 끼운 뒤 (학습용 예시)]
  CREATE TABLE … AS SELECT      42,408 행 · 원본 FULL 한 번 (22,422 블록) · 경과 2초 안팎 · undo 거의 없음
  인덱스 둘 · PK 다시 만들기     1초 안팎 (4만 행)
  매장별 집계 화면               TABLE ACCESS FULL  A-Rows 42,408 · Buffers 22,426 → 약 955
  USER_TABLES                    NUM_ROWS 42,408 · BLOCKS 약 950

모범 답안 서술

DELETE는 행마다 세 가지를 고친다. 테이블의 행을 지우고, 인덱스 둘(ORD_PK, ORD_X01)에서 키를 하나씩 빼고, 그 셋 모두의 되돌림 정보(undo)와 변경 기록(redo)을 남긴다. 957,592행이니 redo 약 740MB, undo 약 360MB, 7분이 든다. 지울 행이 95%를 넘으면 일의 거의 전부가 '버리는 행'의 기록이다. 그리고 DELETE는 고수위선(HWM)을 내리지 않는다. 행은 사라졌지만 블록은 '써 본 블록'으로 남으므로 FULL은 여전히 HWM까지 22,422블록을 다 연다. 통계의 NUM_ROWS는 42,408인데 BLOCKS는 22,422 그대로다. 그래서 집계 화면도 22,426 → 22,426으로 변하지 않는다. 남길 5%를 CTAS로 새 테이블에 옮기고(직접 경로, undo 거의 없음, 약 950블록), 인덱스와 제약을 다시 만든 뒤 이름을 바꿔 끼운다. 집계 화면의 Buffers는 22,426 → 약 955로 준다. 옛 테이블을 ORD_OLD로 남겨 두면 문제가 생겼을 때 이름만 되돌리면 된다. 대가도 있다. 새 테이블에는 권한(GRANT), 제약 이름, 인덱스, 통계, 트리거가 따라오지 않으므로 원본 DDL을 미리 DBMS_METADATA로 뽑아 하나씩 다시 만든다. ORD를 참조하는 PL/SQL은 INVALID가 됐다가 다시 컴파일된다. 그리고 CTAS를 시작한 뒤 들어온 주문은 새 테이블에 없으므로 작업 창 동안 POS 입력을 멈춰야 한다. 이 다운타임은 영업이 없는 토요일 밤이라 감당할 수 있다. 매달 반복할 일이라면 ORD를 ORD_DT 기준 월별 파티션으로 바꾼다. 지난달은 DELETE가 아니라 ALTER TABLE … DROP PARTITION 한 줄로 칸막이째 버린다. redo가 거의 없고 HWM 문제도 생기지 않는다(교재 14화: DROP PARTITION redo 0.01MB). 글로벌 인덱스가 있으면 UPDATE INDEXES를 붙이거나 LOCAL 인덱스로 만든다.

해설

다른 정답: 남길 행을 사본에 담고 TRUNCATE TABLE ORD → INSERT /*+ APPEND */ INTO ORD SELECT * FROM 사본 순서로 처리한다. 테이블 객체가 그대로라서 권한, 제약, 의존 객체가 깨지지 않는다. 대신 TRUNCATE는 되돌릴 수 없으므로 사본의 건수를 확인한 뒤에 한다. 사본을 NOLOGGING으로 만들었다면 TRUNCATE하는 순간 사본이 유일본이 된다(백업 전에는 쓰지 않는다).
DELETE 뒤 SHRINK: ALTER TABLE ORD ENABLE ROW MOVEMENT와 SHRINK SPACE로도 HWM을 내릴 수 있고, 서비스 중에도 할 수 있다. 그러나 남은 행을 다시 쓰는 redo가 또 들고, DELETE의 740MB는 이미 치른 뒤다(교재 05화 3,602 → 183, 14화 SHRINK redo 17.96MB).
나눠 지우기(10만 건씩 DELETE + COMMIT)는 한 트랜잭션의 undo만 줄인다. redo 총량과 HWM은 그대로다.
파티션의 대가: 파티션 키(ORD_DT)를 조건에 가공 없이 써야 프루닝된다. TRUNCATE 뒤 APPEND로 다시 채우면 칸마다 첫 익스텐트가 크게 남을 수 있다(교재 14화: 한 칸 161 → 1,169, INITIAL을 적는다). 유상 옵션이라는 점도 대가다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황