RE: Function-based indexes and trunc()

  • From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Sat, 19 Apr 2014 14:25:48 +0000


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

Other related posts: