May 052014
 

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!

  3 Responses to “Oracle can now use function-based indexes in queries without functions!”

  1. Chris,
    btw, I saw this your great finding in Oracle-L, but I’ve understood just today that it’s also good workaround for indexing of columns with extended datatypes like varchar2(32767) without code changing.

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>