RE: Function-based indexes and trunc()

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 19 Apr 2014 12:33:38 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DE9E63_at_exmbx05.thus.corp>

Based on the comments on this blog posting it's possible that the enhancement appeared in 11.2.0.2.

Regards
Jonathan Lewis

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Chris Saxon [chris.saxon_at_gmail.com] Sent: 19 April 2014 11:14
To: oracle-l_at_freelists.org
Subject: Function-based indexes and trunc()

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 - 14:33:38 CEST