Re: Function-based indexes and trunc()
- From: Mohamed Houri <mohamed.houri@xxxxxxxxx>
- To: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Sat, 19 Apr 2014 16:57:16 +0200
Chris,
I don’t know clearly how Oracle is optimizing the *trunc* function. I have
shown in a blog article that if you create an index on a virtual column
virt_col* (**trunc (col1))*, and you use a where clause on column *col1*,
the index on the virtual column is selected by the CBO.
http://hourim.wordpress.com/2013/10/25/index-on-a-virtual-column-would-it-help-others/
However, in the same article I have tried several virtual columns using
different function (abs, round, ceil, etc…) which reveal not having the
same behavior as the trunc function.
In your case, the similarity to my blog article looks close. Consider the
following indexes (12c)
SQL> create index mho_t_cost_ind on plch_invoices(abs(total_cost));
SQL> select *
2 from plch_invoices
3 where total_cost >= abs(100);
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 171
(100)| |
|* 1 | TABLE ACCESS FULL| PLCH_INVOICES | 20 | 340 | 171 (1)|
00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TOTAL_COST">=100)
SQL> create index mho_t_cost_ind on plch_invoices(round(total_cost));
select * from plch_invoices where total_cost >= round(100)
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 171
(100)| |
|* 1 | TABLE ACCESS FULL| PLCH_INVOICES | 20 | 340 | 171 (1)|
00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TOTAL_COST">=100)
And so on until you will use the trunc function
SQL> create index mho_t_cost_ind on plch_invoices(trunc(total_cost));
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| PLCH_INVOICES | 20 |
340 |
|* 2 | INDEX RANGE SCAN | MHO_T_COST_IND | 1642
| |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TOTAL_COST">=100)
2 - access("PLCH_INVOICES"."SYS_NC00005$">=TRUNC(100))
I have tried to disable the hidden parameter that seems optimizing the
trunc function but this didn’t changed anything:
SQL> alter session set "_truncate_optimization_enabled" = false;
Session altered.
select * from plch_invoices where total_cost >= trunc(100)
Plan hash value: 1694338939
------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| PLCH_INVOICES | 8 |
136 |
|* 2 | INDEX RANGE SCAN | MHO_T_COST_IND | 8
| |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TOTAL_COST">=100)
2 - access("PLCH_INVOICES"."SYS_NC00005$">=TRUNC(100))
Note
-----
- statistics feedback used for this statement
May be Jonathan Lewis (or others) has a clue to show us why the trunc
function is considered differently by the CBO?
By the way, in your particular case the CBO starts using the index from
11.2.0.3 as shown below:
SQL> alter session set optimizer_features_enable='10.2.0.4';
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 174
(100)|
|* 1 | FILTER | | | |
|
|* 2 | TABLE ACCESS FULL| PLCH_INVOICES | 101 | 1717 | 174 (3)|
00:00:03 |
-----------------------------------------------------------------------------------
SQL> alter session set optimizer_features_enable='11.2.0.1';
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 174
(100)|
|* 1 | FILTER | | | |
|
|* 2 | TABLE ACCESS FULL| PLCH_INVOICES | 101 | 1717 | 174 (3)|
00:00:03
-----------------------------------------------------------------------------------
SQL> alter session set optimizer_features_enable='11.2.0.3';
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| |
|* 1 | FILTER | |
| |
|* 2 | TABLE ACCESS BY INDEX ROWID| PLCH_INVOICES | 101 |
1717 |
|* 3 | INDEX RANGE SCAN | PLCH_INVO_RAISED_DATE_I | 821
| |
---------------------------------------------------------------------------------
Best regards
Mohamed Houri
www.hourim.wordpress.com
2014-04-19 16:25 GMT+02:00 Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>:
>
>
> 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
>
>
>
--
Bien Respectueusement
Mohamed Houri
Other related posts:
