분기 점검 스크립트가 지워도 된다고 올린 인덱스 넷을 이번 주 토요일에 그대로 지우겠다고 한다

실전DDL한빛유통68 / 700

상황

한빛유통 DBA가 분기 점검에서 '접두 중복 인덱스' 찾기 스크립트를 돌렸다. 스크립트의 규칙은 이렇다. "한 인덱스의 컬럼이 다른 인덱스의 앞부분과 같으면 짧은 쪽을 지운다." 스크립트가 지울 목록 넷과 DROP 문을 만들었고, DBA는 이번 주 토요일에 그대로 돌릴 생각이다. 박팀장은 이렇게 적어 보냈다. "넷을 하나씩 따져 오고, 실제로 정리할 인덱스와 스크립트 규칙을 고쳐 와라."

테이블과 인덱스

T_ORD 주문: 100,000행 · 896블록 · 주문 INSERT 하루 약 140건 · 결제나 취소로 ORD_ST를 고치는 UPDATE 하루 약 300건
- PK_T_ORD (ORD_SEQ): PK 제약을 받치는 유니크 인덱스 · BLEVEL 1 · 리프 약 190블록
- IX_T_ORD_SEQ_ST (ORD_SEQ, ORD_ST): 2019년 「주문 상태 확인」 API(SELECT ORD_ST FROM T_ORD WHERE ORD_SEQ = :o, 하루 약 5만 번)를 테이블에 가지 않게 하려고 만들었다 · 리프 약 240블록
- IX_T_ORD_CUS (CUS_ID): 리프 약 330블록 · IX_T_ORD_CUS_DT (CUS_ID, ORD_DT): 상담 화면용 · 리프 약 375블록 · FK FK_ORD_CUS (CUS_ID → M_CUS)
T_ORD_DET 주문상세: 200,000행 · 896블록 · (ORD_SEQ, ORD_DET_NO) 순으로 쌓였다 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO): BLEVEL 1 · 리프 약 500블록 · IX_T_ORD_DET_SEQ (ORD_SEQ): 리프 약 400블록 · FK FK_ORD_DET_ORD (ORD_SEQ → T_ORD)
T_ORD_STLM 정산: 100,000행 · 640블록 · 야간 배치가 비우고(TRUNCATE) 주문 한 건에 정산 한 건씩 다시 채운다
- UX_STLM_ORD (ORD_SEQ): 제약 없이 CREATE UNIQUE INDEX로 만든 유니크 인덱스 · 2022년 정산 배치가 두 번 돌아 정산이 두 배로 찍힌 사고 뒤에 만들었다
- IX_STLM_ORD_DT (ORD_SEQ, STLM_DT): 「PG 대사」 배치용(SELECT STLM_SEQ, STLM_AMT … WHERE ORD_SEQ = :o AND STLM_DT >= :d)
모든 인덱스의 통계는 최신이다

원본 SQL

sql

-- 스크립트가 만든 DROP 문 (이번 주 토요일 실행 예정)
DROP INDEX IX_T_ORD_DET_SEQ;   -- ① (ORD_SEQ) ⊂ PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO)
DROP INDEX PK_T_ORD;           -- ② (ORD_SEQ) ⊂ IX_T_ORD_SEQ_ST (ORD_SEQ, ORD_ST)
DROP INDEX IX_T_ORD_CUS;       -- ③ (CUS_ID)  ⊂ IX_T_ORD_CUS_DT (CUS_ID, ORD_DT)
DROP INDEX UX_STLM_ORD;        -- ④ (ORD_SEQ) ⊂ IX_STLM_ORD_DT (ORD_SEQ, STLM_DT)

-- 참고: 「주문 상태 확인」 API
SELECT ORD_ST FROM T_ORD WHERE ORD_SEQ = :o;

실행계획 학습용 예시

xplan

[「주문 상태 확인」 API · 지금]
---------------------------------------------------------------------------------------------
| Id  | Operation        | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                 |      1 |        |      1 |00:00:00.01 |       2 |
|*  1 |  INDEX RANGE SCAN| IX_T_ORD_SEQ_ST |      1 |      1 |      1 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------
   1 - access("ORD_SEQ"=:O)

[같은 API · NO_INDEX(T_ORD IX_T_ORD_SEQ_ST) 힌트로 잰 것]
----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_ORD    |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  2 |   INDEX UNIQUE SCAN         | PK_T_ORD |      1 |      1 |      1 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------
   2 - access("ORD_SEQ"=:O)

[주문 상세 조회 WHERE ORD_SEQ = :o]  지금 IX_T_ORD_DET_SEQ로 Buffers 3 · NO_INDEX 힌트로 PK_T_ORD_DET를 쓰게 해도 Buffers 3

문제

스크립트가 올린 DROP 넷을 하나씩 판정하시오. 그대로 지워도 되는 것과 안 되는 것, 그 이유를 설명하시오.
②와 ④에서 실제로 정리할 인덱스를 정하고, 넷을 반영하는 DDL을 고쳐 쓰시오.
'짧은 쪽을 지운다'는 스크립트 규칙에 더할 조건을 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 지운다 : PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) 의 선두가 같다 (FK_ORD_DET_ORD 의 자식 쪽 확인도 PK 가 받친다)
ALTER INDEX IX_T_ORD_DET_SEQ INVISIBLE;
-- ③ 지운다 : IX_T_ORD_CUS_DT 의 선두가 같다 (FK_ORD_CUS 도 그 인덱스가 받친다)
ALTER INDEX IX_T_ORD_CUS INVISIBLE;
-- ② PK_T_ORD 는 지우지 않는다 (PK 제약) · 정리할 쪽은 유일 컬럼 뒤에 ORD_ST 를 붙인 긴 인덱스
ALTER INDEX IX_T_ORD_SEQ_ST INVISIBLE;
-- ④ UX_STLM_ORD 는 지우지 않는다 (주문당 정산 한 건) · 제약으로 올려 다음 스크립트가 지우지 못하게 한다
ALTER TABLE T_ORD_STLM ADD CONSTRAINT UQ_STLM_ORD UNIQUE (ORD_SEQ) USING INDEX UX_STLM_ORD;
ALTER INDEX IX_STLM_ORD_DT INVISIBLE;

-- 2주 동안 V$SQL_PLAN · DBA_INDEX_USAGE 로 지켜본 뒤
DROP INDEX IX_T_ORD_DET_SEQ;
DROP INDEX IX_T_ORD_CUS;
DROP INDEX IX_T_ORD_SEQ_ST;
DROP INDEX IX_STLM_ORD_DT;

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

xplan

[「주문 상태 확인」 API · IX_T_ORD_SEQ_ST를 지운 뒤]
----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_ORD    |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  2 |   INDEX UNIQUE SCAN         | PK_T_ORD |      1 |      1 |      1 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------
   2 - access("ORD_SEQ"=:O)

[주문 상세 조회 · IX_T_ORD_DET_SEQ를 지운 뒤]
--------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |      1 |        |      2 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_DET    |      1 |      2 |      2 |00:00:00.01 |       3 |
|*  2 |   INDEX RANGE SCAN                  | PK_T_ORD_DET |      1 |      2 |      2 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------------------------
   2 - access("ORD_SEQ"=:O)

모범 답안 서술

①은 지운다. (ORD_SEQ)는 PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO)의 접두다. 주문번호로 상세를 찾는 SQL은 PK로 같은 범위를 찾아가고(3블록 그대로), 테이블이 (ORD_SEQ, ORD_DET_NO) 순으로 쌓여 있어 CF도 같다. FK_ORD_DET_ORD의 자식 쪽 확인(부모 주문을 지우거나 키를 바꿀 때 자식 행을 찾는 일)도 FK 컬럼 ORD_SEQ가 PK의 선두라 그대로 받친다. ③도 지운다. (CUS_ID)는 IX_T_ORD_CUS_DT의 접두이고, 고객 한 명의 주문 50건은 어느 인덱스로 가도 테이블의 서로 다른 50블록에 있다. FK_ORD_CUS의 자식 쪽 확인도 CUS_ID가 선두인 IX_T_ORD_CUS_DT가 받친다. 고객 번호만 세는 SQL이 FAST FULL SCAN을 쓴다면 330 → 375블록으로 조금 커지는 것이 대가다. ②는 그대로 돌리면 ORA-02429로 멈춘다. PK_T_ORD는 PK 제약을 받치는 인덱스라 DROP INDEX로 지울 수 없다. 방향도 거꾸로다. ORD_SEQ가 유일하므로 (ORD_SEQ, ORD_ST)의 뒤 컬럼 ORD_ST는 범위를 좁히는 데 쓰일 일이 없다. 이 인덱스가 하는 일은 상태 API를 테이블에 가지 않게 하는 커버링뿐이고, 그 몫은 한 번에 1블록이다(3 → 2블록, 하루 5만 블록). 대신 결제나 취소로 ORD_ST가 바뀔 때마다 이 인덱스의 항목을 지우고 다시 넣는다. 정리할 쪽은 IX_T_ORD_SEQ_ST다. ④는 위험하다. UX_STLM_ORD는 제약 없이 만든 유니크 인덱스라 DROP이 에러 없이 끝나고, 그 순간 "주문 한 건에 정산 한 건" 규칙을 지키던 장치가 사라진다. 2022년처럼 정산 배치가 두 번 돌면 ORA-00001로 멈추던 적재가 그대로 끝나 정산이 두 배로 찍힌다. 여기서도 정리할 쪽은 IX_STLM_ORD_DT다. ORD_SEQ가 유일하므로 PG 대사 배치는 UX_STLM_ORD로 한 건을 찾고 STLM_DT는 테이블에서 확인하면 된다. 어차피 STLM_AMT를 읽으러 테이블에 가므로 IX_STLM_ORD_DT가 덜어 주는 것이 없다. UX_STLM_ORD는 ALTER TABLE … ADD CONSTRAINT … UNIQUE … USING INDEX로 제약으로 올려, 다음 스크립트가 지우려 하면 ORA-02429로 막히게 한다. 스크립트 규칙에는 다음을 더한다. 짧은 쪽이 PK나 유니크 인덱스면 지우지 않는다. 선두가 유일 컬럼인 결합 인덱스는 따로 골라 커버링으로 쓰이는지 본다. FK 컬럼이 남는 인덱스의 선두에 있는지 확인한다. 지우기 전에 V$SQL_PLAN과 DBA_INDEX_USAGE로 그 인덱스를 쓰는 SQL을 찾고, INVISIBLE로 2주 지켜본 뒤 지운다.

해설

함정: 에러가 나는 ②는 차라리 안전하다. 조용히 지워지는 ④가 사고를 낸다. 제약 없이 만든 유니크 인덱스는 업무 규칙인데도 스크립트에는 그냥 인덱스로 보인다.
PK 제약을 (ORD_SEQ, ORD_ST) 비유일 인덱스로 받치게 바꾸면(USING INDEX) 인덱스를 하나로 줄이면서 상태 API의 커버링도 지킬 수 있다. 하지만 ORD_ST가 바뀔 때마다 PK 인덱스 항목이 움직이고, 얻는 것은 한 번에 1블록이다. 하루 5만 블록은 버퍼 캐시에서 끝나는 양이라 단순한 쪽을 고른다.
교재 04화 §6의 '지울 때 잃을 수 있는 것' 표에는 리프 크기와 통계가 있다. 여기에 CF(ACC-022), 유일성(이 문제), 선두가 다른 인덱스(IDX-064)를 더해 두면 정리 규칙이 완성된다.
유니크 인덱스는 대량 적재 때 UNUSABLE로 끄고 켜는 대상에서도 빠진다(DML이 ORA-01502로 멈춘다, 교재 14화 §3). 정산 적재는 이 인덱스를 켠 채 넣는 비용을 받아들인다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황