RE: Function-based indexes and trunc()

From: Jonathan Lewis <>
Date: Sat, 19 Apr 2014 14:25:48 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DE9E7B_at_exmbx05.thus.corp>

It's a numeric thing, not a character thing - I just changed the code to make the date column a number column. And it's a "simple" arithmetic model (though I haven't checked the treatment of stats yet - or looked at the 10053) which says roughly

If you want X >= A
then trunc(X) >= trunc(A) is a reasonable first approximation, and you can check anything that survives that test; similarly if you want X < B
then trunc(X) <= trunc(B) is a reasonable first approximation -- note the change from < to <= in that case.

Jonathan Lewis

From: [] on behalf of Stéphane Faroult [] Sent: 19 April 2014 13:48
Cc: >> Chris Saxon Subject: Re: Function-based indexes and trunc()


   trunc() ignores bits on the right-hand side of the key (don't take "right-hand side" literally, it's just to help picture it), therefore it doesn't hurt a tree-search for which what matters is the left-hand side. It's certainly a welcome improvement. The left() function in SQL Server use indexes even when the expression wasn't indexed, exactly for the same reason. Working on something else now but it *might* just be, at least it's a possible optimization, that Oracle's equivalent of left(), substr(<col>, 1, ...) would use the index (not sure that in SQL Server substring(<col>, 1, ...) does it), as does LIKE 'blahblah%'.

 On the other hand, I am not sure that this kind of improvement will help make understand index usage to young developers :-).


Stéphane Faroult
RoughSea Ltd<>
RoughSea Channel on Youtube<> Author, SQL Success<>, The Art of SQL<>, Refactoring SQL Applications<>

On 04/19/2014 12:14 PM, Chris Saxon wrote: 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 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,

         round(dbms_random.value(10, 100), 2)
  from dual
  connect by level <= 182500;


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_!))


          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!


-- Received on Sat Apr 19 2014 - 16:25:48 CEST

Original text of this message