[Oracle] 함수 기반 인덱스란?

함수 기반 인덱스란?

함수 기반 인덱스는 인덱스 생성 시 함수의 결과를 가지고 인덱스를 생성하는 방식을 의미한다.

원래는 쿼리 where 절에 인덱스된 열에 함수를 실행하면 인덱스가 사용되지 않는다.

 

이러한 문제 해결을 위해 Oracle 8i에서 함수 기반 인덱스가 도입 되었다.

해당 인덱스를 활용할 수 있는 쿼리가 서버로 전달되면, 해당 쿼리가 인덱스를 사용할 수 있도록 재작성된다.

 

 

컬럼 타입이 날짜(DATE) 타입이고 파라미터로 비교하는 데이터가 YYYYMM 형태의 String 문자열로 넘어온다고 가정하자.

이 경우, YYYYMM 데이터를 DATE 타입으로 변경해서 조회할 수도 있지만, DATE 타입 컬럼에 함수 기반 인덱스를 추가해서 데이터 조회 시 성능을 향상 시킬 수도 있다.

 

함수 기반 인덱스를 사용할 때는 데이터 타입이나 값, 매개 변수 등 정확한 값이 비교될 수 있게 고려를 잘 해서 인덱스를 지정해야한다.

복잡한 함수를 사용하는 경우, 오히려 성능이 저하될 수 있다.

 

 

예시

아래는 Oracle base에 있는 컬럼에 걸린 인덱스일 때, 함수 기반 인덱스일 때를 비교한 예시이다.

 

컬럼에 인덱스를 걸고, 조회 쿼리의 WHERE절에 함수를 사용하는 경우

결론부터 말하면, 이 경우에는 인덱스를 타지 않는다.

first_name이라는 컬럼 자체에 인덱스를 걸었을 경우, where절에 UPPER 함수를 쓰면 인덱스를 타지 않는다. (실행 계획을 보면 FULL SCAN을 하고 있음)

CREATE INDEX first_name_idx ON user_data (first_name);
EXEC DBMS_STATS.gather_table_stats(USER, 'user_data', cascade => TRUE);

SET AUTOTRACE ON
SELECT *
FROM   user_data
WHERE  UPPER(first_name) = 'JOHN2';

Execution Plan
----------------------------------------------------------
Plan hash value: 2489064024

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |    20 |   540 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| USER_DATA |    20 |   540 |     5   (0)| 00:00:01 |
-------------------------------------------------------------------------------

 

컬럼에 함수 기반 인덱스를 걸고, 조회 쿼리 WHERE절에 함수를 사용하는 경우

하지만 아래와 같이 UPPER 함수 적용 결과에 인덱스를 걸면(즉 함수 기반 인덱스를 걸면),

같은 쿼리 실행 시 인덱스를 타게 된다. (INDEX RANGE SCAN)

DROP INDEX first_name_idx;
CREATE INDEX first_name_idx ON user_data (UPPER(first_name));
EXEC DBMS_STATS.gather_table_stats(USER, 'user_data', cascade => TRUE);

-- Later releases set these by default.
ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED; 
ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;

SET AUTOTRACE ON
SELECT *
FROM   user_data
WHERE  UPPER(first_name) = 'JOHN2';

Execution Plan
----------------------------------------------------------
Plan hash value: 1309354431

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    36 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| USER_DATA      |     1 |    36 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | FIRST_NAME_IDX |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

 

Reference

https://www.oraexcel.com/oracle-10gR2-ORA-30554/lang-ko

https://oracle-base.com/articles/8i/function-based-indexes

https://mystyle70024.tistory.com/10