RE: Function-based indexes and trunc()

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 19 Apr 2014 18:32:02 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DE9EAB_at_exmbx05.thus.corp>

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
_at_jloracle

---

From: Mohamed Houri [mohamed.houri_at_gmail.com] Sent: 19 April 2014 15:57
To: Jonathan Lewis
Cc: oracle-l_at_freelists.org; chris.saxon_at_gmail.com >> 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>

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Apr 19 2014 - 20:32:02 CEST