PART-045

주문 칸을 들어내려니 ORA-02266이 나서 주문상세부터 매달 DELETE로 지운다

실전DDL한빛유통420 / 700

상황

한빛유통은 주문을 월별 파티션 T_ORD_P로 옮기고, 보관 기간(24개월)이 지난 달을 칸째 정리하기로 했다. 주문상세 T_ORD_DET는 파티션 없이 그대로 두었고, 주문을 FK로 참조한다. 이관 뒤 첫 월 정리에서 조민우가 주문상세의 그 달 행을 먼저 DELETE로 지우고 주문 칸을 DROP했는데 ORA-02266이 났다. "자식 행은 다 지웠는데 왜 막히죠?" 지금은 두 테이블 모두 DELETE로 지운다. 박팀장: "FK를 잠깐 끄고 하자는 말도 나오는데, 방법 몇 가지를 비교해서 주문상세까지 칸 단위로 정리할 수 있게 설계해 와."

테이블과 인덱스

T_ORD_P 주문(파티션 복사본, 문제 설정): T_ORD 100,000행을 PARTITION BY RANGE (ORD_DT) 월별 24칸(P202301 ~ P202412)과 빈 PMAX로 나눠 CTAS로 지었다(STORAGE (INITIAL 64K)). 칸 하나는 약 4,170행, FULL로 약 38블록이다. 이번 달 정리 대상 P202301은 4,163행이다.
PK PK_T_ORD_P (ORD_SEQ): 주문상세의 FK가 이 컬럼 하나를 참조하므로 파티션하지 않은 GLOBAL 유니크 인덱스로 만들었다.
T_ORD_DET 주문상세: 200,000행 · 896블록(전체 스캔 807) · 컬럼 ORD_SEQ · ORD_DET_NO · ITM_ID · ORD_QTY · UNT_PRC · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) · FK_ORD_DET_ORD (ORD_SEQ) REFERENCES T_ORD_P (ORD_SEQ) 활성. 주문 하나에 1 ~ 3건이고, P202301 주문의 상세는 8,331행이다.
운영은 ARCHIVELOG다. 낮에는 주문 화면과 배송 화면이 두 테이블을 고친다. 주문을 DELETE하는 화면이나 배치는 없다(취소는 상태만 바꾼다).

원본 SQL

sql

-- 처음 시도 (2025-01-01)
DELETE FROM T_ORD_DET d
 WHERE d.ORD_SEQ IN (SELECT o.ORD_SEQ FROM T_ORD_P o
                      WHERE o.ORD_DT >= DATE '2023-01-01'
                        AND o.ORD_DT <  DATE '2023-02-01');
COMMIT;
ALTER TABLE T_ORD_P DROP PARTITION P202301 UPDATE GLOBAL INDEXES;      -- ORA-02266

-- 지금 (매월 1일 02:00)
DELETE FROM T_ORD_DET d
 WHERE d.ORD_SEQ IN (SELECT o.ORD_SEQ FROM T_ORD_P o
                      WHERE o.ORD_DT >= DATE '2023-01-01'
                        AND o.ORD_DT <  DATE '2023-02-01');
DELETE FROM T_ORD_P
 WHERE ORD_DT >= DATE '2023-01-01'
   AND ORD_DT <  DATE '2023-02-01';
COMMIT;

실행계획 학습용 예시

xplan

[처음 시도]
  DELETE FROM T_ORD_DET ...                     8,331 rows deleted.   Commit complete.
  ALTER TABLE T_ORD_P DROP PARTITION P202301 UPDATE GLOBAL INDEXES
  ORA-02266: unique/primary keys in table referenced by enabled foreign keys

[지금 · ① 주문상세 DELETE]
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name      | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-----------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT          |           |      1 |        |      0 | 00:00:00.21 |   26114 |        |       |
|   1 |  DELETE                   | T_ORD_DET |      1 |        |      0 | 00:00:00.21 |   26114 |        |       |
|*  2 |   HASH JOIN RIGHT SEMI    |           |      1 |   8340 |   8331 | 00:00:00.05 |     845 |        |       |
|   3 |    PARTITION RANGE SINGLE |           |      1 |   4170 |   4163 | 00:00:00.01 |      38 |      1 |     1 |
|*  4 |     TABLE ACCESS FULL     | T_ORD_P   |      1 |   4170 |   4163 | 00:00:00.01 |      38 |      1 |     1 |
|   5 |    TABLE ACCESS FULL      | T_ORD_DET |      1 |   200K |   200K | 00:00:00.03 |     807 |        |       |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("D"."ORD_SEQ"="O"."ORD_SEQ")
   4 - filter(("O"."ORD_DT">=TO_DATE(' 2023-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."ORD_DT"<TO_DATE(' 2023-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

[지금 · ② 주문 DELETE]
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name      | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-----------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT          |           |      1 |        |      0 | 00:00:00.09 |   12655 |        |       |
|   1 |  DELETE                   | T_ORD_P   |      1 |        |      0 | 00:00:00.09 |   12655 |        |       |
|   2 |   PARTITION RANGE SINGLE  |           |      1 |   4170 |   4163 | 00:00:00.01 |      38 |      1 |     1 |
|*  3 |    TABLE ACCESS FULL      | T_ORD_P   |      1 |   4170 |   4163 | 00:00:00.01 |      38 |      1 |     1 |
-----------------------------------------------------------------------------------------------------------------
  (지우는 주문마다 PK_T_ORD_DET 로 자식이 남았는지 확인하고, GLOBAL PK 의 키를 지운다)

[세션 통계 · ① + ② + COMMIT]
  지운 행 12,494 · redo size 약 4.6 MB · undo change vector size 약 2.1 MB · db block changes 약 38,000

[정리 뒤]
  T_ORD_P P202301   0행 · 38블록 그대로       T_ORD_DET 전체 스캔 807 → 807 (지운 자리가 테이블 곳곳에 흩어져 있다)

문제

자식 행을 다 지웠는데도 ORA-02266이 난 까닭과, 지금 두 DELETE의 비용이 어디서 나오는지 설명하시오.
ⓐ FK를 잠시 끄고 주문 칸을 DROP하는 안, ⓑ 주문상세에도 주문일을 넣어 같은 기준으로 나누는 안, ⓒ 주문상세를 참조 파티션(PARTITION BY REFERENCE)으로 다시 짓는 안을 정합성, 매달의 비용, 부작용으로 비교하시오.
고른 안의 DDL과 월 정리 SQL을 작성하고, 그 안의 대가를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ⓒ 주문상세를 참조 파티션으로 다시 짓는다 : 자식은 FK 로 부모(T_ORD_P)의 칸 기준을 물려받는다
CREATE TABLE T_ORD_DET_R (
  ORD_SEQ     NUMBER        NOT NULL,           -- 참조 파티션의 FK 컬럼은 NOT NULL 이어야 한다
  ORD_DET_NO  NUMBER        NOT NULL,
  ITM_ID      VARCHAR2(20),
  ORD_QTY     NUMBER,
  UNT_PRC     NUMBER,
  CONSTRAINT FK_ORD_DET_R FOREIGN KEY (ORD_SEQ)
    REFERENCES T_ORD_P (ORD_SEQ) ON DELETE CASCADE      -- TRUNCATE … CASCADE 의 전제
)
PARTITION BY REFERENCE (FK_ORD_DET_R)
STORAGE (INITIAL 64K);

INSERT /*+ APPEND */ INTO T_ORD_DET_R (ORD_SEQ, ORD_DET_NO, ITM_ID, ORD_QTY, UNT_PRC)
SELECT ORD_SEQ, ORD_DET_NO, ITM_ID, ORD_QTY, UNT_PRC FROM T_ORD_DET;
COMMIT;

ALTER TABLE T_ORD_DET_R ADD CONSTRAINT PK_T_ORD_DET_R PRIMARY KEY (ORD_SEQ, ORD_DET_NO);

-- 작업 창에서 바꿔 끼운다. 옛 테이블의 FK 가 남아 있으면 아래 TRUNCATE 도 ORA-02266 으로 막히므로 옛 테이블은 지운다
ALTER TABLE T_ORD_DET RENAME TO T_ORD_DET_OLD;
ALTER TABLE T_ORD_DET_R RENAME TO T_ORD_DET;
DROP TABLE T_ORD_DET_OLD PURGE;                 -- 건수 대조를 끝낸 뒤

-- 월 정리 (매월 1일 02:00) : 부모 칸을 비우면서 자식의 같은 칸도 함께 비운다 · GLOBAL PK 둘은 같이 고친다
ALTER TABLE T_ORD_P TRUNCATE PARTITION P202301 CASCADE UPDATE INDEXES;

-- 확인 : 두 테이블의 그 칸이 비었는지, 인덱스가 VALID 인지
SELECT COUNT(*) FROM T_ORD_P   PARTITION (P202301);
SELECT COUNT(*) FROM T_ORD_DET PARTITION (P202301);
SELECT index_name, status FROM user_indexes WHERE table_name IN ('T_ORD_P', 'T_ORD_DET');

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

xplan

[월 정리 · TRUNCATE PARTITION P202301 CASCADE UPDATE INDEXES]
  경과 약 0.1초 · redo 약 0.03 MB · undo 약 0.01 MB
  T_ORD_P   P202301   4,163 → 0
  T_ORD_DET P202301   8,331 → 0   (참조 파티션의 자식 칸은 부모 칸 이름을 그대로 물려받는다)
  PK_T_ORD_P · PK_T_ORD_DET_R   VALID (23ai 는 지운 키를 고아 항목으로 두었다가 나중에 정리한다)

[세 안 비교 (어림)]
  안                         정합성                         매달 비용                   부작용
  지금 DELETE 둘             FK 가 지킨다                   redo 4.6 MB · 행마다        주문상세 807블록 그대로
  ⓐ FK 끄고 DROP             꺼 둔 사이 고아 행 가능         켤 때 VALIDATE 면 20만 행    켜는 것을 잊으면 FK 없이 운영
  ⓑ 주문일 추가 + 같은 칸     FK 가 여전히 부모 DDL 을 막음   ⓐ 와 같다 (FK 를 꺼야 한다)  컬럼 추가 · 적재 프로그램 수정
  ⓒ 참조 파티션              FK 가 지킨다                   redo 약 0.03 MB             ON DELETE CASCADE · 이관 한 번

모범 답안 서술

FK가 켜져 있으면 오라클은 부모 칸을 DROP하거나 TRUNCATE하기 전에 자식 행이 실제로 남았는지 세어 보지 않는다. 칸 DDL은 행 단위로 확인하지 않고 칸째 사라지므로, 켜진 FK가 그 테이블을 참조한다는 사실만으로 ORA-02266을 내고 막는다. 그래서 주문상세의 그 달 행을 먼저 다 지웠어도 막혔다. 지금 두 DELETE의 비용은 행 단위 일에서 나온다. ① 주문상세 DELETE는 주문 칸 하나(38블록)와 주문상세 전체(807블록)를 해시 세미 조인으로 대어 8,331행을 고른 뒤, 행마다 테이블 행과 PK 키를 지우며 undo와 redo를 남긴다(Buffers 26,114). ② 주문 DELETE는 4,163행마다 GLOBAL PK 키를 지우고, 지우는 주문마다 PK_T_ORD_DET로 자식이 남았는지 확인한다(12,655). 합쳐 redo 약 4.6MB, undo 약 2.1MB이고, 주문상세는 지운 자리가 테이블 곳곳에 흩어져 전체 스캔이 807블록 그대로다. 세 안을 비교한다. ⓐ FK를 끄고 DROP한 뒤 다시 켜는 안은 꺼 둔 사이에 주문 화면이나 배송 화면이 부모 없는 상세(고아 행)를 넣어도 막지 못한다. 다시 켤 때 VALIDATE면 주문상세 20만 행을 모두 확인하고, NOVALIDATE면 그 확인을 건너뛰어 고아 행이 있어도 모른다. 켜는 단계를 잊으면 FK 없이 운영하게 된다. ⓑ 주문상세에 주문일을 넣어 같은 기준으로 나누면 칸은 맞지만, FK가 켜져 있는 한 부모 칸 DDL은 여전히 ORA-02266으로 막힌다. 자식 칸을 먼저 비워도 마찬가지라서 결국 ⓐ처럼 FK를 꺼야 하고, 컬럼 추가와 적재 프로그램 수정까지 든다. ⓒ 참조 파티션은 주문상세가 FK로 주문의 칸 기준을 물려받아, 주문 한 건의 상세는 늘 주문과 같은 이름의 칸에 들어간다. FK는 그대로 켜 둔 채 ALTER TABLE T_ORD_P TRUNCATE PARTITION P202301 CASCADE UPDATE INDEXES 한 줄로 부모 칸과 자식의 같은 칸을 함께 비운다. 행을 하나씩 지우지 않으므로 redo는 약 4.6 → 0.03MB가 되고, 정합성은 FK가 계속 지킨다. 그래서 ⓒ를 고른다. ⓒ의 대가는 셋이다. 첫째, CASCADE를 쓰려면 참조 FK에 ON DELETE CASCADE가 있어야 한다. 그러면 누군가 주문을 DELETE하면 상세가 에러 없이 함께 지워진다. 지금은 주문을 DELETE하는 화면이 없지만, 코딩 규칙과 권한으로 주문 DELETE를 막아 둔다. 둘째, 주문상세를 한 번 다시 지어야 한다(20만 행 이관, 작업 창, 옛 테이블과 옛 FK 정리). 옛 FK가 남아 있으면 CASCADE도 ORA-02266으로 막힌다. 셋째, 두 테이블의 PK가 GLOBAL이라 정리 DDL에 UPDATE INDEXES를 빠뜨리면 UNUSABLE이 되어 주문 INSERT가 ORA-01502로 멈춘다. 확인 쿼리를 정리 스크립트 끝에 둔다.

해설

참조 파티션의 조건: FK 컬럼이 NOT NULL이고 FK가 켜져 있으며 검증된 상태여야 한다. 부모의 참조 키(ORD_SEQ)에는 PK나 유니크 제약이 있어야 하는데, 부모의 파티션 키가 아니므로 GLOBAL 유니크 인덱스가 된다. TRUNCATE PARTITION … CASCADE는 12c부터 쓸 수 있다.
덤: 부모와 자식이 같은 기준으로 짝지어 있으므로, 주문과 주문상세를 한 달로 조인할 때 칸끼리 조인하거나 자식 쪽도 그 칸만 읽게 할 수 있다(파티션 와이즈 조인, PART-048).
TRUNCATE는 칸을 남긴다(64KB). 빈 칸을 계속 둘지, 분기마다 부모 칸을 DROP할지는 정리 규칙으로 정한다. 참조 파티션에서 부모 칸 DDL이 자식 칸에 어떻게 이어지는지는 운영 버전에서 시험 DB로 먼저 확인한다.
FK를 아예 없애자는 말이 나오기 쉽다. 그러면 칸 DDL은 쉬워지지만 고아 행을 막을 장치가 사라진다(교재 18화의 꺼진 FK와 고아 키).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황