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.