[Oracle] Index coalesce vs Index rebuild

INDEX COALESCE의 특징

1. 단편화 최소화
   • COALESCE는 인덱스의 리프 블록(leaf block)들 중에서 사용되지 않는 공간(프리 블록)을 병합하여 단편화를 줄입니다.
   • 인덱스 구조는 유지되며, 불필요한 I/O를 줄일 수 있습니다.
2. 온라인 작업 지원
   • COALESCE 작업 중에도 DML(INSERT, UPDATE, DELETE)이 가능하여 OLTP 환경에서 적합합니다.
   • 데이터베이스의 가용성을 보장합니다.
3. 전체 재구성 없음
   • 인덱스를 처음부터 다시 생성하지 않으므로, 리빌드에 비해 리소스 소모가 적습니다.
   • 단편화 문제를 해결하는 목적이라면 COALESCE가 리빌드보다 효율적입니다.
4. 부분 작업 가능
   • COALESCE는 파티셔닝된 인덱스에서 특정 파티션만 작업할 수 있습니다.

---

COALESCE와 REBUILD의 비교

특성                                                 COALESCE                                      REBUILD

단편화 해결 방식	여유 공간 병합	전체 인덱스 재생성
자원 소모	상대적으로 낮음	상대적으로 높음
온라인 작업	지원 (동시 DML 가능)	지원 (일부 제한적 잠금 발생)
성능 최적화	제한적	더 많은 성능 최적화 가능
작업 시간	더 짧음	더 길어질 수 있음
Redo/Undo 생성	적음	많음

---

INDEX COALESCE 사용 방법

1. 일반 인덱스에서 COALESCE 실행

 

ALTER INDEX <index_name> COALESCE;

2. 파티셔닝된 인덱스에서 특정 파티션에 대해 COALESCE 실행

 

ALTER INDEX <index_name> MODIFY PARTITION <partition_name> COALESCE;

3. COALESCE 수행 시 사용 가능한 추가 옵션

• PARALLEL: 병렬 처리를 통해 작업 속도를 높일 수 있습니다.
   

  ALTER INDEX <index_name> COALESCE PARALLEL 4;
• MONITORING USAGE: 작업 중 인덱스 사용 통계를 활성화하여 분석에 활용할 수 있습니다.

---

COALESCE를 사용하는 시점

COALESCE는 다음과 같은 상황에서 유용합니다:

1. 인덱스가 단편화된 경우
   • 예를 들어, DELETE 작업이 많이 발생해 리프 블록에 사용되지 않는 공간이 많을 때.
2. 리빌드 대신 가벼운 유지보수가 필요한 경우
   • 성능 최적화가 필요하지만 리소스 소모를 최소화해야 할 때.
3. OLTP 환경에서 작업 중 서비스 중단이 불가능한 경우
   • COALESCE는 DML과 병행하여 실행할 수 있으므로 OLTP 환경에서 적합합니다.

---

COALESCE의 한계

• COALESCE는 루트 블록이나 브랜치 블록에는 영향을 미치지 않으며, 리프 블록 단위에서만 작동합니다.
• 단편화 문제를 해결할 수는 있지만, 전체 인덱스 구조 최적화에는 한계가 있습니다.
• 만약 인덱스가 지나치게 크거나 오래된 경우에는 REBUILD가 더 적합할 수 있습니다.

---

결론

INDEX COALESCE는 단편화를 줄이고 인덱스 효율성을 높이기 위해 가볍고 자원 친화적인 방법으로 설계되었습니다. OLTP 환경에서 인덱스 유지보수를 수행해야 하는 경우, 리빌드보다 서비스 영향이 적고 빠르게 수행할 수 있는 좋은 대안입니다. 다만, 단편화가 심하지 않거나 전체 구조 최적화가 필요하지 않을 때 사용하는 것이 이상적입니다.