Home » Oracle » Creating Function Based Index In Oracle

Creating Function Based Index In Oracle

Function-based index is good idea for making queries run faster without changing the application logic
and code. Function-based indexes are highly useful in case-sensitive searches and sorts and in searches
on columns that have computations performed on them.

The following are some examples that shows how to create a function-based index.

create index indxemp_name on emp (UPPER(ENAME));

Once you create the function-based index as shown here, you can use the index expression in a
query such as the following:

select *
from emp
where UPPER(ENAME) LIKE 'K%N';

Another example of a function-based index shows a test table and an index that is created on
an arithmetic expression:

create table testtbl(
a integer,
b integer,
c integer);

create index indxtesttbl on testtbl(a+b*(c-1),a,b);

If you issue a SQL statement as shown below, the query will use an index range scan instead of
a full table scan.

select a from testtbl where a+b*(c-1) <200;

Limitations of Function-Based Indexes
Function-based indexes come with a few limitations or restrictions.

• The data type of the index expression can't be VARCHAR2, RAW, LONGRAW, or a PL/SQL
data type of indeterminate length.
• The database ignores function-based indexes while doing an OR expansion.
• The index expression must not invoke an aggregate function such as SUM.