Oracle function-based index:

We will give demonstration with table name department created in the CREATE INDEX. 

Statement to creates an index on the first_name column of the department table:

CREATE INDEX department_i ON members(first_name);

if in WHERE clause first_name column is being used ,optimizer will definitely use the index:

select * from  department  WHERE first_name = 'Man';

if function is being used on the indexed column first_name as below then optimizer will not use index.

select * from  department  WHERE UPPER(first_name) = 'MAN';

To overcome above concern, Oracle introduced function-based indexes:

A function-based index calculates the result of a function that involves one or more columns and stores that result in the index.

syntax of creating a function-based index:

CREATE INDEX index_name ON table_name (expression)

index expression can be an arithmetic expression or an expression that contains a function such as a SQL function, PL/SQL function, and package function.

*** function-based index can be a btree or bitmap index.
example:

CREATE INDEX department_i ON members(UPPER(first_name));

Here Oracle will convert all values of first_name column to uppercase and stored  in index department_i .

Now below quey will use index to fetch the data.

select * from  department  WHERE UPPER(first_name) = 'MAN';