[Oracle] 오라클 ALL_IND_EXPRESSIONS 완전 분석 : 함수 기반 인덱스 추적과 활용 전략

[Oracle] ALL_IND_EXPRESSIONS

 

Oracle에서 고급 인덱스 설계를 진행하다 보면 Function-Based Index (함수 기반 인덱스)가 필수적인 경우가 많습니다. 이 인덱스는 컬럼 값에 함수 또는 표현식을 적용해 저장되므로 일반적인 인덱스 뷰만으로는 파악하기 어렵습니다. 이때 사용하는 뷰가 바로 ALL_IND_EXPRESSIONS입니다. 본 글에서는 Oracle의 ALL_IND_EXPRESSIONS 뷰를 중심으로 함수 기반 인덱스를 추적하고, 실전에서 어떻게 분석하고 튜닝에 활용할 수 있는지를 구체적으로 설명합니다.

1. ALL_IND_EXPRESSIONS란?

ALL_IND_EXPRESSIONS는 Oracle 데이터베이스에서 함수 기반 인덱스에 포함된 표현식을 보여주는 시스템 뷰입니다. 일반 컬럼 이름 대신 계산식이나 함수 호출이 저장된 인덱스를 식별하고 관리할 수 있도록 도와줍니다.

2. 주요 컬럼 설명

컬럼명	설명
INDEX_OWNER	인덱스를 소유한 사용자
INDEX_NAME	인덱스 이름
TABLE_NAME	인덱스가 적용된 테이블 이름
COLUMN_EXPRESSION	함수 기반 인덱스에 정의된 표현식
COLUMN_POSITION	인덱스 내 표현식의 순서

3. 관련 뷰 비교

뷰 이름	내용	용도
ALL_IND_EXPRESSIONS	Function-Based Index의 표현식 정보	함수 기반 인덱스 분석
ALL_IND_COLUMNS	일반 인덱스 컬럼 정보	인덱스 컬럼 확인
ALL_INDEXES	인덱스 속성 및 정보	인덱스 전체 구조 파악

4. 실무 활용 예제

4.1 모든 함수 기반 인덱스 표현식 조회

SELECT INDEX_NAME, TABLE_NAME, COLUMN_POSITION, COLUMN_EXPRESSION
FROM ALL_IND_EXPRESSIONS
ORDER BY INDEX_NAME, COLUMN_POSITION;
  

4.2 특정 테이블에 대한 함수 기반 인덱스 조회

SELECT INDEX_NAME, COLUMN_EXPRESSION
FROM ALL_IND_EXPRESSIONS
WHERE TABLE_NAME = 'CUSTOMERS'
  AND INDEX_OWNER = 'HR';
  

4.3 함수 기반 인덱스를 사용하는 인덱스 속성 확인

SELECT ie.INDEX_NAME, i.UNIQUENESS, i.STATUS, ie.COLUMN_EXPRESSION
FROM ALL_IND_EXPRESSIONS ie
JOIN ALL_INDEXES i ON ie.INDEX_NAME = i.INDEX_NAME AND ie.INDEX_OWNER = i.OWNER
WHERE i.TABLE_NAME = 'ORDERS';
  

4.4 성능 최적화를 위한 표현식 확인

SELECT *
FROM ALL_IND_EXPRESSIONS
WHERE COLUMN_EXPRESSION LIKE '%UPPER%';
  

5. 함수 기반 인덱스의 장단점

장점	단점
WHERE 절에서 표현식을 사용하는 경우 성능 향상	인덱스 생성 시 컴파일 및 성능 고려 필요
복잡한 조건에도 인덱스를 활용 가능	표현식 변경 시 인덱스 무효화 가능성
대문자/소문자 구문 일치 해결 (예: UPPER)	통계 수집 및 유지 관리가 어려움

6. 실전 활용 전략

• UPPER, LOWER, TRIM 등 자주 사용되는 함수 최적화: 검색 성능 개선 효과가 큽니다.
• WHERE 절과 표현식 일치 여부 확인: 표현식이 쿼리와 다르면 인덱스를 사용하지 않으므로 주의 필요
• 통계 정보 갱신 필수: 함수 기반 인덱스도 ANALYZE 또는 DBMS_STATS 사용하여 최신화 필요
• 표현식 복잡성 최소화: 너무 복잡한 함수 사용은 인덱스 무효화 또는 성능 저하를 초래할 수 있습니다.

7. 결론

Oracle의 ALL_IND_EXPRESSIONS 뷰는 함수 기반 인덱스를 효율적으로 파악하고 관리하는 데 핵심적인 역할을 합니다. 특히 UPPER, SUBSTR, NVL 등 자주 사용되는 SQL 함수와 함께 쿼리 성능을 높이려는 경우, 이 뷰를 통해 표현식의 구성을 확인하는 것은 필수입니다. 일반 인덱스보다 관리가 어렵지만, 적절히 활용하면 매우 강력한 성능 개선 도구가 될 수 있으므로, DBA와 개발자는 ALL_IND_EXPRESSIONS 뷰를 통해 정기적으로 인덱스를 검토하는 습관을 갖는 것이 좋습니다.

8. 참고자료

• Oracle Database 21c SQL Language Reference: https://docs.oracle.com/en/database/
• Oracle 공식 튜닝 가이드: SQL Tuning with Indexes
• Oracle SQL Developer 도구 활용 사례