보관 배치에 APPEND를 붙였는데 redo가 그대로이고, 트리거를 끄자 보관 일시가 비었다

중급INSERT한빛유통461 / 700

상황

매년 1월 첫 일요일에 지난해 주문을 보관 테이블로 옮기는 배치가 돈다. 주문(T_ORD_ARC)을 먼저 옮기고 주문상세(T_ORD_DET_ARC)를 옮긴다. 오과장이 두 INSERT에 모두 /*+ APPEND */를 붙였는데, 주문상세 단계의 redo와 시간은 그대로였다. 리허설에서 실행계획의 Note를 보고 보관 일시를 채우던 트리거를 꺼 봤더니 계획은 여전히 그대로였고, 그렇게 들어간 행은 ARC_DT가 NULL이었다.

테이블과 인덱스

T_ORD: 100,000행 · 896블록 · ORD_DT 2023-01-01 ~ 2024-12-31 · 2023년 주문 50,060건 · PK_T_ORD (ORD_SEQ)
T_ORD_DET: 200,000행 · 896블록(전체 스캔 807) · 2023년 주문의 상세 100,140행 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO)
T_ORD_ARC · T_ORD_DET_ARC 보관 테이블(문제 설정): 원본 컬럼에 ARC_DT DATE와 ARC_USER VARCHAR2(30)를 더했다. 처음 보관하는 해라 둘 다 비어 있다. T_ORD_ARC에는 PK_T_ORD_ARC (ORD_SEQ)만 있다.
T_ORD_DET_ARC에는 PK_T_ORD_DET_ARC (ORD_SEQ, ORD_DET_NO)와 FK_ORD_DET_ARC (ORD_SEQ) REFERENCES T_ORD_ARC가 있고, 행 트리거 TRG_ORD_DET_ARC_BI(BEFORE INSERT FOR EACH ROW)가 :NEW.ARC_DT := SYSDATE; :NEW.ARC_USER := USER;를 채운다.
보관 테이블은 이 배치만 쓰고, 보관 담당자가 가끔 조회만 한다. 연습 DB 복사본이다(NOARCHIVELOG).

원본 SQL

sql

-- 매년 1월 첫 일요일 · 지난해 주문 보관
-- ① 주문
INSERT /*+ APPEND */ INTO T_ORD_ARC
SELECT o.*, SYSDATE, USER
  FROM T_ORD o
 WHERE o.ORD_DT >= DATE '2023-01-01' AND o.ORD_DT < DATE '2024-01-01';
COMMIT;

-- ② 주문상세 (ARC_DT · ARC_USER 는 트리거가 채운다)
INSERT /*+ APPEND */ INTO T_ORD_DET_ARC (ORD_SEQ, ORD_DET_NO, ITM_ID, ORD_QTY, UNT_PRC)
SELECT d.ORD_SEQ, d.ORD_DET_NO, d.ITM_ID, d.ORD_QTY, d.UNT_PRC
  FROM T_ORD_DET d
 WHERE d.ORD_SEQ IN (SELECT ORD_SEQ FROM T_ORD_ARC);
COMMIT;

실행계획 학습용 예시 · 교재 14화 §4.3 모양

xplan

[② 주문상세 보관 — 리허설 1회차]
-----------------------------------------------------------------------------------------------------
| Id  | Operation                | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |               |      1 |        |      0 |00:00:05.87 |    623K |
|   1 |  LOAD TABLE CONVENTIONAL | T_ORD_DET_ARC |      1 |        |      0 |00:00:05.87 |    623K |
|*  2 |   HASH JOIN RIGHT SEMI   |               |      1 |    100K|    100K|00:00:00.12 |     919 |
|   3 |    INDEX FAST FULL SCAN  | PK_T_ORD_ARC  |      1 |  50060 |  50060 |00:00:00.01 |     112 |
|   4 |    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"="ORD_SEQ")

Note
-----
   - Direct Load disabled because triggers are defined

[② 같은 문장 — 리허설 2회차 · ALTER TRIGGER TRG_ORD_DET_ARC_BI DISABLE 뒤]
  1번 줄  LOAD TABLE CONVENTIONAL  T_ORD_DET_ARC  · A-Time 00:00:03.62 · Buffers 512K (아래 줄은 1회차와 같다)
Note
-----
   - Direct Load disabled because parent referential constraints are present

[세션 통계 — ② 한 번]
                            1회차 (트리거 켬)    2회차 (트리거 끔)    (비교) ① 주문 보관
  redo size                    42.7 MB             27.9 MB              0.1 MB
  undo change vector size      12.4 MB              8.2 MB              0.02 MB
  session logical reads        623,410             512,880              1,980
  경과                          5.9 초               3.6 초              0.08 초
  ARC_DT가 NULL인 행                 0             100,140              —

문제

①은 직접 경로로 돌았는데 ②는 왜 일반 INSERT로 돌았는지, 2회차에서 트리거를 끈 뒤에도 그대로였던 이유와 함께 설명하시오.
②가 직접 경로로 돌도록 고치시오. 트리거가 하던 일과 참조 무결성을 잃지 않게 하시오.
고친 배치가 정말 직접 경로로 돌았는지 확인하는 방법과, 제약을 다시 켤 때의 선택지를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ② 주문상세 보관 — 직접 경로가 되도록 트리거와 FK 를 잠시 치우고, 트리거가 하던 일은 SELECT 로 옮긴다
ALTER TRIGGER TRG_ORD_DET_ARC_BI DISABLE;
ALTER TABLE T_ORD_DET_ARC DISABLE CONSTRAINT FK_ORD_DET_ARC;

INSERT /*+ APPEND */ INTO T_ORD_DET_ARC (ORD_SEQ, ORD_DET_NO, ITM_ID, ORD_QTY, UNT_PRC, ARC_DT, ARC_USER)
SELECT d.ORD_SEQ, d.ORD_DET_NO, d.ITM_ID, d.ORD_QTY, d.UNT_PRC, SYSDATE, USER
  FROM T_ORD_DET d
 WHERE d.ORD_SEQ IN (SELECT ORD_SEQ FROM T_ORD_ARC);
COMMIT;

-- 부모(T_ORD_ARC)에 있는 주문만 골라 넣었으므로 기존 행 검사 없이 다시 켠다 (검사까지 하려면 ENABLE VALIDATE)
ALTER TABLE T_ORD_DET_ARC ENABLE NOVALIDATE CONSTRAINT FK_ORD_DET_ARC;
ALTER TRIGGER TRG_ORD_DET_ARC_BI ENABLE;      -- 손으로 넣는 행을 위해 되돌려 둔다

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

xplan

------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |               |      1 |        |      0 |00:00:00.41 |    3874 |
|   1 |  LOAD AS SELECT                  | T_ORD_DET_ARC |      1 |        |      0 |00:00:00.41 |    3874 |
|   2 |   OPTIMIZER STATISTICS GATHERING |               |      1 |    100K|    100K|00:00:00.13 |     919 |
|*  3 |    HASH JOIN RIGHT SEMI          |               |      1 |    100K|    100K|00:00:00.12 |     919 |
|   4 |     INDEX FAST FULL SCAN         | PK_T_ORD_ARC  |      1 |  50060 |  50060 |00:00:00.01 |     112 |
|   5 |     TABLE ACCESS FULL            | T_ORD_DET     |      1 |    200K|    200K|00:00:00.03 |     807 |
------------------------------------------------------------------------------------------------------------

[세션 통계 — ② 한 번]
  redo size        42.7 MB → 9.6 MB   (남은 것은 PK 인덱스를 적재 끝에 한꺼번에 고치는 몫)
  undo             12.4 MB → 3.0 MB
  logical reads    623,410 → 3,874
  경과             5.9 초 → 0.4 초
  ARC_DT NULL      0행
  FK 다시 켜기      ENABLE NOVALIDATE 0.01 초 · (ENABLE VALIDATE 라면 약 0.2 초: 자식 10만 행을 부모 PK 로 확인)

모범 답안 서술

①의 T_ORD_ARC에는 PK만 있어서 직접 경로로 돌았다(redo 0.1MB). ②의 T_ORD_DET_ARC에는 행 트리거와 자식 쪽 FK가 둘 다 있다. 대상 테이블에 행 트리거나 부모를 참조하는 FK가 있으면 오라클은 /*+ APPEND */를 에러 없이 무시하고 일반 INSERT로 돈다. 1회차 실행계획 1번 줄이 LOAD TABLE CONVENTIONAL이고 Note가 'triggers are defined'인 것이 그 증거다. 그래서 행마다 PK 인덱스를 고치고, 부모 키를 찾아가고, 트리거를 부르며 redo 42.7MB, 논리 읽기 62만이 나왔다. 2회차에 트리거만 껐을 때 계획이 그대로였던 것은 Note가 꺼진 이유를 하나만 보여 주기 때문이다. 트리거를 치우자 다음 이유인 FK('parent referential constraints are present')가 나왔다. 게다가 트리거가 하던 일(ARC_DT, ARC_USER 채우기)까지 꺼져서 100,140행의 ARC_DT가 NULL로 들어갔다. 고친 배치는 트리거와 FK를 둘 다 잠시 끄고, 트리거가 하던 일을 SELECT의 SYSDATE, USER로 옮긴다. 그러면 1번 줄이 LOAD AS SELECT가 되고 redo는 42.7 → 9.6MB(남은 몫은 PK 인덱스를 적재 끝에 한꺼번에 고치는 일), 논리 읽기는 623,410 → 3,874, 경과는 5.9 → 0.4초로 준다. ARC_DT가 빈 행은 없다. 직접 경로로 돌았는지는 힌트가 아니라 실행계획의 첫 줄로 확인한다. LOAD AS SELECT와 OPTIMIZER STATISTICS GATHERING이 보이면 직접 경로이고, LOAD TABLE CONVENTIONAL이면 Note에서 이유를 찾는다. FK를 다시 켤 때는 두 가지를 고를 수 있다. ENABLE NOVALIDATE는 기존 행 검사를 건너뛰어 바로 끝난다. 이 배치는 부모(T_ORD_ARC)에 있는 주문만 IN으로 골라 넣었으므로 그 근거로 NOVALIDATE를 쓴다. 검사까지 하려면 ENABLE VALIDATE로 켠다. 자식 10만 행을 부모 PK로 다시 확인하는 비용(약 0.2초)이 들고, 부모 없는 행이 있으면 켜지지 않는다.

해설

직접 경로가 꺼지는 조건은 에러를 내지 않는다(교재 14화 §4.3). 힌트를 붙였다는 사실이 아니라 계획의 첫 줄이 증거다. 대상 테이블의 트리거와 제약은 USER_TRIGGERS, USER_CONSTRAINTS에서 한꺼번에 보고 치운다.
트리거를 끄기 전에 트리거 본문을 읽고, 그 일을 SQL로 옮길 수 있는지 먼저 본다. 감사 로그처럼 다른 테이블에 쓰는 트리거라면 그 INSERT도 배치에 따로 넣어야 한다.
트리거와 제약을 끈 동안 다른 세션이 이 테이블에 쓰면 검사도 트리거도 없이 들어간다. 배치만 쓰는 보관 테이블이라 괜찮다. 공유 테이블이라면 이 방법을 쓰지 않는다.
다른 정답: 보관 테이블의 FK를 RELY DISABLE NOVALIDATE로 두고 정합성은 배치가 건수와 MINUS로 확인한다. 옵티마이저는 RELY 제약을 조인 제거 등에 쓸 수 있다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

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

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황