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
Query Optimization - Function Based Index
Subscribe to:
Post Comments (Atom)
Hi Nimish, that's a very good point to note!
ReplyDeleteI 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?
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...
DeleteVery Good Explained step by step
ReplyDeleteHi Nimish, I have a question if you can help
ReplyDeletewe 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