Forum Stats

  • 3,770,203 Users
  • 2,253,081 Discussions
  • 7,875,363 Comments

Discussions

Partition pruning and TRUNC function

Hesh
Hesh Member Posts: 628 Bronze Badge
edited Nov 23, 2019 1:44PM in SQL & PL/SQL

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

Tagged:

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Nov 23, 2019 12:21PM

    It is Worst Practice to compare DATE to string & depend upon implicit datatype conversion

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

    IMO, results above should be same as results below

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

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,790 Gold Crown
    edited Nov 23, 2019 1:44PM

    Is this "list // range" composite ?

    It look a little strange to have a table partitioned by type and subpartitioned by date, I would usually expect to see range partitioned by date, list subpartitioned by type.  What do your partitioning clauses look like.

    You might be able to get partition elimination to work by adding a (silly looking and redundant) check constraint on the date of the form:

      check (from_dt >= trunc(from_dt))

    UPDATE:::  I've suggested the wrong constraint - the principle is nominally correct, but I'll have to think about what might actually work/

    UPDATE 2:  I was right the first time - but I'd forgotten you have to declare FROM_DT at not null for the predicate generation to work.

    You may find that the optimizer than uses transitive closure to generate a new predicate:

        trunc(from_dt) > to_date('01-Nov-2019','dd-mon-yyyy')

    combined with

        from_dt >= trunc(from_dt)

    =>

        from_dt > to_date('01-Nov-2019','dd-mon-yyyy')

    If it does work it may mess up the optimizer's cardinality estimates (more than usual for partitioned tables).

    If you want to consider a different solution - and if you decide to rethink the partitioning strategy - you might want to look at creating a virtual column ono trunc(from_dt) and partitioning by that to see what you can achieve. See: https://jonathanlewis.wordpress.com/2013/10/17/virtual-date-partitions/


    Regards

    Jonathan Lewis

    Heshberx