There’s a neat optimization in Oracle I found while tinkering around (look closely at the predicate section):

select oracle_can_now_use, a_function_based_index from queries_with_no_functions where a_date_col_with_an_fbi = :a_date and oracle_version >= '11.2.0.2' ; ------------------------------------------------------------------------- | Id | Operation | Name | Rows | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | |* 1 | TABLE ACCESS BY INDEX ROWID| QUERIES_WITH_NO_FUNCTIONS | 1 | |* 2 | INDEX RANGE SCAN | QUWF_DATE_FBI | 1 | ------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ORACLE_VERSION">='11.2.0.2' AND "A_DATE_COL_WITH_AN_FBI"=:A_DATE) 2 - access(TRUNC(INTERNAL_FUNCTION("A_DATE_COL_WITH_AN_FBI"))=TRUNC(:A_DATE))

Line 2 of the predictates states that the INDEX RANGE SCAN was accessed via TRUNC(INTERNAL_FUNCTION()) – a function-based index was used when no function is present in the where clause!

This optimization appeared in 11.2.0.2 (fix 9263333) – I have to thank the people of Oracle-L (particularly Velikikh Mikhail) identifying when this improvement came in.

This optimization isn’t restricted to TRUNC() on date columns however.



It also works with FBIs applying TRUNC() to a number (trunc appears in the predicate section again, though this time the bind variable has to_number applied to it):

alter table queries_with_no_functions modify (a_date_col_with_an_fbi number); select * from queries_with_no_functions where a_date_col_with_an_fbi = :a_number and oracle_version >= '11.2.0.2' ; ------------------------------------------------------------------------- | Id | Operation | Name | Rows | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | |* 1 | TABLE ACCESS BY INDEX ROWID| QUERIES_WITH_NO_FUNCTIONS | 1 | |* 2 | INDEX RANGE SCAN | QUWF_NUMBER_FBI | 1 | ------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ORACLE_VERSION">='11.2.0.2' AND "A_DATE_COL_WITH_AN_FBI"=TO_NUMBER(:A_NUMBER)) 2 - access(TRUNC("A_DATE_COL_WITH_AN_FBI")=TRUNC(TO_NUMBER(:A_NUMBER)))

Or SUBSTR() to a varchar2 (again, see how substr() is listed in the predicate section):

create index quwf_version_i on queries_with_no_functions ( substr(oracle_version, 1 , 3 )); select * from queries_with_no_functions where oracle_version >= '11.2.0.2' ; ------------------------------------------------------------------------- | Id | Operation | Name | Rows | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | |* 1 | TABLE ACCESS BY INDEX ROWID| QUERIES_WITH_NO_FUNCTIONS | 1 | |* 2 | INDEX RANGE SCAN | QUWF_VERSION_FBI | 1 | ------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ORACLE_VERSION">='11.2.0.2') 2 - access(SUBSTR("ORACLE_VERSION",1,3)>='11.')

If you’d like a complete script showing this in action use this – remember you’ll need to be on at least 11.2.0.2 to see this effect!