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