Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Partition pruning and TRUNC function

HeshNov 23 2019 — edited Nov 23 2019

Hi,

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

This should be a dumb question!

My application has a very big Partitioned(sub-partitioned as well) table say T_RCE . This table is partitioned on the column PRICE_TYPE_KEY and further sub-partitioned on FROM_DT.

While querying this table if any query is using TRUNC function looks like Partition Pruning is not happening its working fine when there is no TRUNC.

Is there anyway we can make the optimizer to do the desired partition prune with and without TRUNC? I know I can request for a code change but just wonder if there is anyway we can do this!

select * from T_RCE where price_type_key=3 and trunc(from_dt) >'01-Nov-2019'

Without Trunc : Scanning 71 to 74 partitions

pastedImage_0.png

With Trunc: Scanning 1 to 74 Partitions

pastedImage_2.png

Regards

Hesh
This post has been answered by Jonathan Lewis on Nov 23 2019
Jump to Answer
Comments
Post Details
Added on Nov 23 2019
#performance
3 comments
1,626 views