FUNCTION BASED INDEX

Function based index is also a part of indexes which use result of the function as key instead of using column as the value for the key.

They enable you to construct queries that evaluate the value returned by an expression, which in turn may include built-in or user-defined functions.

Syntax:-

Create index  on  (upper (column name)));

Example:-

Create index na_fbi_job on EMP (upper (job));

We can retrieve the data based on JOB

SQL>select *from EMP where upper (job) =’MANAGER’;

We can not create index by using more than one function.

For dropping index

Syntax:-

Drop index ;

Example:-

SQL>Drop index idx4;

-- Index dropped