Forum Stats

  • 3,784,391 Users
  • 2,254,932 Discussions


Partition pruning and TRUNC function

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


Oracle Database 12c Enterprise Edition Release - 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


With Trunc: Scanning 1 to 74 Partitions






  • 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,857 Blue Diamond
    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:


    Jonathan Lewis