Function-based indexes and trunc()

From: Chris Saxon <chris.saxon_at_gmail.com>
Date: Sat, 19 Apr 2014 11:14:56 +0100
Message-ID: <CAJ7OfbNwqUW5_DkpepLocXvtnHmY=7+gi_fE+0MgYRm7psO8jg_at_mail.gmail.com>

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_at_!)-1<TRUNC(SYSDATE_at_!))    2 - filter("RAISED_DATETIME">=TRUNC(SYSDATE_at_!)-1 AND "RAISED_DATETIME"<TRUNC(SYSDATE_at_!))

   3 -
access(TRUNC(INTERNAL_FUNCTION("RAISED_DATETIME"))>=TRUNC(TRUNC(SYSDATE_at_!)-1) AND TRUNC(INTERNAL_FUNCTION("RAISED_DATETIME"))<=TRUNC(TRUNC(SYSDATE_at_!)))

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

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Apr 19 2014 - 12:14:56 CEST