Oracle Index 문제 (Fragmentation 등)

인덱스(Index)는 데이터 검색 성능을 향상시키는 중요한 구조이지만, 시간이 지나면서 단편화(Fragmentation), 희소성(Sparse), 비대화(Bloat), 데이터 불균형(Skew) 등의 문제로 인해 성능이 저하될 수 있습니다.
이 문서에서는 Index Fragmentation(인덱스 단편화) 를 비롯한 여러 인덱스 관련 문제를 정리하고, 원인과 해결 방법을 설명합니다.

1. Index Fragmentation (인덱스 단편화)

(1) Index Fragmentation이란?

• 인덱스 블록이 비효율적으로 사용되는 현상.
• DELETE 및 UPDATE 연산으로 인해 빈 블록이 남아 있음.
• B-tree 인덱스에서 특히 많이 발생하며, 리프 블록의 공간 낭비로 인해 성능 저하.

(2) 발생 원인

✅ DELETE 후 블록이 재사용되지 않음
✅ UPDATE 시 ROWID 변경으로 인해 새로운 블록 할당
✅ INSERT가 불규칙적으로 이루어지면서 블록 간 균형이 깨짐
✅ 고빈도 데이터 변경 작업으로 인해 비효율적인 공간 사용

(3) 문제 확인 방법

SELECT index_name, blevel, leaf_blocks, distinct_keys, clustering_factor
FROM user_indexes
WHERE table_name = '고객';

• BLEVEL: 인덱스 깊이 (높을수록 성능 저하 가능성 높음).
• LEAF_BLOCKS: 인덱스가 차지하는 블록 수 (너무 크면 단편화 가능성).
• CLUSTERING_FACTOR: 테이블과 인덱스의 정렬 정도 (값이 높을수록 테이블 정렬이 필요).

💡 BLEVEL 값이 높고, LEAF_BLOCKS가 증가했으며, CLUSTERING_FACTOR가 증가하면 단편화 가능성 높음.

(4) 해결 방법

✅ Index Coalesce (공간 병합)

ALTER INDEX 고객_PK COALESCE;

✅ Index Rebuild (완전 재구성)

ALTER INDEX 고객_PK REBUILD;

✅ 새로운 테이블 생성 후 교체

CREATE TABLE 고객_NEW AS SELECT * FROM 고객;
DROP TABLE 고객;
ALTER TABLE 고객_NEW RENAME TO 고객;

2. Index Sparse (희소 인덱스)

(1) Index Sparse란?

• DELETE 및 UPDATE로 인해 인덱스 블록 내에서 미사용 공간이 증가하는 현상.
• 사용되지 않는 인덱스 공간이 많아 인덱스 크기가 불필요하게 커짐.

(2) 발생 원인

✅ DELETE 후 인덱스 블록이 재사용되지 않음
✅ UPDATE로 인해 기존 ROWID가 변경되면서 새로운 블록이 할당됨
✅ INSERT, DELETE, UPDATE 연산이 반복되면서 빈 블록(희소 블록)이 증가
✅ 데이터가 고르게 분포되지 않고 특정 값에 집중될 경우 발생 가능

(3) 문제 확인 방법

SELECT index_name, table_name, blocks, empty_blocks
FROM user_segments
WHERE segment_type = 'INDEX'
AND table_name = '고객';

• BLOCKS : 인덱스가 차지하는 전체 블록 수
• EMPTY_BLOCKS : 사용되지 않는 빈 블록 수

💡 EMPTY_BLOCKS 값이 높으면, Sparse Index 문제가 존재할 가능성이 큼

(4) 해결 방법

✅ Index Coalesce (블록 병합)

ALTER INDEX 고객_PK COALESCE;

✅ Index Rebuild (완전 재구성)

ALTER INDEX 고객_PK REBUILD;

3. Index Bloat (인덱스 비대화)

(1) Index Bloat이란?

• DELETE, UPDATE 후 불필요한 공간이 남아 인덱스 크기가 불필요하게 커지는 현상.
• 실제 데이터보다 인덱스 크기가 훨씬 커지면서 성능 저하 발생.

(2) 문제 확인

SELECT index_name, table_name, leaf_blocks, blevel
FROM user_indexes
WHERE table_name = '고객';

BLEVEL이 증가하면 인덱스가 과도하게 커진 것(비대화) 가능성 있음.

(3) 해결 방법

✅ 불필요한 인덱스 삭제 (DROP INDEX)

DROP INDEX 고객_PK;

✅ 주기적으로 ALTER INDEX ... REBUILD 실행

ALTER INDEX 고객_PK REBUILD;

4. Index Skew (데이터 불균형)

(1) Index Skew란?

• 인덱스가 특정 키 값에 과도하게 집중되는 현상.
• 옵티마이저가 잘못된 실행 계획을 선택할 가능성이 높아짐.

(2) 문제 확인

SELECT indexed_column, COUNT(*)
FROM 고객
GROUP BY indexed_column
ORDER BY COUNT(*) DESC;

• 특정 컬럼 값에 데이터가 과도하게 집중되면 SKEW 현상 발생 가능성 높음.

(3) 해결 방법

✅ 히스토그램(Histogram) 생성

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname => 'SCHEMA_NAME',
        tabname => '고객',
        method_opt => 'FOR COLUMNS indexed_column SIZE 254'
    );
END;

✅ 비트맵 인덱스(Bitmap Index) 사용

CREATE BITMAP INDEX 고객_bitmap_idx ON 고객(성별);

✅ 파티셔닝(Partitioning) 활용

CREATE INDEX 고객_idx 
ON 고객(가입일) 
LOCAL;

5. Unusable Index (사용 불가능한 인덱스)

(1) Unusable Index란?

• 대량 데이터 변경 후 인덱스가 UNUSABLE 상태가 되는 문제.
• 주로 TRUNCATE TABLE, LOAD DATA, INSERT /*+ APPEND */ 사용 시 발생.

(2) 문제 확인

SELECT index_name, status 
FROM user_indexes 
WHERE table_name = '고객';

• STATUS가 UNUSABLE이면 문제 발생.

(3) 해결 방법

✅ 인덱스 재구성 (REBUILD)

ALTER INDEX 고객_PK REBUILD;

✅ 모든 인덱스 활성화

ALTER INDEX ALL ON 고객 REBUILD;

6. 인덱스 문제별 해결 방법 요약

문제	원인	해결 방법
Index Fragmentation	DELETE/UPDATE로 인한 단편화	ALTER INDEX ... COALESCE, ALTER INDEX ... REBUILD
Index Sparse	사용되지 않는 블록 존재	ALTER INDEX ... COALESCE
Index Bloat	인덱스 크기 증가	DROP INDEX, ALTER INDEX ... REBUILD
Index Skew	특정 키 값에 데이터 집중	히스토그램 생성, 비트맵 인덱스, 파티셔닝
Unusable Index	TRUNCATE, APPEND로 인한 문제	ALTER INDEX ... REBUILD

---

7. 결론

✅ 정기적인 인덱스 모니터링 필요 (BLEVEL, LEAF_BLOCKS 확인)
✅ 불필요한 인덱스 삭제 (DROP INDEX) 및 인덱스 최적화 (REBUILD, COALESCE) 수행
✅ 데이터 분포 분석 및 적절한 인덱싱 전략 활용
✅ 테이블 변경이 많을 경우 BITMAP INDEX, PARTITIONING 등의 대안 검토