[ORACLE] ALL_IND_PARTITIONS 뷰로 인덱스 파티션 분석 및 관리 전략

[Oracle] ALL_IND_PARTITIONS

 

Oracle 데이터베이스에서 파티셔닝된 인덱스를 효과적으로 관리하려면, ALL_IND_PARTITIONS 뷰는 필수 도구입니다. 이 뷰는 사용자가 접근할 수 있는 인덱스 파티션의 저장 정보와 통계를 제공합니다.

1. ALL_IND_PARTITIONS란?

ALL_IND_PARTITIONS는 현재 사용자가 접근할 수 있는 모든 인덱스 파티션에 대한 정보를 제공합니다. 각 파티션의 이름, 저장소 설정, 통계, 상태 등을 확인할 수 있습니다.

2. 주요 컬럼 설명

컬럼명	설명
INDEX_OWNER / INDEX_NAME	인덱스 소유자 및 이름
PARTITION_NAME / PARTITION_POSITION	파티션 이름과 순서
STATUS	USABLE 또는 UNUSABLE 상태
TABLESPACE_NAME	파티션이 저장된 테이블스페이스
PCT_FREE, INITIAL_EXTENT 등	저장소 설정(블록 여유 공간, 익스텐트 크기 등)
BLEVEL, LEAF_BLOCKS	B*-트리 깊이와 리프 블록 수
DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY	키 분포와 통계 정보
CLUSTERING_FACTOR	클러스터링도 (쿼리 성능 지표)
NUM_ROWS, LAST_ANALYZED	행 수 및 최근 통계 수집 날짜
BUFFER_POOL, FLASH_CACHE	버퍼 및 플래시 캐시 정책

3. 관련 뷰와의 비교

뷰 이름	기능	권한
USER_IND_PARTITIONS	본인 소유 인덱스 파티션 정보	일반 사용자
ALL_IND_PARTITIONS	접근 가능한 파티션 정보	일반 사용자
DBA_IND_PARTITIONS	전체 DB 파티션 정보	DBA 권한

4. 주요 실무 활용 예제

4.1 특정 인덱스의 모든 파티션 상태 조회

SELECT PARTITION_NAME, STATUS, NUM_ROWS, LAST_ANALYZED
FROM ALL_IND_PARTITIONS
WHERE INDEX_OWNER = 'HR'
  AND INDEX_NAME = 'IDX_EMP_DATE';
  

4.2 분석되지 않은 파티션 통계 확인

SELECT INDEX_NAME, PARTITION_NAME
FROM ALL_IND_PARTITIONS
WHERE LAST_ANALYZED IS NULL;
  

4.3 UNUSABLE 상태인 파티션 재활용 대상 검색

SELECT INDEX_NAME, PARTITION_NAME
FROM ALL_IND_PARTITIONS
WHERE STATUS = 'UNUSABLE';
  

4.4 클러스터링 팩터가 높은 파티션 식별

SELECT INDEX_NAME, PARTITION_NAME, CLUSTERING_FACTOR
FROM ALL_IND_PARTITIONS
WHERE CLUSTERING_FACTOR > 100000;
  

4.5 저장소 설정 비교

SELECT INDEX_NAME, PARTITION_NAME, PCT_FREE, INITIAL_EXTENT, NEXT_EXTENT
FROM ALL_IND_PARTITIONS
ORDER BY INDEX_NAME, PARTITION_POSITION;
  

5. 실무 전략 및 주의사항

• 정기적인 통계 갱신: LAST_ANALYZED 기준으로 ANALYZE 또는 DBMS_STATS 실행.
• UNUSABLE 파티션 관리: 해당 파티션의 인덱스를 재생성하거나 REBUILD.
• 클러스터링 최적화: 순차적 데이터 접근에 유리한 인덱스 순서 구성 확인.
• 저장소 일관성 검증: 모든 파티션이 동일한 PCT_FREE/EXTENT 설정인지 점검.
• 버퍼 정책 조정: 고빈도 접근 파티션은 KEEP 버퍼 또는 FLASH_CACHE 할당 고려.

6. 요약: 활용 목적별 컬럼 중심 비교

활용 목적	중요 컬럼	설명
통계 점검	NUM_ROWS, LAST_ANALYZED	파티션별 분석 상태 확인
상태 관리	STATUS	UNUSABLE 여부파악
성능 최적화	CLUSTERING_FACTOR, BLEVEL	쿼리 실행 효율 예측
저장소 균일화	PCT_FREE, INITIAL_EXTENT	파티션 간 공간 정책 비교
캐시 구성	BUFFER_POOL, FLASH_CACHE	메모리/디스크 I/O 전략 확인

7. 결론

ALL_IND_PARTITIONS 뷰는 인덱스 파티션 단위의 통계와 저장소 구성, 상태 등을 한눈에 파악할 수 있어 데이터베이스 성능 최적화와 유지보수에 매우 유용한 정보입니다. 정기적인 활용 계획을 세우고, DBA_IND_PARTITIONS/ALL_INDEXES 등과 함께 운영하면 안정적인 대규모 DB 운영이 가능합니다.

8. 참고자료

• Oracle Database Reference (10g/19c)
• Oracle VLDB & Partitioning Guide
• StackOverflow – Partitioned index rebuild 실무 예