Oracle Function Based Index :

In my previous article I have given different ways to create index with multiple examples.In this article i would like to give you one special type of index with its advantages as well as disadvantages which is Oracle Function Based Index . Oracle Function Based Index is one of the best index which used for different Oracle functions created by developer.Rather than applying the index on specified column the oracle 8i provides the way to create index directly on the function. There are so many times the developers are using functions for column in where clause. The simple example of it is to check the case sensitivity user needs to Upper function to check it.The Oracle Function Based index is useful in the situation than simple index on specified column.

Oracle Function Based Index Syntax  and  Examples :

In this section I would like to give you the syntax of Oracle function based index with some important examples.I would like to give some most important examples for functional based indexes in industry.User can create the index directly to the function.Before user can create the functional based index there are following prerequisites needs to consider,

Prerequisite 1 : User must have Create Any Index Privillege

Prerequisite 2 :Table to be index will be in Same schema.

Prerequisite 3 :User must have Index Object privilege on the table where you need to create index.

Syntax :

Create Index Index_name

On Table_Name(Function(column_name));

The above syntax is used to create the index with specific function in oracle which will be user-defined function or any built in oracle function.These functional based indexes will be useful for improving the performance of the queries where user is using different functions.

I would like to give you some most important examples of Oracle Function Based Index :

Example 1 : Handling Case-sensitiveness in Column 

There are so many times where user needs to handle the case-sensitiveness in specific column.Let us say that If user do not know case of the  Employee_name column in Employee table.Then user can use the following statement to handle this.

User needs to create functional based index on Employee Name column.

Create Index FB_Employee_Name

On Employee(Upper(Employee_name));

The above statement will create functional based index named FB_Employee_Name on Employee_Name column.

When and How this index is used by Oracle ?

The main question in mind that when this index will be useful.There are situations where user is using not null condition in the SQL statement. When Not null condition is used with upper column then it will definitely take the Oracle Function Based Index which is created on Upper column.

Select Employee_Name,Salary

From Employee where Upper(Employee_name) is not null;

The above statement will take the index named ‘FB_Employee_Name’.The key condition to take the index by oracle engine here is the function with column should be used in where clause of select statement.

Select Employee_Name,Salary

From Employee where Upper(Employee_name) = ‘AMIT’;

The above statement will fetch the value fast if Upper(Employee_name) column is indexed.

Example 2 : Handling different Arithmetic statements

There are so many situations where user is using the arithmetic statements in where clause.Let us say that Employee has got the bonus with some percentage in the salary.The new salary of Employee will be

Salary=Salary * Bonus;

Then user needs to create new index on Salary*Bonus column to make the operation fast.

Create Index FB_Salary_Bonus

On Employee((Salary*Bonus));

With using above statement it will create index named FB_Salary_Bonus.

Select * from Employee where (Salary*Bonus) >25000;

If user is using this statement it will use the index named FB_Salary_Bonus and will help oracle engine to improve the performance.

Example 3 : Handling different date statements

There are so many situations where user is handling date using different date functions.User can create functional based indexes on the specified columns.

Let us say the Hire_date is using trunc function to truncate it and fetch the records from the table. The following index is useful to improve the performance of query.

Create index FI_Employee on Employee(trunc(Hire_date);

Instead of creating index on the Hire_date if user can create index on trunc function of Hire_date then it will be useful than simple index.

Example 4 : Handling LOB or Clob datatypes

User can create index on LOB or Clob columns datatypes to improve the performance of the query.Let us say that in Employee table we are using photo_media as Lob datatype.User needs to create the functional based index,

Create index lob_Index on Employee on (text_length(photo_media));

The above statement will create the index on column named photo_media which is of Lob datatype. Here user needs to gather the stats so that user can use that index,

Analyze index lob_Index  COMPUTE STATASTICS;

This statement will gather the stats of the index.

Select Employee_No,Salary from Employee where text_length(photo_media) < 1500;

The above statement will use the index named lob_index to fast retrieval of the data from the table.

Example 5 : Create index on User defined function

There are some situations where user needs to create the index on user defined function.Lets take example of Email validation function in SQL>.

Function Code :

Create or replace

FUNCTION xx_check_email(l_user_name IN VARCHAR2)

RETURN VARCHAR2 IS

l_dot_pos    NUMBER;

l_at_pos     NUMBER;

l_str_length NUMBER;

BEGIN

l_dot_pos    := instr(l_user_name

,’.’);

l_at_pos     := instr(l_user_name

,’@’);

l_str_length := length(l_user_name);

IF ((l_dot_pos = 0) OR (l_at_pos = 0) OR (l_dot_pos = l_at_pos + 1) OR

(l_at_pos = 1) OR (l_at_pos = l_str_length) OR

(l_dot_pos = l_str_length))

THEN

RETURN ‘FAILURE’;

END IF;

IF instr(substr(l_user_name

,l_at_pos)

,’.’) = 0

THEN

RETURN ‘FAILURE’;

END IF;

RETURN ‘SUCCESS’;

end xx_check_email;

The function named xx_check_email has been created. If user is using this function in where statement to check the mail of the user then first user needs to create index on that function.

Create index FB_Email

On Employee(xx_check_email(Employee_mail));

This will create Oracle function based index on employee table.This will be used with using following select statement,

Select Employee_name,Salary from Employee where xx_check_email(Employee_mail)=Employee_mail_new;

The above statement will use the index named FB_Email to improve the performance of the query.

These are some most important oracle function based index which will be useful for user.

Advantages of Oracle Function Based Index :

In this section i would like to explain some Advantages of Function Based Index and explain why to use functional based index,

1. It is easy to use and implement.
2. These Indexes provides the immediate value.
3. These indexes are used to speed up the existing applications without changing any logic.
4. These indexes are used to improve the performance of complex sql queries
5. These indexes are useful to improve the performance of reporting queries
6. These indexes are also used to improve the performance of built in packages in PL SQL

Disadvantages of Functional Based Index :

Every coin has two sides,There are some following disadvantages of Functional based index,

1.Sometimes the functional based indexes degrades the performance of query. User needs to check Explain plan of the query to check it.

2.It will not be used where there are huge insert and update statements.For select statement these indexes are more used.

I am hoping that you will get the exact idea about oracle function based index in detail. If you like this article on oracle function based index or if you have any suggestions or concerns with the same kindly comment in to comments section.