Other Views

Solving performance issues using Oracle Function-Based Indexes

Introduction

Function-Based Index is one of the many new features introduced in Oracle 8i. This functionality allows the database administrator to create indexes on functions or on even expressions. Good thing is that these functions can be user generated function or, standard SQL functions. In other words by implementing your own functions and operators this index allows you to have case insensitive sorts or searches, complex equations, and extend the SQL language efficiently.

In previous version of Oracle if you use a function on an indexed column in the where clause of a SQL query then index would not be used. Function Based Indexes has been introduced to handle this problem. Let me try to explain this feature of oracle 8i with an practical example. Problem Scenario

Suppose you have a table Student_Data and you have bunch of data in this table.

CREATE TABLE Student_Data (
 Std_Id          	NUMBER(10)    NOT NULL,
 Std_First_Name  	VARCHAR2(250)  NOT NULL
)

Sample Data

Insert into Student_Data values (1,’Rahil’);
Insert into Student_Data values (2,’Nadeem’);
Insert into Student_Data values (3,’Sajid’);
Insert into Student_Data values (4,’Majid’);
Insert into Student_Data values (5,’Aneeq’);
Insert into Student_Data values (6,’Shakoor’);

Initially when this table is not indexed so you would expect a full table scan for any query, we can witness this through monitoring execution plan of query.

SET AUTOTRACE ON
SELECT Std_Id ,  Std_First_Name  ,   Std_Last_Name
FROM   Student_Data
WHERE  Upper(Std_First_Name ) = 'RAHIL';
Execution Plan
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=10)
   1    0   TABLE ACCESS (FULL) OF 'STUDENT_DATE' (Cost=1 Card=1 Bytes=10)

Now if you create a regular index on the Std_First_Name we see that the applied index is still not used.

CREATE INDEX std_first_name_idx ON Student_data (Std_First_Name);
ANALYZE TABLE Student_data COMPUTE STATISTICS;
SELECT Std_Id ,  Std_First_Name  ,   Std_Last_Name
FROM   Student_Data
WHERE  Upper(Std_First_Name ) = 'RAHIL';

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE 
                    (Cost=1 Card=1 Bytes=10)
   1    0   TABLE ACCESS (FULL) OF 'STUDENT_DATE' 
                    (Cost=1 Card=1 Bytes=10)

Solution Now user function-based index on the Std_First_Name column instead of normal index, you will see that the index is now used for retrieval of data.

DROP INDEX std_first_name_idx;
CREATE INDEX std_first_name_idx ON Student_data (UPPER(std_first_name));
ANALYZE TABLE Student_data COMPUTE STATISTICS;

These two alter session queries ensures that the new index is used

ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED; 
ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;
SET AUTOTRACE ON
SELECT Std_Id, Std_First_Name, Std_Last_Name
FROM   Student_Data
WHERE  Upper(Std_First_Name ) = 'RAHIL';

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE 
                   (Cost=2 Card=1 Bytes=14)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'STUDENT_DATE' 
                   (Cost=2 Card=1 Bytes=14)
   2    1     INDEX (RANGE SCAN) OF 'STD_FIRST_NAME_IDX' (NON-UNIQUE) 
                   (Cost=1 Card=1)

Conclusion Traditionally, using a function on indexed column in WHERE clause of a SQL query guaranteed an index would not be used. With the Function Based Indexes Oracle introduced a solution to counter this problem. Rather than indexing a column, now you can index the function on a particular column.