함수 기반 인덱스 (Function-Based Index, FBI) in Oracle

Oracle에서 함수 기반 인덱스(FBI, Function-Based Index) 는 일반 인덱스가 적용되지 않는 경우에도 함수나 표현식 결과를 인덱스로 활용할 수 있도록 도와주는 강력한 기능입니다.

Table of Contents

1. 함수 기반 인덱스(FBI)란?

조건절에서 인덱스 컬럼에 함수를 적용하면 일반적으론 Index Range Scan이 불가능합니다. 인덱스는 가공하지 않은 값을 기준으로 정렬해서 저장한 반면, 가공한 값으로 검색하게 되면 수직적 탐색을 통해 시작점과 끝 지점을 찾을 수 없기 때문입니다. 이때 인덱스 생성 자체를 함수를 적용한 상태로 생성하는 것을 ‘함수기반 인덱스’ 라고 합니다.

✅ 일반적인 인덱스는 컬럼 값 자체에 대해서만 생성됨
✅ WHERE 절에서 컬럼에 함수나 연산을 적용하면 기존 B-tree 인덱스를 사용할 수 없음
✅ 이 문제를 해결하기 위해 컬럼 값을 변형하는 함수 결과를 인덱스로 저장하는 것이 FBI

(1) 기본 인덱스와 함수 기반 인덱스 비교

📌 기본 인덱스 (일반적인 B-tree 인덱스)

CREATE INDEX emp_idx ON employees(last_name);

SELECT * FROM employees WHERE last_name = 'SMITH';
✅ 인덱스 사용 가능
SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';
❌ 인덱스 사용 불가 (함수가 적용된 경우 일반 인덱스 미사용)

📌 함수 기반 인덱스 (FBI)

CREATE INDEX emp_fbi_idx ON employees(UPPER(last_name));

SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';
✅ 인덱스 사용 가능!

2. 함수 기반 인덱스 생성 및 사용

(1) 대소문자 변환을 위한 FBI

CREATE INDEX emp_upper_idx ON employees(UPPER(last_name));

사용 예시:

SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH'

UPPER(last_name)에 대한 인덱스가 생성되었으므로 인덱스를 사용할 수 있음

(2) 날짜 변환 함수 적용

📌 TO_CHAR을 사용한 날짜 포맷 적용

CREATE INDEX emp_date_idx ON employees(TO_CHAR(hire_date, 'YYYY-MM'));

사용 예시:

SELECT * FROM employees WHERE TO_CHAR(hire_date, 'YYYY-MM') = '2024-02';

TO_CHAR(hire_date, 'YYYY-MM') 결과값에 대한 인덱스가 적용됨

(3) 수학 연산이 포함된 인덱스

CREATE INDEX emp_salary_idx ON employees(salary * 1.1);

사용 예시:

SELECT * FROM employees WHERE salary * 1.1 > 5000;

salary * 1.1의 연산 결과를 인덱스로 저장하여 성능 최적화

(4) NULL 값을 포함하는 인덱스

기본적으로 NULL 값은 B-tree 인덱스에 저장되지 않음.
하지만 FBI를 사용하면 NULL도 포함 가능.

CREATE INDEX emp_null_idx ON employees(NVL(department_id, 0));

사용 예시:

SELECT * FROM employees WHERE NVL(department_id, 0) = 10;

NVL(department_id, 0)로 NULL을 0으로 변환하여 인덱스를 활용

3. 함수 기반 인덱스 확인 및 삭제

(1) 생성된 인덱스 확인

SELECT index_name, table_name, column_expression
FROM user_ind_expressions
WHERE table_name = 'EMPLOYEES';

(2) 함수 기반 인덱스 삭제

ROP INDEX emp_upper_idx;

4. 함수 기반 인덱스 사용 시 주의사항

✅ 인덱스 활용을 위해 반드시 동일한 함수 적용 필요

SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';  -- ✅ 인덱스 사용 가능
SELECT * FROM employees WHERE last_name = 'SMITH';  -- ❌ 인덱스 미사용

FBI를 적용한 컬럼을 조회할 때는 반드시 인덱스에 사용된 함수와 동일한 함수를 사용해야 함

✅ QUERY_REWRITE_ENABLED = TRUE 설정 필요

ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;

FBI가 자동으로 최적화되도록 설정.

✅ FBI는 B-tree 인덱스와 동일한 제한이 존재함

길이가 긴 문자열(VARCHAR2 4000 초과)에는 인덱스 적용 불가
LOB, LONG 데이터 타입에는 사용 불가
DISTINCT, GROUP BY, ORDER BY 등의 최적화에 활용 가능

5. FBI를 활용한 성능 개선 예제

📌 LIKE 연산 최적화

CREATE INDEX emp_lower_idx ON employees(LOWER(last_name));

사용 예시:

SELECT * FROM employees WHERE LOWER(last_name) LIKE 's%';

일반 인덱스가 적용되지 않는 LIKE 연산에서도 성능 개선 가능

📌 숫자 변환 최적화

CREATE INDEX emp_abs_idx ON employees(ABS(salary - 5000));

사용 예시:

SELECT * FROM employees WHERE ABS(salary - 5000) < 1000;

절대값 연산을 포함한 조건에서도 인덱스 사용 가능

6. 함수 기반 인덱스 vs 일반 인덱스 비교

구분	일반 인덱스	함수 기반 인덱스 (FBI)
지원되는 값	컬럼 원본 값	특정 함수나 연산 결과
인덱스 적용 범위	단순 비교 (`=`)	함수 적용된 조건
성능 최적화	컬럼 값 그대로 활용	함수 적용 후에도 인덱스 활용 가능
예제	`last_name = 'SMITH'`	`UPPER(last_name) = 'SMITH'`

7. 결론

✅ FBI는 WHERE 절에서 함수가 적용된 컬럼을 효율적으로 인덱싱하는 방법
✅ 일반 인덱스가 적용되지 않는 경우에도 성능 최적화 가능
✅ 사용 시 반드시 동일한 함수와 함께 사용해야 인덱스 활용 가능
✅ NULL 값도 포함할 수 있도록 변환 가능 (NVL)
✅ 대소문자 변환, 날짜 변환, 수학 연산 등 다양한 조건에서 활용 가능