How to create function based index and how it works

Suppose a query is running slow. You examine the WHERE clause and notice that a SQL UPPER function has been applied to a column. The UPPER function blocks the use of the existing index on that column. You want to create a function-based index to support the query. Here’s an example of such a query:

SELECT first_name
FROM cust
WHERE UPPER(first_name) = 'DAVE';

You inspect USER_INDEXES and discover that an index exists on the FIRST_NAME column:

select index_name, column_name
from user_ind_columns
where table_name = 'CUST';

INDEX_NAME COLUMN_NAME
-------------------- --------------------
   CUST_IDX1    FIRST_NAME


You generate an explain plan via SET AUTOTRACE TRACE EXPLAIN and notice that with the UPPER function applied to the column, the index is not used:

You need to create an index that Oracle will use in this situation.

So here are the solution. There are two ways to resolve this issue:

• Create a function-based index.

• If using Oracle Database 11g or higher, create an indexed virtual column.

This solution focuses on using a function-based index. You create a function-based index by
referencing the SQL function and column in the index creation statement. For this example, a functionbased index is created on UPPER(name):

SQL> creae index cust_fidx1 on cust(UPPER(first_name));

To verify if the index is used, the Autotrace facility is turned on:

SQL> set autotrace trace explain;

Now the query is executed:

SELECT first_name
FROM cust
WHERE UPPER(first_name) = 'DAVE';

Here is the resulting execution plan showing that the function-based index is used:

How It Works

Function-based indexes are created with functions or expressions in their definitions. Function-based indexes allow index lookups on columns referenced by SQL functions in the WHERE clause of a query.