PART-038

보관 규정이 바뀐 뒤 칸째 들어내던 주문상태이력 정리가 다시 행마다 지우는 DELETE가 되었다

실전DDL한빛유통413 / 700

상황

주문상태이력 T_ORD_LOG_P14는 월별 칸으로 나뉘어 있어서, 예전 규정(전부 24개월 보관)대로라면 매달 가장 오래된 칸을 DROP PARTITION 한 줄로 들어내면 됐다. 올해 1월 감사팀 규정이 바뀌었다. 결제(S)와 취소(X) 이력은 5년, 나머지 셋(접수 R, 배송 D, 완료 C)은 6개월만 보관한다. 조민우가 월 정리를 DELETE로 바꿨고, 첫 달에는 18개월 치 약 18만 행을 지웠다. 그 뒤로는 매달 1일 약 1만 행을 지운다. 오과장: "지운 칸이 가벼워지지도 않고, 5년 동안은 칸을 하나도 못 들어낸다. 설계를 다시 하자." 아래는 7월 1일의 월 정리다.

테이블과 인덱스

T_ORD_LOG_P14 주문상태이력(교재 14화): 교재 05화의 T_ORD_LOG 500,000행(2023-01-01 ~ 2025-06-19, 900일, 3,602블록)을 PARTITION BY RANGE (LOG_DT) 월별 30칸과 PMAX로 나눠 CTAS로 지었다. 한 달은 약 17,000행, FULL로 약 120 ~ 124블록이다.
LOG_ST는 R · S · D · C · X 다섯 가지가 20%씩이다. 이력 행은 한 번 쓰면 고치지 않는다.
GLOBAL 인덱스 IX_LOG_P14_SEQ (ORD_SEQ): 「주문 이력」 화면용(주문 하나에 이력 약 5행, 하루 수천 번)
그 밖의 SQL: 「월 상태 통계」 한 달 GROUP BY LOG_ST(월초 몇 번) · 「감사 추출」 분기마다 결제와 취소 이력(LOG_ST IN ('S', 'X'))
운영은 ARCHIVELOG다. 23ai라서 UPDATE GLOBAL INDEXES는 지운 키를 고아 항목으로 남겼다가 나중에 정리한다.

원본 SQL

sql

-- 월 정리 (매월 1일 · :v_mon = 이번 달 첫날 · 7개월 전 달의 R · D · C 를 지운다)
DELETE FROM T_ORD_LOG_P14
 WHERE LOG_DT >= ADD_MONTHS(:v_mon, -7)
   AND LOG_DT <  ADD_MONTHS(:v_mon, -6)
   AND LOG_ST IN ('R', 'D', 'C');
COMMIT;

-- 「감사 추출」 (분기마다 · 2024년 4분기)
SELECT ORD_SEQ, LOG_DT, LOG_ST, LOG_MSG
  FROM T_ORD_LOG_P14
 WHERE LOG_ST IN ('S', 'X')
   AND LOG_DT >= DATE '2024-10-01'
   AND LOG_DT <  DATE '2025-01-01';

-- 「주문 이력」 (화면 · 하루 수천 번)
SELECT LOG_DT, LOG_ST, LOG_MSG FROM T_ORD_LOG_P14 WHERE ORD_SEQ = :ord_seq ORDER BY LOG_DT;

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

xplan

[월 정리 · 2025-07-01 · 2024년 12월의 R · D · C]
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT            |               |      1 |        |      0 | 00:00:00.34 |     31K |        |       |
|   1 |  DELETE                     | T_ORD_LOG_P14 |      1 |        |      0 | 00:00:00.34 |     31K |        |       |
|*  2 |   FILTER                    |               |      1 |        |  10333 | 00:00:00.02 |     124 |        |       |
|   3 |    PARTITION RANGE ITERATOR |               |      1 |  10320 |  10333 | 00:00:00.02 |     124 |    KEY |   KEY |
|*  4 |     TABLE ACCESS FULL       | T_ORD_LOG_P14 |      1 |  10320 |  10333 | 00:00:00.02 |     124 |    KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ADD_MONTHS(:V_MON,-7)<ADD_MONTHS(:V_MON,-6))
   4 - filter((INTERNAL_FUNCTION("LOG_ST") AND "LOG_DT">=ADD_MONTHS(:V_MON,-7) AND "LOG_DT"<ADD_MONTHS(:V_MON,-6)))

[세션 통계 · 위 DELETE + COMMIT]
  지운 행 10,333 · 글로벌 인덱스 키 10,333개 삭제 · redo 약 2.9MB · undo 약 1.4MB

[그 뒤 · 2024년 12월 칸]
  남은 행 6,889 (S · X) · FULL Buffers 124 그대로 · 세그먼트 크기 그대로

[「감사 추출」 · 2024년 4분기]
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |               |      1 |        |  20444 | 00:00:00.03 |     368 |        |       |
|   1 |  PARTITION RANGE ITERATOR |               |      1 |  20444 |  20444 | 00:00:00.03 |     368 |     22 |    24 |
|*  2 |   TABLE ACCESS FULL       | T_ORD_LOG_P14 |      3 |  20444 |  20444 | 00:00:00.03 |     368 |     22 |    24 |
-----------------------------------------------------------------------------------------------------------------------

   2 - filter(("LOG_ST"='S' OR "LOG_ST"='X'))

[「주문 이력」]  TABLE ACCESS BY GLOBAL INDEX ROWID · INDEX RANGE SCAN IX_LOG_P14_SEQ · 8블록

문제

보관 규정이 바뀐 뒤 칸째 들어낼 수 없게 된 까닭과, 지금 월 정리 DELETE의 비용을 위 숫자로 설명하시오.
두 보관 규칙을 모두 DDL 한 줄씩으로 지킬 수 있게 테이블을 다시 설계하고, 매달 정리와 5년 뒤 정리를 SQL로 쓰시오.
다른 설계 둘(상태로 먼저 나누고 그 안을 월로 나누기, 테이블을 둘로 나누기)과 비교하고, 고른 설계의 대가와 확인할 것을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 월(INTERVAL)을 먼저 나누고, 그 안을 보관 규칙별로 나눈다 (조각 하나 = 들어내는 단위 하나)
CREATE TABLE T_ORD_LOG_RL
STORAGE (INITIAL 64K)
PARTITION BY RANGE (LOG_DT) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
SUBPARTITION BY LIST (LOG_ST)
SUBPARTITION TEMPLATE
( SUBPARTITION SP_KEEP  VALUES ('S', 'X'),        -- 결제 · 취소 : 5년
  SUBPARTITION SP_SHORT VALUES ('R', 'D', 'C'),   -- 접수 · 배송 · 완료 : 6개월
  SUBPARTITION SP_ETC   VALUES (DEFAULT) )        -- 새 상태 코드 (보관 규칙을 정할 때까지)
( PARTITION P202301 VALUES LESS THAN (DATE '2023-02-01') )
AS SELECT * FROM T_ORD_LOG_P14;

-- 「주문 이력」용 : 주문번호로 찾는다 → 칸과 상관없는 GLOBAL
CREATE INDEX IX_ORD_LOG_RL_SEQ ON T_ORD_LOG_RL (ORD_SEQ);

-- 매월 1일 : 7개월 전 달의 짧은 보관 조각만 비운다 (행을 하나씩 지우지 않는다)
ALTER TABLE T_ORD_LOG_RL TRUNCATE SUBPARTITION FOR (DATE '2024-12-01', 'R')
  UPDATE GLOBAL INDEXES;

-- 5년 지난 달 (예: 2028년 2월 1일에 2023년 1월) : 달 전체를 들어낸다
ALTER TABLE T_ORD_LOG_RL DROP PARTITION FOR (DATE '2023-01-01')
  UPDATE GLOBAL INDEXES;

-- 확인 : 새 상태 코드가 DEFAULT 조각에 들어왔는가
SELECT LOG_ST, COUNT(*) FROM T_ORD_LOG_RL
 WHERE LOG_ST NOT IN ('S', 'X', 'R', 'D', 'C') GROUP BY LOG_ST;

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

xplan

[매월 1일 · TRUNCATE SUBPARTITION FOR (2024-12, 'R') UPDATE GLOBAL INDEXES]
  경과 약 0.05초 · redo 약 0.01MB · 그 조각의 세그먼트를 반납 (약 75블록 → 첫 익스텐트)
  IX_ORD_LOG_RL_SEQ  VALID · ORPHANED_ENTRIES = YES (지운 키는 나중에 정리)

[「감사 추출」 · 2024년 4분기]
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name         | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |              |      1 |        |  20444 | 00:00:00.01 |     154 |        |       |
|   1 |  PARTITION RANGE ITERATOR |              |      1 |  20444 |  20444 | 00:00:00.01 |     154 |     22 |    24 |
|   2 |   PARTITION LIST SINGLE   |              |      3 |  20444 |  20444 | 00:00:00.01 |     154 |      1 |     1 |
|   3 |    TABLE ACCESS FULL      | T_ORD_LOG_RL |      3 |  20444 |  20444 | 00:00:00.01 |     154 |        |       |
----------------------------------------------------------------------------------------------------------------------

  (실행 때 연 조각 — 2024년 10 ~ 12월 칸의 SP_KEEP 세 개 · 조건이 조각의 값 목록과 같아 filter 도 없다)

[「월 상태 통계」 · 2024년 11월]  RANGE SINGLE + LIST ALL · TABLE ACCESS FULL Starts 2 · 약 122블록
[「주문 이력」]  GLOBAL 인덱스 IX_ORD_LOG_RL_SEQ · 8블록 그대로
[세그먼트]  달마다 2개 (빈 SP_ETC 는 세그먼트가 없다) · 30달이면 60개

모범 답안 서술

칸째 들어낼 수 없게 된 까닭은 칸 안의 행이 서로 다른 보관 기간을 갖게 되었다는 데 있다. 월별 칸 하나에 6개월만 둘 R, D, C와 5년을 둘 S, X가 섞여 있어서, 6개월이 지난 달도 칸째 들어낼 수 없다(S, X를 남겨야 한다). 들어내는 단위(한 달의 R, D, C)와 칸(한 달 전체)이 어긋났다. 그래서 정리가 행마다 지우는 DELETE로 돌아갔고, 칸 전체는 5년 동안 한 번도 들어내지 못한다. 지금 DELETE의 비용은 위 숫자 그대로다. 칸을 고르는 일(KEY, 124블록)은 싸지만, 10,333행마다 테이블 행과 글로벌 인덱스 키를 하나씩 지우며 undo 약 1.4MB와 redo 약 2.9MB를 만든다(DELETE 줄 Buffers 31K). ARCHIVELOG라 그 redo가 매달 아카이브로 나간다. 그리고 DELETE는 고수위선을 내리지 않아서 지운 뒤에도 2024년 12월 칸은 124블록 그대로다. 5년 동안 칸마다 60%가 빈 블록으로 남는다(교재 05화). 들어내는 단위마다 조각이 따로 있으면 정리가 DDL 한 줄로 돌아온다. 월로 먼저 나누고(PARTITION BY RANGE (LOG_DT) INTERVAL (…)), 칸 안을 보관 규칙별 LIST 서브파티션으로 나눈다(SUBPARTITION BY LIST (LOG_ST), SP_KEEP은 S와 X, SP_SHORT는 R, D, C, 새 상태 코드는 SP_ETC). 매달 1일에는 7개월 전 달의 SP_SHORT 조각만 TRUNCATE SUBPARTITION FOR (DATE '2024-12-01', 'R')로 비운다. 서브파티션 이름은 INTERVAL 칸에서 저절로 붙으므로 값으로 조각을 지목한다. 5년이 지난 달은 DROP PARTITION FOR (…)로 달 전체를 들어낸다. 둘 다 UPDATE GLOBAL INDEXES를 붙여 「주문 이력」의 GLOBAL 인덱스를 살린다. 정리는 redo 약 0.01MB, 0.05초가 되고 조각의 세그먼트를 반납한다. 「주문 이력」 인덱스는 GLOBAL로 둔다. 주문번호로 찾는 조회라 칸을 고를 단서가 없어서, LOCAL이면 칸 30개 × 조각 2개를 모두 뒤진다(약 60번). 「감사 추출」은 칸 셋에서 SP_KEEP 조각만 읽어 368 → 약 154블록으로 준다. 「월 상태 통계」는 조각 둘을 읽어 약 122블록으로 그대로다. 다른 설계 둘과 견주면 이렇다. 상태로 먼저 나누고(LIST) 그 안을 월로 나누면(RANGE 서브파티션) 같은 정리를 할 수 있지만, 서브파티션 단계에는 INTERVAL을 쓸 수 없어서 달마다 두 칸에 월 조각을 손으로 더해야 한다(PART-028의 ORA-14400이 그 결과다). 테이블을 둘로 나누면(5년용, 6개월용) 각각 월별 칸으로 단순하게 정리할 수 있지만, 이력을 쓰는 프로그램이 상태마다 다른 테이블에 넣어야 하고 「주문 이력」은 두 테이블을 UNION ALL로 읽어야 한다. 그래서 월 × 보관 규칙 복합 파티션을 권한다. 대가와 확인할 것은 이렇다. 세그먼트가 달마다 1개에서 2개로 늘어난다(5년이면 120개, 첫 익스텐트는 INITIAL 64K). 새 상태 코드가 생기면 SP_ETC 조각에 들어가므로, 확인 쿼리로 들어온 값이 있는지 보고 보관 규칙을 정해 SET SUBPARTITION TEMPLATE을 고친다. 23ai가 아닌 버전에서는 UPDATE GLOBAL INDEXES가 그 자리에서 키를 지워 redo가 다시 커지므로 다시 잰다.

해설

상태 코드로 나눠도 되는 경우: 이력 행은 한 번 쓰면 고치지 않으므로 LOG_ST가 칸 기준이 되어도 행이 칸을 옮길 일이 없다. 결제 때 상태가 바뀌는 주문 테이블과는 다르다(PART-033).
설계 원칙: 들어내는 단위 하나가 조각 하나가 되게 나눈다. 보관 규칙이 하나면 월 한 단계로 충분하고, 규칙이 행의 종류마다 다르면 그 종류를 아래 단계로 둔다.
DELETE와 SHRINK로 매달 청소하는 길도 있지만, SHRINK는 남은 행을 모두 옮겨 써서 지운 몫보다 비싸다(교재 14화 §11, 교재 05화).
TRUNCATE SUBPARTITION과 DROP PARTITION은 DDL이라 롤백이 없다. 감사 규정이 바뀔 수 있는 데이터라면 비우기 전에 조각을 보관 테이블과 맞바꾸는 방법(EXCHANGE)도 함께 검토한다(대량 처리 문제에서 다룬다).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

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

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황