Function-based indexes and trunc()

Hi,

I've just been testing queries and indexes using the trunc() function on
dates and noticed something I didn't expect.

I created a simple table on 11.2.0.2 EE, filled it with data and created a
function-based index on a date column applying trunc to it:

create table plch_invoices (
  invoice_id      integer not null primary key,
  raised_datetime date not null,
  total_cost      number(10, 2) not null
);

insert into plch_invoices
  select rownum,
         sysdate-1825+(rownum/100),
         round(dbms_random.value(10, 100), 2)
  from   dual
  connect by level <= 182500;

commit;

exec dbms_stats.gather_table_stats(user, 'plch_invoices');

create index plch_invo_raised_date_i
on plch_invoices (trunc(raised_datetime));

I then ran the following query which doesn't include the trunc() function
in the predicates:

select *
from   plch_invoices
where  raised_datetime >= trunc(sysdate)-1
and    raised_datetime < trunc(sysdate);

From my understanding of function-based indexes, this query shouldn't use
the index created above because the predicates don't match what's in the
index.

When looking at the autotrace output however, I see this:

Execution Plan
----------------------------------------------------------
Plan hash value: 1427368697

--------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                    | Rows  |
Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                         |   101 |
 1717 |     9   (0)| 00:00:01 |
|*  1 |  FILTER                      |                         |       |
    |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| PLCH_INVOICES           |   101 |
 1717 |     9   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | PLCH_INVO_RAISED_DATE_I |   821 |
    |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TRUNC(SYSDATE@!)-1<TRUNC(SYSDATE@!))
   2 - filter("RAISED_DATETIME">=TRUNC(SYSDATE@!)-1 AND
"RAISED_DATETIME"<TRUNC(SYSDATE@!))
   3 -
access(TRUNC(INTERNAL_FUNCTION("RAISED_DATETIME"))>=TRUNC(TRUNC(SYSDATE@!)-1)
AND

TRUNC(INTERNAL_FUNCTION("RAISED_DATETIME"))<=TRUNC(TRUNC(SYSDATE@!)))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         19  consistent gets
          0  physical reads
          0  redo size
       2853  bytes sent via SQL*Net to client
        442  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed

Oracle's applied the trunc function to the query for me and used the index!

Has this always worked like this? If so, have I misunderstood something
about function-based indexes? If not, when did this change?

I'm curious to understand this, so if you know answers to the above then
please share!

Thanks,
Chris

Other related posts: