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 등의 대안 검토