[ORACLE] ALL_INDEXES 뷰로 인덱스 구조 완벽 분석하기

[Oracle] ALL_INDEXES

 

Oracle Database의 성능 최적화에서 인덱스(Index)는 핵심 요소 중 하나입니다. 테이블에 생성된 인덱스 정보를 확인하고 분석하기 위해 Oracle은 ALL_INDEXES라는 시스템 뷰를 제공합니다. 본 글에서는 ALL_INDEXES의 구조와 주요 컬럼, 실무 활용법, 성능 분석 포인트까지 상세히 설명하겠습니다.

1. ALL_INDEXES란 무엇인가?

ALL_INDEXES는 사용자가 접근할 수 있는 모든 테이블 및 인덱스에 대한 메타데이터 정보를 제공하는 Oracle 시스템 뷰입니다. 해당 뷰를 통해 인덱스의 이름, 유형, 소유자, 유니크 여부, 파티셔닝 여부, 컴프레션 여부 등을 파악할 수 있으며, 성능 문제를 사전에 진단하는 데 매우 유용합니다.

2. 주요 컬럼 설명

ALL_INDEXES 뷰에서 자주 사용되는 주요 컬럼은 다음과 같습니다:

컬럼명	설명
OWNER	인덱스를 소유한 사용자
INDEX_NAME	인덱스 이름
TABLE_NAME	해당 인덱스가 적용된 테이블 이름
INDEX_TYPE	인덱스 유형 (NORMAL, BITMAP 등)
UNIQUENESS	UNIQUE 여부 (UNIQUE/NONUNIQUE)
TABLE_TYPE	테이블 종류 (TABLE/CLUSTER 등)
PARTITIONED	파티셔닝 여부 (YES/NO)
COMPRESSION	압축 사용 여부 (ENABLED/DISABLED)

3. USER_INDEXES, ALL_INDEXES, DBA_INDEXES 비교

Oracle은 인덱스 정보를 확인할 수 있는 여러 뷰를 제공합니다. 아래 표는 주요 인덱스 관련 뷰들의 차이를 정리한 것입니다:

뷰 이름	접근 권한	내용
USER_INDEXES	자신이 소유한 인덱스만 조회	개인 개발자 또는 일반 사용자용
ALL_INDEXES	접근 가능한 모든 인덱스	협업 환경에서 타인 인덱스도 확인 가능
DBA_INDEXES	DBA 권한 필요	DB 전체 인덱스를 관리할 때 사용

4. 실무 활용 예제

4.1 특정 테이블에 적용된 인덱스 목록 조회

SELECT INDEX_NAME, UNIQUENESS, INDEX_TYPE
FROM ALL_INDEXES
WHERE TABLE_NAME = 'EMPLOYEES'
  AND OWNER = 'HR';
  

4.2 UNIQUE 인덱스만 조회

SELECT INDEX_NAME, TABLE_NAME
FROM ALL_INDEXES
WHERE UNIQUENESS = 'UNIQUE';
  

4.3 파티셔닝된 인덱스 확인

SELECT INDEX_NAME, TABLE_NAME
FROM ALL_INDEXES
WHERE PARTITIONED = 'YES';
  

4.4 압축이 적용된 인덱스 확인

SELECT INDEX_NAME, COMPRESSION
FROM ALL_INDEXES
WHERE COMPRESSION = 'ENABLED';
  

5. ALL_INDEXES 관련 보조 뷰

• ALL_IND_COLUMNS: 인덱스에 포함된 컬럼 정보
• ALL_IND_EXPRESSIONS: 함수 기반 인덱스 정의 정보
• ALL_IND_PARTITIONS: 파티션 인덱스 정보
• ALL_TAB_COLUMNS: 테이블 컬럼 정보 (인덱스 구성 시 유용)

6. 성능 최적화를 위한 실무 팁

• 인덱스가 너무 많거나 불필요하게 중복되어 있는 경우, 오히려 성능 저하를 유발할 수 있습니다.
• 데이터가 자주 변경되는 테이블에서는 BITMAP 인덱스를 피하는 것이 좋습니다.
• 파티션 인덱스는 대량의 데이터를 처리하는 경우 유용하지만, 계획적 설계가 필수입니다.
• UNIQUE 인덱스를 사용할 때는 반드시 비즈니스 로직 상 고유한 컬럼을 선택해야 합니다.

7. 인덱스 유형 비교

인덱스 유형	설명	사용 예시
NORMAL	기본 B*Tree 인덱스	일반 SELECT 쿼리 가속
BITMAP	비트맵 인덱스	카디널리티가 낮은 컬럼
FUNCTION-BASED	함수를 적용한 인덱스	조건식에 함수가 포함될 경우
DOMAIN	사용자 정의 타입 인덱스	Oracle Text 등 특수 기능

8. 결론

ALL_INDEXES 뷰는 Oracle 데이터베이스 성능 최적화와 구조 분석을 위한 필수 도구입니다. 인덱스는 SELECT 쿼리의 성능에 직접적인 영향을 주며, 잘못된 인덱스 설계는 오히려 병목을 유발할 수 있습니다. 따라서 ALL_INDEXES와 그 보조 뷰들을 능숙하게 활용하는 것이 고성능 데이터베이스 운영의 핵심입니다.

9. 참고자료

• Oracle Database Reference 21c: https://docs.oracle.com/en/database/
• 실제 운영 환경 DB 튜닝 사례 및 프로젝트 문서
• Oracle SQL Tuning Guide