장애 뒤 FK 컬럼마다 인덱스를 만들자는 스크립트에 야간 배치 담당이 반대한다

중급DDL한빛유통478 / 700

상황

12화 장애 뒤 DBA가 「FK 컬럼에는 모두 인덱스를 만든다」는 표준을 세우고, 한빛유통 스키마의 FK 다섯 개에 인덱스를 만드는 스크립트를 올렸다. 야간 체인 담당 오과장이 반대했다. "주문상세는 보관 배치와 이관 리허설 때마다 20만 행씩 넣어요. 인덱스가 하나 늘 때마다 그만큼 느려져요." 박팀장이 FK마다 근거를 붙여 정리해 달라고 했다.

테이블과 인덱스

FK 다섯(자식 칸 → 부모)과 자식에 이미 있는 인덱스
- FK_T_ORD_DET_ORD: T_ORD_DET (ORD_SEQ) → T_ORD · 자식에 PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO)
- FK_T_ORD_DET_ITM: T_ORD_DET (ITM_ID) → M_ITM · 해당 인덱스 없음
- FK_T_ORD_CUS: T_ORD (CUS_ID) → M_CUS · 자식에 IX_T_ORD_CUS_DT (CUS_ID, ORD_DT)(고객별 주문 화면용)
- FK_T_ITM_EVL_ITM: T_ITM_EVL (ITM_ID) → M_ITM · 자식에 PK_T_ITM_EVL (ITM_ID, EVL_LST_NO)
- FK_T_ITM_EVL_CUS: T_ITM_EVL (CUS_ID) → M_CUS · 해당 인덱스 없음
부모를 고치는 일: M_CUS는 매주 개인정보 파기 배치가 탈퇴 고객 3 ~ 5명을 지운다. M_ITM은 지우지 않고 단종이면 ITM_TP만 바꾼다. T_ORD는 매년 보관 배치가 지난해 주문을 지운다. 어느 부모도 PK 값을 고치지 않는다.
T_ORD_DET 200,000행 · 896블록. T_ITM_EVL 5,000행 · 48블록. 주문상세를 ITM_ID로 찾는 화면은 없다(상품별 판매는 밤에 전체를 읽어 집계한다).

원본 SQL

sql

-- FK 인덱스 표준 적용 (DBA · 12화 후속)
CREATE INDEX IX_T_ORD_DET_ORD ON T_ORD_DET (ORD_SEQ);
CREATE INDEX IX_T_ORD_DET_ITM ON T_ORD_DET (ITM_ID);
CREATE INDEX IX_T_ORD_CUS     ON T_ORD (CUS_ID);
CREATE INDEX IX_T_ITM_EVL_ITM ON T_ITM_EVL (ITM_ID);
CREATE INDEX IX_T_ITM_EVL_CUS ON T_ITM_EVL (CUS_ID);

실행계획 학습용 예시 · 인덱스 하나당 적재 비용은 교재 14화 5만 행 표와 같은 비율

xplan

[개인정보 파기 배치 — 고객 한 명 DELETE FROM M_CUS (지난주)]
  자식 테이블    자식에서 FK를 확인한 방법                          잠금
  T_ORD          INDEX RANGE SCAN IX_T_ORD_CUS_DT · 3블록          없음
  T_ITM_EVL      TABLE ACCESS FULL · 48블록                       확인하는 동안 T_ITM_EVL에 TM 4(SHARE)
  지난주 파기 4명 동안 상품평 등록 화면   enq: TM - contention 3건 (최대 1.2초)

[T_ORD_DET 20만 행 적재 리허설 — 인덱스 개수별 (연습 DB)]
  인덱스                          redo        db block changes    경과
  PK만 (지금)                     52.1 MB         41만             3.9 초
  + (ORD_SEQ)                    100.4 MB         81만             7.6 초
  + (ORD_SEQ) + (ITM_ID)         148.9 MB        121만            11.8 초

문제

다섯 FK 각각에 대해 인덱스가 필요한지 판단하고 근거를 쓰시오.
판단대로 스크립트를 고치시오.
오과장이 걱정한 적재 비용을 위 숫자로 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 다섯 FK 를 하나씩 따졌다. 새로 만드는 것은 하나뿐이다.
-- T_ITM_EVL (CUS_ID) : 부모 M_CUS 를 매주 지우는데 CUS_ID 가 맨 앞인 인덱스가 없다 → 만든다
CREATE INDEX IX_T_ITM_EVL_CUS ON T_ITM_EVL (CUS_ID);

-- 만들지 않는 넷
--   T_ORD_DET (ORD_SEQ) : PK_T_ORD_DET 의 맨 앞 칸이 이미 ORD_SEQ (보관 배치가 T_ORD 를 지울 때 이것을 쓴다)
--   T_ITM_EVL (ITM_ID)  : PK_T_ITM_EVL 의 맨 앞 칸이 이미 ITM_ID
--   T_ORD (CUS_ID)      : IX_T_ORD_CUS_DT (CUS_ID, ORD_DT) 의 맨 앞 칸이 이미 CUS_ID
--   T_ORD_DET (ITM_ID)  : 부모 M_ITM 은 지우지도 키를 고치지도 않고, ITM_ID 로 찾는 화면도 없다

개선 후 실행계획 학습용 예시 · 인덱스 하나당 적재 비용은 교재 14화 5만 행 표와 같은 비율

xplan

[개인정보 파기 배치 — 고객 한 명 DELETE FROM M_CUS]
  T_ITM_EVL   TABLE ACCESS FULL 48블록 · TM 4 → INDEX RANGE SCAN IX_T_ITM_EVL_CUS 2블록 · 잠금 없음
  상품평 등록 화면 enq: TM - contention 0

[T_ORD_DET 20만 행 적재] DBA 스크립트대로 했다면 52.1 → 148.9 MB · 3.9 → 11.8 초 → 실제로는 52.1 MB · 3.9 초 그대로
[T_ITM_EVL] 인덱스 하나 추가 · 상품평은 하루 수십 건 등록이라 적재 비용은 무시할 만하다

모범 답안 서술

FK 확인에 쓰이는 것은 이름이 아니라 'FK 칸이 맨 앞에 오는 인덱스'다. 그 기준으로 다섯을 보면 셋은 이미 덮여 있다. T_ORD_DET (ORD_SEQ)는 PK_T_ORD_DET의 맨 앞 칸이 ORD_SEQ이고, T_ITM_EVL (ITM_ID)는 PK_T_ITM_EVL의 맨 앞 칸이 ITM_ID이며, T_ORD (CUS_ID)는 IX_T_ORD_CUS_DT의 맨 앞 칸이 CUS_ID다. 이미 FK 인덱스 역할을 하는 인덱스가 있으므로 같은 칸으로 하나를 더 만들면 중복이다. 나머지 둘은 부모 쪽에서 무엇을 하는지로 가른다. 자식 쪽 인덱스가 없을 때 생기는 문제는 부모 행을 지우거나 부모 키를 바꿀 때 생긴다. 오라클이 그 부모를 가리키는 자식 행이 있는지 자식 테이블을 통째로 읽어 확인하고, 그동안 자식 테이블에 TM 잠금을 건다(교재 12화). M_CUS는 매주 탈퇴 고객을 지우는데 T_ITM_EVL에는 CUS_ID가 맨 앞인 인덱스가 없어서, 지난주 고객 한 명을 지울 때마다 T_ITM_EVL 48블록을 통째로 읽고 TM 4를 걸어 상품평 등록 화면이 기다렸다. 그래서 T_ITM_EVL (CUS_ID)는 만든다. 반대로 M_ITM은 지우지도 키를 고치지도 않고 단종이면 ITM_TP만 바꾸며, 주문상세를 ITM_ID로 찾는 화면도 없다. T_ORD_DET (ITM_ID) 인덱스는 지켜 줄 일이 없이 적재 비용만 늘리므로 만들지 않는다. 오과장의 걱정은 숫자로 맞다. T_ORD_DET 20만 행 적재에서 인덱스 하나가 늘 때마다 redo가 약 48MB(52.1 → 100.4 → 148.9MB), 블록 변경이 약 40만씩 붙고 경과도 3.9 → 7.6 → 11.8초로 는다. DBA 스크립트대로라면 T_ORD_DET에만 인덱스 둘이 붙어 적재가 세 배가 되는데, 그 둘은 하나는 중복이고 하나는 지킬 일이 없는 인덱스다. 고친 스크립트는 T_ITM_EVL에 하나만 만들므로 주문상세 적재는 그대로이고, 상품평은 하루 수십 건 등록이라 인덱스 하나의 비용이 거의 없다.

해설

표준을 고치는 법: 「FK 컬럼에는 모두 인덱스」 대신 「부모를 지우거나 키를 바꾸는 FK에는, FK 칸이 맨 앞인 인덱스가 있어야 한다」로 적는다. 판단의 근거가 남는다.
지금 부모를 지우지 않는 FK라도 나중에 정리 배치가 생기면 다시 따져야 한다. LOCK-018(유통의 상품 정리 배치)이 그 모양이다.
FK 칸이 결합 인덱스의 두 번째 칸이면 덮이지 않는다. (ORD_DT, CUS_ID) 같은 인덱스는 CUS_ID FK를 지켜 주지 못한다.
자식 인덱스는 '부모 → 자식' 방향 조인(부모를 먼저 읽고 자식을 찾는 NL 조인)에도 쓰인다. 그런 화면이 있다면 그것도 만들 근거다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 인덱스 하나당 적재 비용은 교재 14화 5만 행 표와 같은 비율 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 인덱스 하나당 적재 비용은 교재 14화 5만 행 표와 같은 비율 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황