[ORACLE] ALL_PART_INDEXES 뷰 완전 이해하기

1. ALL_PART_INDEXES란 무엇인가?

Oracle 데이터베이스에서 ALL_PART_INDEXES는 사용자가 접근할 수 있는 모든 파티션 인덱스에 대한 정보를 제공하는 데이터 딕셔너리 뷰입니다. 이 뷰를 활용하면 파티션 테이블의 인덱스 구조, 파티션 방법, 로컬/글로벌 인덱스 여부 등을 확인할 수 있어 데이터베이스 성능 튜닝 및 인덱스 전략 수립에 매우 유용합니다.

2. 주요 컬럼 설명

컬럼명	설명
INDEX_NAME	인덱스의 이름
TABLE_NAME	해당 인덱스가 적용된 테이블 이름
PARTITIONING_TYPE	인덱스의 파티셔닝 방식 (RANGE, LIST, HASH 등)
LOCALITY	인덱스가 LOCAL인지 GLOBAL인지 여부
STATUS	인덱스 상태 (VALID, UNUSABLE 등)
TABLE_OWNER	인덱스 대상 테이블의 소유자
INDEX_TYPE	인덱스 유형 (NORMAL, BITMAP 등)

3. 사용 목적과 실무에서의 활용

• 대규모 데이터셋에 대해 효율적인 쿼리 실행을 위한 인덱스 구조 분석
• 파티션 테이블과 연동된 인덱스의 LOCAL 또는 GLOBAL 전략 확인
• 인덱스 상태 모니터링 (UNUSABLE 인덱스 발견 시 재빌드 필요)
• 자동 통계 수집 정책 수립 및 인덱스 재구성 시 활용

4. 활용 예제

특정 테이블의 파티션 인덱스 정보를 조회하는 SQL 예제:

SELECT 
  index_name, 
  partitioning_type, 
  locality, 
  status, 
  index_type
FROM 
  all_part_indexes
WHERE 
  table_name = 'SALES'
  AND table_owner = 'HR';
      

이 결과를 통해 SALES 테이블에 설정된 인덱스의 파티션 방식과 상태를 확인할 수 있습니다.

5. LOCAL vs GLOBAL 인덱스 비교

구분	LOCAL INDEX	GLOBAL INDEX
정의 방식	파티션 단위로 인덱스를 생성	전체 테이블을 대상으로 인덱스 생성
재빌드 대상	파티션 단위로 독립 재빌드 가능	파티션 변경 시 전체 인덱스 재빌드 필요
관리 용이성	높음	낮음
성능	파티션 액세스에 최적화	전체 범위 쿼리에 유리
적용 대상	분리된 파티션 쿼리	크로스 파티션 조인

6. 인덱스 튜닝을 위한 체크리스트

1. 인덱스 상태 점검: UNUSABLE 상태 인덱스는 성능 저하 원인. 반드시 재빌드 필요.
2. 파티션 전략 점검: 테이블의 파티션 방식과 일치하는 인덱스 전략 적용.
3. 인덱스 스캔 방식 분석: 실행 계획에서 INDEX RANGE SCAN, INDEX FULL SCAN 등으로 사용 여부 확인.
4. 쿼리 빈도 기반 튜닝: 조회 빈도가 높은 컬럼에 대해 BITMAP 또는 함수 기반 인덱스 고려.
5. 모니터링 도구 활용: AWR, ADDM 보고서 기반 인덱스 활용도 분석.

7. 인덱스 재작성 시 참고 사항

대규모 테이블에서 인덱스를 재작성할 경우, ONLINE 모드 사용을 고려해 시스템 부하를 최소화해야 합니다. 또한 PARALLEL 옵션을 적절히 활용하면 재작업 시간을 단축할 수 있습니다.

ALTER INDEX idx_sales_region REBUILD ONLINE PARALLEL 4;
      

8. 결론

ALL_PART_INDEXES 뷰는 오라클 데이터베이스에서 파티션 인덱스를 효율적으로 관리하고 성능을 최적화하는 데 핵심적인 역할을 합니다. 인덱스의 구조와 상태를 명확히 파악함으로써 DBA는 불필요한 리소스 낭비를 줄이고, 쿼리의 성능을 향상시킬 수 있습니다. 특히 파티션된 테이블을 사용하는 환경에서는 LOCAL과 GLOBAL 인덱스 전략을 적절히 병행하여 최적의 인덱스 설계를 해야 합니다.

9. 참고 자료

• Oracle Database Reference 21c - ALL_PART_INDEXES
• Oracle Indexing Best Practices - Oracle Corporation
• Oracle Performance Tuning Guide
• Oracle Optimizer Blog - https://blogs.oracle.com/optimizer