중복 인덱스 관리: 포함 관계·용도 차이·안전한 제거

핵심 요약

중복 인덱스는 이름이나 컬럼 집합만 보고 판단하지 않습니다.

중복·제거 판단
  = 정의와 객체 속성 비교
  + Constraint·무결성·잠금 용도 확인
  + 실제 사용 SQL·업무 주기 확인
  + 실행계획·작업량 비교
  + Invisible Query 회귀 검증
  + 실제 Drop 후 DML·공간 효과 확인

Oracle은 같은 컬럼 집합에 여러 인덱스를 만들 수 있지만, Index Type·Partitioning·Uniqueness 같은 특성이 달라야 하며 같은 컬럼 집합에서는 동시에 하나만 Visible일 수 있습니다. 따라서 실무에서 말하는 “완전 중복”은 물리적으로 완전히 동일한 두 인덱스라기보다 같은 Query 기능을 사실상 중복 제공하는 객체를 의미하는 경우가 많습니다.

높은 중복 가능성
  X1(A,B)
  X2(A,B,C)

기능 차이 가능성
  X1(A,B,C)
  X2(A,C,B)

객체 역할 차이
  B-tree vs Bitmap
  Unique vs Nonunique
  Local vs Global
  ASC vs DESC·Reverse Key
  일반 Column vs Function-Based Expression
  Constraint Index vs 성능 전용 Index

더 긴 인덱스가 짧은 인덱스의 Leading Portion을 포함하더라도 짧은 인덱스를 바로 삭제하지 않습니다. 짧은 인덱스가 더 작아 Range·Fast Full Scan·Cache에 유리하거나, 다른 정렬·NULL Row 집합·Clustering·Constraint·Foreign Key 잠금 용도를 가질 수 있습니다.

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → 인덱스 튜닝 → 인덱스 설계·운영 범위에서 중복 인덱스 식별, 사용 증거, Invisible 검증, Constraint와 안전한 제거 절차를 다룹니다.

학습 목표

이 이론을 학습한 뒤에는 다음을 설명할 수 있어야 합니다.

• 물리적으로 동일한 인덱스와 기능적으로 중복된 인덱스를 구분한다.
• Prefix 중복·컬럼 순서 중복·객체 속성 차이를 설명한다.
• Oracle의 같은 컬럼 집합 다중 인덱스와 Visibility 제한을 설명한다.
• USER_INDEXES, USER_IND_COLUMNS, USER_IND_EXPRESSIONS, USER_PART_INDEXES로 정의를 비교한다.
• PK·UK Constraint Index와 FK 성능·잠금 Index를 구분한다.
• 짧은 인덱스가 긴 인덱스보다 유리할 수 있는 이유를 설명한다.
• DBA_INDEX_USAGE와 USER_OBJECT_USAGE의 역할과 한계를 설명한다.
• Hint·SQL Plan Baseline·Patch·운영 Script 의존성을 확인한다.
• Invisible Index가 Query Plan과 DML에 미치는 영향을 설명한다.
• 실제 Drop 전후 Query·DML·Redo·공간·운영 회귀를 검증한다.
• 안전한 재생성·통계·Rollback 절차를 설계한다.

1. 중복 인덱스를 줄이는 이유

Table DML이 발생하면 Oracle은 관련 인덱스를 자동으로 유지합니다.

• INSERT: 각 관련 Index에 Entry 삽입
• Index Key UPDATE: 기존 Entry 삭제 + 새 Entry 삽입
• DELETE: 각 Index Entry 유지
• Buffer·Undo·Redo 증가
• Segment·Backup·Recovery·통계 수집 증가
• Buffer Cache 경쟁
• Hot Leaf·Block Split·RAC 경합 가능성
• Optimizer가 비교할 Access Path 증가

불필요한 Index 제거 기대 효과
  → DML CPU·Buffers 감소
  → Redo·Undo 감소
  → Segment·Backup 감소
  → Cache·경합 감소 가능

잘못된 제거 위험
  → Full Scan·비효율 Plan
  → Sort·TEMP 증가
  → Parent Delete·Update Lock 악화
  → 월말·복구·Batch 장애
  → Constraint·Hint·Baseline 의존성 훼손

2. 중복의 네 가지 수준

2.1 동일 컬럼 집합의 다중 인덱스

Oracle은 같은 컬럼 집합에 다음 특성이 다른 여러 인덱스를 허용할 수 있습니다.

• B-tree·Bitmap 등 다른 Index Type
• Unique·Nonunique
• Nonpartitioned·Partitioned
• Local·Global·다른 Partitioning
• 다른 특성의 마이그레이션 후보

같은 컬럼 집합에서는 한 시점에 하나만 Visible일 수 있습니다.

ORDERS_X1(A,B) B-tree VISIBLE
ORDERS_X2(A,B) Bitmap INVISIBLE

컬럼만 같아도 용도와 동시성 특성이 다르므로 즉시 중복으로 삭제하지 않습니다.

2.2 Prefix 중복

X1(customer_id, order_date)
X2(customer_id, order_date, status)

X2의 Leading Portion이 X1 전체를 포함합니다.

Prefix 중복
  → 대체 가능성의 시작
  ≠ 삭제 확정

비교 항목입니다.

• Leaf Blocks·BLEVEL·Entry 폭
• Clustering Factor
• Fast Full·Full Scan 비용
• NULL Row 집합
• 정렬·Covering
• Constraint·FK 용도
• Partition·Compression
• 실제 SQL Runtime

2.3 같은 컬럼 집합, 다른 순서

X1(customer_id, order_date, status)
X2(customer_id, status, order_date)

-- X1에 유리할 수 있음
WHERE customer_id = :customer_id
AND   order_date >= :from_date

-- X2에 유리할 수 있음
WHERE customer_id = :customer_id
AND   status = :status
AND   order_date >= :from_date

첫 Range와 후행 Filter가 달라 서로 대체하지 못할 수 있습니다.

2.4 기능적 중복

정의가 달라도 같은 핵심 SQL에서 비슷한 작업량과 결과를 제공하는 인덱스입니다.

일반 B-tree vs Function-Based Index
단일 Wide Index vs 두 Index의 Index Join
B-tree 복합 Index vs 일시 Bitmap Conversion
짧은 Index vs 긴 Covering Index

기능적 중복은 실제 Workload로 판단합니다.

3. Index 정의를 정확히 비교한다

3.1 기본 속성

SELECT index_name,
       index_type,
       uniqueness,
       visibility,
       status,
       partitioned,
       compression,
       prefix_length,
       blevel,
       leaf_blocks,
       distinct_keys,
       clustering_factor,
       generated,
       auto,
       last_analyzed
FROM   user_indexes
WHERE  table_name = 'ORDERS'
ORDER BY index_name;

확인합니다.

• Type: Normal·Bitmap·Function-Based·Reverse 등
• Unique·Nonunique
• Visible·Invisible
• Valid·Unusable·Disabled
• System Generated·Auto Index 여부
• Compression
• BLEVEL·Leaf Blocks·Clustering
• 통계 시점

AUTO='YES'인 Auto Index는 자동 관리 정책과 Report를 확인하고 수동 Index처럼 단순 Drop하지 않습니다.

3.2 컬럼·방향

SELECT index_name,
       column_position,
       column_name,
       descend,
       column_length
FROM   user_ind_columns
WHERE  table_name = 'ORDERS'
ORDER BY index_name, column_position;

3.3 Function-Based 표현식

SELECT index_name,
       column_position,
       column_expression
FROM   user_ind_expressions
WHERE  table_name = 'ORDERS'
ORDER BY index_name, column_position;

UPPER(name)과 UPPER(TRIM(name))은 같은 Index가 아닙니다.

3.4 Local·Global Partition 속성

USER_INDEXES.PARTITIONED만으로 Local·Global을 구분할 수 없습니다.

SELECT index_name,
       locality,
       alignment,
       partitioning_type,
       subpartitioning_type
FROM   user_part_indexes
WHERE  table_name = 'ORDERS';

Partition별 Status도 확인합니다.

SELECT index_name,
       partition_name,
       status,
       leaf_blocks,
       last_analyzed
FROM   user_ind_partitions
WHERE  index_name IN ('ORDERS_X1','ORDERS_X2');

4. Constraint와 Index의 역할을 분리한다

4.1 Primary Key·Unique Constraint

Oracle은 Enabled PK·UK Constraint를 Index로 Enforcement합니다. 일반적으로 Unique Index를 생성하지만, Deferrable Constraint 등에서는 Nonunique Index를 사용할 수 있습니다.

SELECT constraint_name,
       constraint_type,
       index_name,
       status,
       deferrable,
       deferred,
       validated
FROM   user_constraints
WHERE  table_name = 'ORDERS'
AND    constraint_type IN ('P','U');

Enabled PK·UK Constraint가 사용하는 Index는 Index만 단독으로 Drop할 수 없습니다.

Constraint Index 제거
  → Constraint Drop·Disable 또는
     대체 Enforcement 설계가 먼저

“더 긴 Unique Index가 있으므로 대체 가능”이라고 가정하지 않고 같은 무결성·NULL·Deferrable 의미를 검증합니다.

4.2 Foreign Key Index

Foreign Key Constraint는 Child FK Index를 자동 생성하지 않습니다. 하지만 FK Index는 다음 역할을 가질 수 있습니다.

• Parent Key Delete·Update 시 Child Full Scan 방지
• Child Table의 넓은 Table Lock 영향 방지
• Child FK 조회·Join 지원

FK Index
  ≠ Constraint Enforcement Index
  = 동시성·Parent 변경·Join에 중요한 운영 Index 가능

최근 조회에서 사용되지 않았다는 이유만으로 FK Index를 제거하면 Parent Delete·Update에서 Lock·Scan 회귀가 발생할 수 있습니다.

5. 짧은 Index가 별도로 필요한 이유

5.1 작은 Segment와 Cache

X1(A,B)
X2(A,B,C,D,E)

X2가 X1의 Prefix를 포함해도 X1은 다음에서 유리할 수 있습니다.

• 작은 Leaf Blocks
• 낮은 BLEVEL 가능성
• 좁은 Range Scan
• Index Full·Fast Full Scan
• Buffer Cache 효율
• 낮은 DML Entry 폭

5.2 Clustering Factor 차이

추가 컬럼은 동일 Prefix 안의 Entry 정렬을 바꿀 수 있습니다. Table Block 방문 순서가 달라져 Clustering Factor·Table Buffers가 달라질 수 있습니다.

5.3 NULL Row 집합 차이

X1(nullable_col)
X2(nullable_col, order_id)

order_id가 NOT NULL이면 X2에는 (NULL,order_id) Entry가 있지만 X1에는 모든 Key NULL Row가 없습니다.

같은 선두 Column
  ≠ 같은 Row 집합

5.4 정렬·방향·Reverse Key

X1(customer_id ASC, order_date ASC)
X2(customer_id ASC, order_date DESC, order_id DESC)

전체 역방향 Scan으로 지원 가능한 정렬과 혼합 ASC·DESC는 다릅니다. Reverse Key Index는 Hot Block 분산에 유리할 수 있지만 일반 Range Scan·정렬 지원이 제한됩니다.

5.5 Covering과 Index-only

긴 Index는 Table Access를 제거할 수 있지만 짧은 Index는 더 작은 Scan 비용을 가질 수 있습니다. Query별로 다음을 비교합니다.

짧은 Index + Table Access
vs
긴 Covering Index-only

6. 사용 증거를 수집한다

6.1 자동 누적 Usage

Oracle 26ai의 DBA_INDEX_USAGE는 Index별 누적 통계를 제공합니다.

SELECT owner,
       name,
       total_access_count,
       total_exec_count,
       total_rows_returned,
       last_used
FROM   dba_index_usage
WHERE  owner = USER
AND    name IN ('ORDERS_X1','ORDERS_X2');

이 통계는 다음 질문에 도움을 줍니다.

• Index가 접근된 적이 있는가
• 사용 빈도와 참여 실행 수는 어느 정도인가
• 마지막 사용 시점은 언제인가
• 반환 Row 규모는 어떤가

하지만 다음을 직접 알려 주지는 않습니다.

• 어떤 SQL·업무가 사용했는가
• 사용하지 않았을 때 대체 Plan은 무엇인가
• 월말·분기·복구 주기 전체를 관찰했는가
• Constraint·FK·Hint 의존성이 있는가

6.2 명시적 Monitoring Usage

ALTER INDEX orders_x1 MONITORING USAGE;

SELECT index_name,
       monitoring,
       used,
       start_monitoring,
       end_monitoring
FROM   user_object_usage;

USED=YES/NO는 관찰 기간에 한 번이라도 접근됐는지에 가까운 단순 신호입니다. 빈도·SQL Context·성능 이득을 보여 주지 않습니다.

6.3 실제 SQL과 Plan

• V$SQL_PLAN·DISPLAY_CURSOR
• SQL Trace·Application Log
• 허용된 Historical Repository
• Batch·Scheduler·Report 목록
• 장애 복구 Runbook
• 소량·대량·편중 Bind

를 연결합니다.

사용 통계
  + SQL·업무 Context
  + 대체 Plan
  + Runtime 작업량
  = 제거 판단 근거

7. 숨은 의존성을 확인한다

7.1 Hint·Patch·Baseline

다음 객체·SQL이 Index 이름이나 Object Plan에 의존할 수 있습니다.

• INDEX·INDEX_DESC Hint
• SQL Patch
• SQL Plan Baseline
• Stored Outline
• Tuning Script
• Report·ETL Runbook
• 강제 Plan 검증 절차

Index가 사라지면 Hint가 무시되거나 Plan을 재현하지 못할 수 있습니다.

7.2 Auto Index

Auto Index는 Database의 Automatic Indexing 정책에 따라 생성·검증·삭제될 수 있습니다.

AUTO='YES'
  → DBMS_AUTO_INDEX 설정·Report 확인
  → 수동 Index와 목적·Visibility·Retention 구분

7.3 Schema Annotation

Oracle 26ai에서는 Index에 목적 Annotation을 남길 수 있습니다.

ALTER INDEX orders_customer_ix
ANNOTATIONS (
  purpose 'Customer order history',
  owner_team 'ORDER_PLATFORM'
);

신규·유지 Index의 목적을 문서화하면 이후 중복 판단 품질이 높아집니다.

8. Invisible Index로 Query 회귀를 검증한다

ALTER INDEX orders_x1 INVISIBLE;

기본적으로 Optimizer는 Invisible Index를 무시합니다.

ALTER SESSION
SET optimizer_use_invisible_indexes = TRUE;

검증 Session에서는 Invisible Index를 포함한 Plan도 비교할 수 있습니다.

8.1 확인할 수 있는 것

• Index가 없는 기본 Query Plan
• 다른 Index·Full Scan으로의 대체
• Query P95·Buffers·Reads·Sort 회귀
• 월말·Batch·대표 Bind의 Plan 변화
• 빠른 VISIBLE Rollback

8.2 확인할 수 없는 것

Invisible Index는 DML에서 계속 유지되고 Segment도 남습니다.

Invisible Test
  → Query Plan 의존성 확인 가능
  → Index 유지비 제거 효과 확인 불가

동일 컬럼 집합의 여러 인덱스가 있을 때 한 시점에 하나만 Visible이라는 제한도 확인합니다.

9. 후보별 Runtime 비교

같은 SQL·Bind·Fetch·동시성에서 비교합니다.

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    :sql_id,
    :child_no,
    'ALLSTATS LAST +PREDICATE +ALIAS +NOTE'
  )
);

X1이 사용되지 않음
  ≠ X1 제거 안전

X1 Invisible 후 대체 Plan 안정
  + 전체 운영 주기 통과
  + Constraint·FK·Hint 의존성 없음
  → Drop 후보 강화

10. 안전한 제거 절차

1. Index DDL·속성·Annotation·통계를 수집한다.
2. 동일 컬럼 집합·Prefix·기능 중복 후보를 분류한다.
3. PK·UK·FK·Partition·Auto Index 역할을 확인한다.
4. Usage 통계와 사용 SQL·업무 주기를 연결한다.
5. Hint·Baseline·Patch·Script 의존성을 확인한다.
6. 후보를 Invisible로 바꾸고 Query 회귀를 측정한다.
7. 정상·Peak·월말·분기·장애 복구 주기를 관찰한다.
8. 재생성 DDL·통계·권한·Rollback 시간을 준비한다.
9. 실제 Drop을 수행하고 DML·Redo·공간 개선을 측정한다.
10. Query·DML·운영 Monitoring 통과 후 종료한다.

10.1 재생성 DDL

Drop 전에 DBMS_METADATA.GET_DDL 등으로 다음을 보존합니다.

• Index DDL
• Tablespace·Storage·Compression
• Partition 정의
• Visibility
• Function Expression
• Constraint·Annotation
• Statistics 복구·수집 절차

11. Drop 후에만 확인 가능한 효과

Invisible 상태에서는 Index가 계속 유지됩니다. 실제 Drop 후 다음을 측정합니다.

DML
  → INSERT·UPDATE·DELETE Elapsed
  → Buffers·Redo·Undo
  → Lock·Hot Leaf 경합

Storage
  → Segment Size
  → Backup·통계 시간
  → Cache 점유

Query
  → 다른 Plan의 P95·P99
  → Sort·TEMP·Reads
  → Rare SQL 회귀

Drop 효과는 충분한 업무 주기 동안 관찰합니다.

12. 사례

후보

X1(customer_id, order_date)
X2(customer_id, order_date, status, amount)

사전 조사

X1
  Constraint 없음
  Leaf Blocks 12,000
  DBA_INDEX_USAGE Access 3,000,000
  고객 날짜 조회의 Fast Full·Range 사용

X2
  Leaf Blocks 48,000
  주문 상세 Query Covering
  DML Redo 증가 요인

Invisible 결과

X1 INVISIBLE

고객 날짜 Report
  X2 Range Scan
  Buffers 800 → 3,100
  P95 0.3초 → 1.4초

주문 상세
  변화 없음

결론입니다.

X1은 Prefix로 포함되지만
작은 Segment·Range 비용 때문에 별도 가치가 있음
→ 제거하지 않음

반대로 X1 Invisible 후 모든 핵심·Rare SQL이 동일하거나 개선되고 Constraint·FK·Hint 의존성이 없다면 Drop 후보가 됩니다.

13. 혼동하기 쉬운 판단

14. 최종 체크리스트

[정의]
□ Type·Uniqueness·Visibility·Auto·Status를 비교했는가
□ Column 순서·DESC·Expression을 비교했는가
□ Locality·Partition·Compression을 확인했는가
□ Leaf Blocks·BLEVEL·Clustering·통계 시점을 확인했는가

[무결성·의존성]
□ PK·UK Constraint Enforcement를 확인했는가
□ FK Index의 Parent 변경·Lock 용도를 확인했는가
□ Hint·Patch·Baseline·Script 의존성을 확인했는가

[사용]
□ DBA_INDEX_USAGE와 SQL Context를 연결했는가
□ Peak·월말·분기·복구 업무를 포함했는가
□ 소량·대량·편중 Bind를 확인했는가

[검증]
□ Invisible 상태에서 Query 회귀를 확인했는가
□ 실제 Drop 후 DML·Redo·공간을 측정했는가
□ 재생성 DDL·통계·Rollback이 준비됐는가
□ 충분한 관찰 기간과 Monitoring 기준이 있는가