Query Optimization - Function Based Index

Sometime the requirement is to search a table after transforming its values, 
as we may need to search employee names after converting them to lower case.
so that all employee names can be searched by ignoring their case.
But this requirement may lead Oracle to avoid the use of index.

For example:

SQL> CREATE INDEX MYEMP_ENAME_INDX ON MYEMP(ENAME);

Index created.

SQL> SET AUTOT TRACE
SQL>
SQL> SELECT * FROM MYEMP WHERE LOWER(ENAME) = 'ward';

Execution Plan
----------------------------------------------------------
Plan hash value: 2418123361

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    38 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| MYEMP |     1 |    38 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

As we can see that MYEMP table is getting TABLE ACCESS FULL, and not using 
MYEMP_ENAME_INDX. For making Oracle to use the index we need to recreate 
the function based index.

For example:

SQL> DROP INDEX MYEMP_ENAME_INDX;

Index dropped.

SQL> CREATE INDEX MYEMP_ENAME_INDX ON MYEMP(LOWER(ENAME));

Index created.

SQL> SELECT * FROM MYEMP WHERE LOWER(ENAME) = 'ward';

Execution Plan
----------------------------------------------------------
Plan hash value: 2632457189

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    38 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MYEMP            |     1 |    38 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MYEMP_ENAME_INDX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------


Get More Query Optimization Tips:
- Avoiding unnecessary function calls to optimize SQL statements
- Query Optimization Tips For Oracle

4 comments:

  1. Hi Nimish, that's a very good point to note!
    I have a small query regarding this. How can we create Index when we are not sure what kind of functions are applied on the entity while querying. For example, in the case you have given, we can give UPPER function rather than LOWER. So, do we need to create another index for avoiding full table scan?

    ReplyDelete
    Replies
    1. Ananth, we should create index as per the requirements and on the columns on which queries are getting executed frequently. If we are not sure, then create index on the column only with out function and try to give index hint and check the plan...

      Delete
  2. Very Good Explained step by step

    ReplyDelete
  3. Hi Nimish, I have a question if you can help

    we are using MAX(date_time_column) to identify the row with latest update . The table has 300 Million rows and the MAX function is called 2 times in a single query. We know it is doing full table Scan both the times

    Is Function based Index is the solution to improve Query Performance ?
    if yes, can you provide the syntax for the same for Max(date_time_column)

    regards,
    Vineet

    ReplyDelete