4

My understanding is that there is no fractional second for a Date data type. If that is true, then why do the three queries below not all have Pstart=6 and why do they not all have no filter predicate?

That is, for a Date data type is it true that the following two are logically equivalent?

:x > to_date('20210605 00:00:00','YYYYMMDD HH24:MI:SS')

:x >= to_date('20210605 00:00:01','YYYYMMDD HH24:MI:SS')

Oracle 12.1 Enterprise Edition.

Thanks in advance.

Setup: (though this is probably a bit too much for this question, but I was using this setup for other tests)

drop table p;

create table p
  (
    grp CHAR(3 byte)
    , dt DATE
    , payment_amount NUMBER
  )
  nocompress 
  tablespace data
  partition by LIST (grp)
  subpartition by range (dt)
  ( partition P_P_A values ('A')
    ( subpartition P_P_A_20210410 values less than (to_date('20210410 00:00:01','YYYYMMDD HH24:MI:SS'))
     ,subpartition P_P_A_20210424 values less than (to_date('20210424 00:00:01','YYYYMMDD HH24:MI:SS'))
     ,subpartition P_P_A_20210508 values less than (to_date('20210508 00:00:01','YYYYMMDD HH24:MI:SS'))
     ,subpartition P_P_A_20210522 values less than (to_date('20210522 00:00:01','YYYYMMDD HH24:MI:SS'))
     ,subpartition P_P_A_20210605 values less than (to_date('20210605 00:00:01','YYYYMMDD HH24:MI:SS'))
     ,subpartition P_P_A_20210619 values less than (to_date('20210619 00:00:01','YYYYMMDD HH24:MI:SS'))
     ,subpartition P_P_A_20210703 values less than (to_date('20210703 00:00:01','YYYYMMDD HH24:MI:SS'))
    )
   ,partition P_P_B values ('B')
    ( subpartition P_P_B_20210410 values less than (to_date('20210410 00:00:01','YYYYMMDD HH24:MI:SS'))
     ,subpartition P_P_B_20210424 values less than (to_date('20210424 00:00:01','YYYYMMDD HH24:MI:SS'))
     ,subpartition P_P_B_20210508 values less than (to_date('20210508 00:00:01','YYYYMMDD HH24:MI:SS'))
     ,subpartition P_P_B_20210522 values less than (to_date('20210522 00:00:01','YYYYMMDD HH24:MI:SS'))
     ,subpartition P_P_B_20210605 values less than (to_date('20210605 00:00:01','YYYYMMDD HH24:MI:SS'))
     ,subpartition P_P_B_20210619 values less than (to_date('20210619 00:00:01','YYYYMMDD HH24:MI:SS'))
     ,subpartition P_P_B_20210703 values less than (to_date('20210703 00:00:01','YYYYMMDD HH24:MI:SS'))
    )
   ,partition P_P_C values ('C')
    ( subpartition P_P_C_20210410 values less than (to_date('20210410 00:00:01','YYYYMMDD HH24:MI:SS'))
     ,subpartition P_P_C_20210424 values less than (to_date('20210424 00:00:01','YYYYMMDD HH24:MI:SS'))
     ,subpartition P_P_C_20210508 values less than (to_date('20210508 00:00:01','YYYYMMDD HH24:MI:SS'))
     ,subpartition P_P_C_20210522 values less than (to_date('20210522 00:00:01','YYYYMMDD HH24:MI:SS'))
     ,subpartition P_P_C_20210605 values less than (to_date('20210605 00:00:01','YYYYMMDD HH24:MI:SS'))
     ,subpartition P_P_C_20210619 values less than (to_date('20210619 00:00:01','YYYYMMDD HH24:MI:SS'))
     ,subpartition P_P_C_20210703 values less than (to_date('20210703 00:00:01','YYYYMMDD HH24:MI:SS'))
    )
  )
;

insert 
  into p
     ( grp
      ,dt
      ,payment_amount
     )
with "D" as (select /*+ materialize */ 1 from dual connect by level <= 1000)
select case mod(rownum, 3) when 1 then 'A' when 2 then 'B' when 0 then 'C' end
      , to_date('20210410','YYYYMMDD') + 14 * mod(rownum, 7)
      , dbms_random.value(500, 5000)
  from "D", "D"
;
commit;
exec dbms_stats.gather_table_stats(null, 'P');

Query 1 - Strict Inequality

select sum(P.payment_amount) 
  from p "P" 
 where P.grp = 'B'
       and P.dt > to_date('20210605 00:00:00','YYYYMMDD HH24:MI:SS') 
       and P.dt < to_date('20210703 00:00:01','YYYYMMDD HH24:MI:SS')
;  

----------------------------------------------------------------------------------------------------
| id  | Operation                  | name  | rows  | Bytes | cost (%CPU)| time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | select statement           |       |     1 |    34 |  2152   (1)| 00:00:01 |       |       |
|   1 |  sort AGGREGATE            |       |     1 |    34 |            |          |       |       |
|   2 |   partition LIST single    |       |   111K|  3689K|  2152   (1)| 00:00:01 |   key |   key |
|   3 |    partition range ITERATOR|       |   111K|  3689K|  2152   (1)| 00:00:01 |     5 |     7 |
|*  4 |     table access full      | p     |   111K|  3689K|  2152   (1)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------
  
Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("P"."DT">to_date(' 2021-06-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Query 2 - Non-Strict Inequality:

select sum(P.payment_amount) 
  from p "P" 
 where P.grp = 'B'
       and P.dt >= to_date('20210605 00:00:01','YYYYMMDD HH24:MI:SS') 
       and P.dt <= to_date('20210703 00:00:00','YYYYMMDD HH24:MI:SS')
;  

----------------------------------------------------------------------------------------------------
| id  | Operation                  | name  | rows  | Bytes | cost (%CPU)| time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | select statement           |       |     1 |    34 |  1436   (1)| 00:00:01 |       |       |
|   1 |  sort AGGREGATE            |       |     1 |    34 |            |          |       |       |
|   2 |   partition LIST single    |       |   158K|  5270K|  1436   (1)| 00:00:01 |   key |   key |
|   3 |    partition range ITERATOR|       |   158K|  5270K|  1436   (1)| 00:00:01 |     6 |     7 |
|*  4 |     table access full      | p     |   158K|  5270K|  1436   (1)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------
  
Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("P"."DT"<=to_date(' 2021-07-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Query 3 - No Predicate:

select sum(P.payment_amount) 
  from p "P" 
 where P.grp = 'B'
       and P.dt >= to_date('20210605 00:00:01','YYYYMMDD HH24:MI:SS') 
       and P.dt < to_date('20210703 00:00:01','YYYYMMDD HH24:MI:SS')
; 

----------------------------------------------------------------------------------------------------
| id  | Operation                  | name  | rows  | Bytes | cost (%CPU)| time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | select statement           |       |     1 |    34 |  1436   (1)| 00:00:01 |       |       |
|   1 |  sort AGGREGATE            |       |     1 |    34 |            |          |       |       |
|   2 |   partition LIST single    |       |   158K|  5270K|  1436   (1)| 00:00:01 |   key |   key |
|   3 |    partition range ITERATOR|       |   158K|  5270K|  1436   (1)| 00:00:01 |     6 |     7 |
|   4 |     table access full      | p     |   158K|  5270K|  1436   (1)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------

Edit: Addendum to clarify use case and motivation for the above experimental subpartition definition.

The application's current subpartition definitions have the following form.

   ,partition P_P_B values ('B')
    ( subpartition P_P_B_20210410 values less than (to_date('20210411','YYYYMMDD'))
     ,subpartition P_P_B_20210424 values less than (to_date('20210425','YYYYMMDD'))
     ,subpartition P_P_B_20210508 values less than (to_date('20210509','YYYYMMDD'))
     ,subpartition P_P_B_20210522 values less than (to_date('20210523','YYYYMMDD'))
     ,subpartition P_P_B_20210605 values less than (to_date('20210606','YYYYMMDD'))
     ,subpartition P_P_B_20210619 values less than (to_date('20210620','YYYYMMDD'))
     ,subpartition P_P_B_20210703 values less than (to_date('20210704','YYYYMMDD'))
     ,subpartition P_P_B_20210717 values less than (to_date('20210718','YYYYMMDD'))
    )

The application generates queries with predicates as follows.

P.dt between to_date('20210606 00:00:00','YYYYMMDD HH24:MI:SS') 
         and to_date('20210703 23:59:59','YYYYMMDD HH24:MI:SS')

This causes a filter predicate on the upper end of the range.

 4 - filter("P"."DT"<=to_date(' 2021-07-03 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))

Thus, the confusion as to the seemingly non-equivalence of the following two expressions prompted this post.

P.dt <= to_date('20210703 23:59:59','YYYYMMDD HH24:MI:SS')

and

P.dt < to_date('20210704 00:00:00','YYYYMMDD HH24:MI:SS')

This filter predicate can be avoided if the application is modified to generate the predicate as follows.

P.dt >= to_date('20210606 00:00:00','YYYYMMDD HH24:MI:SS') 
and P.dt < to_date('20210703 00:00:00','YYYYMMDD HH24:MI:SS') + 1

Having to do this seems "buggy" or "hackish". Was hoping to get clarification from this community.

Alex Bartsmon
  • 471
  • 4
  • 9
  • Looks like optimizer doesn't consider precision and equivalence from this point of view, because it does not do this for upper and lower boundary. Maybe trace of 10053 event will show you the way Oracle checks predicate against partition boundaries – astentx Jul 11 '21 at 22:31
  • 1
    Seems like `INDEX(grp, dt)` would do just fine without any partitioning, – Rick James Jul 12 '21 at 00:33
  • 1
    Oracle provides a correct result (espetially in the query 1 if you constraint with a time from 5th partition, this partition will be scanned as well). To the usage of Oracle belongs also the knowledge of the *optimalization* that are supported, this *one second* logic is apparetnly *not supported* and IMO this is not a big problem for practical use cases. Partitions are typically defined on a *day boundary* and not one seconds later. – Marmite Bomber Jul 12 '21 at 14:14
  • I'm a little confused by the choice of 00:00:01 in the partition definitions. This would mean that (for example) all data from the whole day of 20210702 plus just the 1 second of 20210703 (midnight) go into the same partition? Seems odd unless the only dates allowed are "whole" (ie, no seconds at all) at which you may as well just do 23:59:59 for the upper value no? – Connor McDonald Jul 13 '21 at 01:48
  • @Connor McDonald: Yes, the grain of ``dt`` is a "whole" date only; that is, the time component is zero (00:00:00). The choice of ``00:00:01`` is motivated by making the greatest possible value in the subpartition definition a "whole" date so as to avoid a filter predicate if ``dt`` happens to be predicated on that greatest "whole" date value. If we chose 23:59:59, neither the least nor the greatest possible value in the subpartition definition would be a "whole" date. – Alex Bartsmon Jul 18 '21 at 00:10

0 Answers0