RE: Function-based indexes and trunc()
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "chris.saxon@xxxxxxxxx" <chris.saxon@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 19 Apr 2014 12:33:38 +0000
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
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Chris Saxon [chris.saxon@xxxxxxxxx]
Sent: 19 April 2014 11:14
To: oracle-l@xxxxxxxxxxxxx
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@!)-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:
