오라클 함수 기반 인덱스(Function-Based Index) 가이드

인덱스를 잘 만들어 놓고도 쿼리가 풀 스캔을 타는 경험, 한 번쯤 있으실 겁니다. 원인 중 상당수는 WHERE 절에서 컬럼을 함수로 감싸버렸기 때문입니다. 이럴 때 필요한 것이 바로 함수 기반 인덱스(Function-Based Index, FBI) 입니다. 이 글에서는 함수 기반 인덱스의 개념부터 생성 방법, 실무에서 주의할 점까지 정리합니다.

1. 왜 함수 기반 인덱스가 필요한가

일반 B-Tree 인덱스는 컬럼의 원본 값 을 정렬해서 저장합니다. 그런데 쿼리에서 컬럼을 가공하면 어떻게 될까요?

-- EMP 테이블의 ENAME 컬럼에 일반 인덱스가 있다고 가정
CREATE INDEX idx_emp_ename ON emp(ename);

-- 이 쿼리는 인덱스를 사용하지 못한다!
SELECT * FROM emp WHERE UPPER(ename) = 'SMITH';

인덱스에는 Smith, smith, SMITH 같은 원본 값이 저장되어 있는데, 옵티마이저는 UPPER(ename)의 결과값을 인덱스에서 찾을 수 없습니다. 결국 테이블 풀 스캔이 발생합니다.

함수 기반 인덱스는 이 문제를 해결합니다. 함수나 표현식의 연산 결과값을 미리 계산해서 인덱스에 저장 해 두는 방식입니다.

2. 기본 생성 방법

-- UPPER 함수 결과를 인덱싱
CREATE INDEX idx_emp_ename_upper ON emp(UPPER(ename));

-- 이제 이 쿼리는 인덱스를 탄다
SELECT * FROM emp WHERE UPPER(ename) = 'SMITH';

함수뿐 아니라 산술 표현식도 가능합니다.

-- 연봉 계산식을 인덱싱
CREATE INDEX idx_emp_annual_sal ON emp((sal + NVL(comm, 0)) * 12);

SELECT * FROM emp WHERE (sal + NVL(comm, 0)) * 12 > 50000000;

사용자 정의 함수도 인덱싱할 수 있습니다. 단, 반드시 DETERMINISTIC으로 선언되어야 합니다.

CREATE OR REPLACE FUNCTION get_grade(p_sal NUMBER)
RETURN VARCHAR2
DETERMINISTIC
IS
BEGIN
  RETURN CASE WHEN p_sal >= 5000 THEN 'A'
              WHEN p_sal >= 3000 THEN 'B'
              ELSE 'C' END;
END;
/

CREATE INDEX idx_emp_grade ON emp(get_grade(sal));

DETERMINISTIC은 "같은 입력에 대해 항상 같은 결과를 반환한다"는 약속입니다. SYSDATE나 시퀀스처럼 호출할 때마다 결과가 달라지는 요소가 함수 안에 있으면 인덱스와 실제 값이 어긋나 잘못된 결과가 나올 수 있으므로 주의해야 합니다.

3. 자주 쓰이는 실무 패턴

대소문자 구분 없는 검색

CREATE INDEX idx_member_email ON member(LOWER(email));

SELECT * FROM member WHERE LOWER(email) = 'hong@example.com';

날짜 컬럼의 일자 단위 조회

CREATE INDEX idx_order_date ON orders(TRUNC(order_date));

SELECT * FROM orders WHERE TRUNC(order_date) = DATE '2026-07-25';

다만 이 경우에는 함수 기반 인덱스보다 범위 조건으로 바꾸는 것 이 더 좋은 선택일 때가 많습니다.

-- 일반 인덱스로도 처리 가능한 형태
SELECT * FROM orders
WHERE order_date >= DATE '2026-07-25'
  AND order_date <  DATE '2026-07-26';

기존 쿼리를 수정할 수 없는 상황(패키지 솔루션, 레거시 등)에서 함수 기반 인덱스가 진가를 발휘합니다.

NULL 값 인덱싱

일반 B-Tree 인덱스는 모든 컬럼이 NULL인 행을 저장하지 않습니다. 함수 기반 인덱스로 이를 우회할 수 있습니다.

CREATE INDEX idx_emp_comm ON emp(NVL(comm, -1));

SELECT * FROM emp WHERE NVL(comm, -1) = -1;  -- comm이 NULL인 행 조회

컬럼 일부만 인덱싱

CREATE INDEX idx_phone_prefix ON member(SUBSTR(phone, 1, 3));

SELECT * FROM member WHERE SUBSTR(phone, 1, 3) = '010';

4. 동작 원리: 숨겨진 가상 컬럼

함수 기반 인덱스를 생성하면 오라클은 내부적으로 숨겨진 가상 컬럼(hidden virtual column) 을 만들고, 그 컬럼에 인덱스를 겁니다. USER_TAB_COLS 뷰를 조회하면 SYS_NC00009$ 같은 이름의 컬럼을 확인할 수 있습니다.

SELECT column_name, data_default, hidden_column
FROM user_tab_cols
WHERE table_name = 'EMP';

이 구조 때문에 중요한 포인트가 하나 생깁니다. 바로 통계정보 수집 입니다. 인덱스 생성 후 히든 컬럼에 대한 통계가 없으면 옵티마이저가 선택도를 잘못 판단할 수 있으므로, 인덱스 생성 후 반드시 통계를 갱신하세요.

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMP', method_opt => 'FOR ALL HIDDEN COLUMNS SIZE AUTO');

전체 흐름을 그림으로 보면 다음과 같습니다.

5. 사용 조건과 확인 방법

함수 기반 인덱스가 실제로 사용되려면 다음 조건이 맞아야 합니다.

1. 쿼리의 표현식이 인덱스 정의와 정확히 일치 해야 합니다. UPPER(ename)으로 인덱스를 만들었는데 쿼리에서 NLS_UPPER(ename)을 쓰면 인덱스를 타지 않습니다.
2. 세션/시스템 파라미터 QUERY_REWRITE_ENABLED = TRUE가 필요합니다(10g 이상에서는 기본값이 TRUE라 보통 신경 쓸 일이 없습니다).
3. 사용자 정의 함수라면 DETERMINISTIC 선언이 필수입니다.

실행계획으로 확인해 봅시다.

EXPLAIN PLAN FOR
SELECT * FROM emp WHERE UPPER(ename) = 'SMITH';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- INDEX RANGE SCAN | IDX_EMP_ENAME_UPPER 가 보이면 성공

만약 인덱스를 만들었는데도 풀 스캔이 나온다면, 힌트로 인덱스 사용을 강제 해서 원인을 좁혀볼 수 있습니다.

SELECT /*+ INDEX(e idx_emp_ename_upper) */ *
FROM emp e
WHERE UPPER(ename) = 'SMITH';

힌트를 줬는데도 인덱스를 안 탄다면 표현식 불일치 등 인덱스 자체를 사용할 수 없는 상황이고, 힌트를 주면 타는데 안 주면 안 탄다면 옵티마이저가 비용 계산상 풀 스캔이 유리하다고 판단한 것입니다. 이처럼 힌트는 강제 수단이기 이전에 훌륭한 진단 도구 입니다.

6. 주의사항과 단점

DML 오버헤드가 커집니다. 다만 테이블 전체가 아니라 ** 인덱스 표현식에 포함된 컬럼을 건드릴 때** 이야기입니다. INSERT는 항상 함수 계산이 발생하지만, UPDATE는 표현식에 쓰인 컬럼을 변경할 때만 재계산됩니다. 예를 들어 UPPER(ename) 인덱스가 있어도 sal만 수정하는 UPDATE는 이 인덱스에 아무 영향이 없습니다. 단, (sal + NVL(comm, 0)) * 12처럼 여러 컬럼을 참조하는 표현식은 그중 하나만 바뀌어도 재계산되며, 무거운 사용자 정의 함수라면 이 비용이 특히 체감됩니다.

함수를 변경하면 인덱스가 무효화됩니다. 인덱스가 참조하는 사용자 정의 함수를 재컴파일하면 인덱스가 DISABLED 상태가 되고, 해당 인덱스를 쓰는 쿼리가 에러(ORA-30554)를 낼 수 있습니다. 함수 수정 후에는 인덱스를 리빌드하거나 활성화해야 합니다.

ALTER INDEX idx_emp_grade ENABLE;
-- 또는
ALTER INDEX idx_emp_grade REBUILD;

표현식 통일이 필요합니다. 개발자마다 UPPER, LOWER, INITCAP을 제각각 쓰면 인덱스를 여러 개 만들거나 인덱스를 못 타는 쿼리가 생깁니다. 팀 차원의 코딩 표준으로 표현식을 통일하는 것이 좋습니다.

통계가 부실하면 옵티마이저가 인덱스를 외면합니다. 앞서 본 히든 컬럼 통계가 없거나 낡으면, 멀쩡한 함수 기반 인덱스도 실행계획에서 배제될 수 있습니다. 급한 불은 INDEX 힌트로 끌 수 있지만, 힌트는 어디까지나 임시 대응입니다. 근본 해결은 통계 수집이라는 점을 잊지 마세요.

근본 해결이 아닐 수 있습니다. 컬럼 가공은 가급적 상수 쪽으로 옮기는 것이 원칙입니다. WHERE TO_CHAR(order_date, 'YYYYMMDD') = '20260725'보다 WHERE order_date >= ... AND order_date < ...가 낫습니다. 함수 기반 인덱스는 쿼리를 고칠 수 없을 때의 훌륭한 대안이지, 무분별한 컬럼 가공을 정당화하는 도구는 아닙니다.

7. 정리

함수 기반 인덱스는 "쿼리를 못 고치는 상황"에서 성능 문제를 해결해 주는 강력한 도구입니다. 다만 만들기 전에 항상 "쿼리를 고쳐서 일반 인덱스로 해결할 수는 없는가?"를 먼저 자문해 보시길 권합니다.