RE: Function-based indexes and trunc()
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: Mohamed Houri <mohamed.houri@xxxxxxxxx>
- Date: Sat, 19 Apr 2014 18:32:02 +0000
Mohamed
I thought it might be the _truncate_optimization_enabled parameter as well, but
then decided that was about improving the performance of "truncate table" ...
possibly by updating tsq$ just once at the end rather than once for every
extent released (but I didn't check that).
Trunc is a little special because there's a simple way to take advantage of an
approximation, as I pointed out in my previous post. Possibly in future
releases a few more arithmetic functions will be "special cased".
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: Mohamed Houri [mohamed.houri@xxxxxxxxx]
Sent: 19 April 2014 15:57
To: Jonathan Lewis
Cc: oracle-l@xxxxxxxxxxxxx; chris.saxon@xxxxxxxxx >> Chris Saxon
Subject: Re: Function-based indexes and trunc()
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;
...
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<http://www.hourim.wordpress.com>
Other related posts:
