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. Regards Jonathan Lewis http://jonathanlewis.wordpress.com @jloracle ________________________________ From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf of Stéphane Faroult [sfaroult@xxxxxxxxxxxx] Sent: 19 April 2014 13:48 To: oracle-l@xxxxxxxxxxxxx Cc: chris.saxon@xxxxxxxxx >> Chris Saxon Subject: Re: Function-based indexes and trunc() Chris, 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<http://www.roughsea.com> Konagora<http://www.konagora.com> RoughSea Channel on Youtube<http://www.youtube.com/user/roughsealtd> Author, SQL Success<http://www.amazon.com/SQL-Success-Database-Programming-Proficiency/dp/1909765007/>, The Art of SQL<http://www.amazon.com/Art-SQL-Stephane-Faroult/dp/0596008945/>, Refactoring SQL Applications<http://www.amazon.com/Refactoring-SQL-Applications-Stephane-Faroult/dp/0596514972/> 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 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