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.
