Wonder if there is any reason to write query like "~~ WHERE order_date >= :ord

I'm a newbie student for oracle tuning.

I'm now solving some query optimization problems from a book and when I checked one of the author's model answer for a query and the where condition was something like

select customer_id, order_date  
from table1
where customer_id = nvl(:cust_no, customer_id)
and   order_date >= to_date(:ord_dt1, 'yyyymmdd')
and   order_date <  to_date(:ord_dt2, 'yyyymmdd')   1
order by order_date

What I'm curious about is the reason he wrote date comparison differently. I thought there might be a reason he wrote fifth line as order_date < to_date(:ord_dt2, 'yyyymmdd') 1 instead of order_date <= to_date(:ord_dt2, 'yyyymmdd')

Is it some kinds of SQL formatting standards? Is there any good? If so, Why < date 1 for end range is more desirable than <= date ?

I would be grateful any idea for this. Thanks for reading my question.

CodePudding user response：

Let's try it with two sample date columns, one truncated to 'dd' and the other with hours in the date.

WITH
    tbl As
        (
            Select 
                25 - LEVEL "ID", 
                TRUNC(SYSDATE   1 - LEVEL,  'dd') "DATE_DD", 
                TRUNC(SYSDATE    1 - LEVEL,  'dd')   LEVEL/24 "DATE_HH" 
            From Dual
                CONNECT BY LEVEL <= 25
        )

Table looks like this:

        ID DATE_DD             DATE_HH           
---------- ------------------- -------------------
        24 22.08.2022 00:00:00 22.08.2022 01:00:00 
        23 21.08.2022 00:00:00 21.08.2022 02:00:00 
        22 20.08.2022 00:00:00 20.08.2022 03:00:00 
        21 19.08.2022 00:00:00 19.08.2022 04:00:00 
        20 18.08.2022 00:00:00 18.08.2022 05:00:00 
        19 17.08.2022 00:00:00 17.08.2022 06:00:00 
        18 16.08.2022 00:00:00 16.08.2022 07:00:00 
        17 15.08.2022 00:00:00 15.08.2022 08:00:00 
        16 14.08.2022 00:00:00 14.08.2022 09:00:00 
        15 13.08.2022 00:00:00 13.08.2022 10:00:00 
        14 12.08.2022 00:00:00 12.08.2022 11:00:00 
        13 11.08.2022 00:00:00 11.08.2022 12:00:00 
        12 10.08.2022 00:00:00 10.08.2022 13:00:00 
        11 09.08.2022 00:00:00 09.08.2022 14:00:00 
        10 08.08.2022 00:00:00 08.08.2022 15:00:00 
         9 07.08.2022 00:00:00 07.08.2022 16:00:00 
         8 06.08.2022 00:00:00 06.08.2022 17:00:00 
         7 05.08.2022 00:00:00 05.08.2022 18:00:00 
         6 04.08.2022 00:00:00 04.08.2022 19:00:00 
         5 03.08.2022 00:00:00 03.08.2022 20:00:00 
         4 02.08.2022 00:00:00 02.08.2022 21:00:00 
         3 01.08.2022 00:00:00 01.08.2022 22:00:00 
         2 31.07.2022 00:00:00 31.07.2022 23:00:00 
         1 30.07.2022 00:00:00 31.07.2022 00:00:00 
         0 29.07.2022 00:00:00 30.07.2022 01:00:00

If we select the data:

SELECT
    ID, 
    To_Char(DATE_DD, 'dd.mm.yyyy hh24:mi:ss') "DATE_DD", 
    To_Char(DATE_HH, 'dd.mm.yyyy hh24:mi:ss') "DATE_HH"
FROM
    tbl

... with column DATE_DD used to filter the data any of folowing WHERE conditions returns the same rows...

WHERE
-- Between
    DATE_DD Between To_Date('19.08.2022 00:00:00', 'dd.mm.yyyy hh24:mi:ss') And To_Date('21.08.2022 00:00:00', 'dd.mm.yyyy hh24:mi:ss')
-- or >= and <=
    DATE_DD >= To_Date('19.08.2022 00:00:00', 'dd.mm.yyyy hh24:mi:ss') And DATE_DD <= To_Date('21.08.2022 00:00:00', 'dd.mm.yyyy hh24:mi:ss')
-- or >= and < ( 1)
    DATE_DD >= To_Date('19.08.2022 00:00:00', 'dd.mm.yyyy hh24:mi:ss') And DATE_DD < To_Date('21.08.2022 00:00:00', 'dd.mm.yyyy hh24:mi:ss')   1

... the result is the same....

        ID DATE_DD             DATE_HH           
---------- ------------------- -------------------
        23 21.08.2022 00:00:00 21.08.2022 02:00:00 
        22 20.08.2022 00:00:00 20.08.2022 03:00:00 
        21 19.08.2022 00:00:00 19.08.2022 04:00:00

... but if we try the same conditions on the column (DATE_HH) containing hours/minutes/seconds...

WHERE
    DATE_HH Between To_Date('19.08.2022 00:00:00', 'dd.mm.yyyy hh24:mi:ss') And To_Date('21.08.2022 00:00:00', 'dd.mm.yyyy hh24:mi:ss')
    DATE_HH >= To_Date('19.08.2022 00:00:00', 'dd.mm.yyyy hh24:mi:ss') And DATE_HH <= To_Date('21.08.2022 00:00:00', 'dd.mm.yyyy hh24:mi:ss')
    DATE_HH >= To_Date('19.08.2022 00:00:00', 'dd.mm.yyyy hh24:mi:ss') And DATE_HH < To_Date('21.08.2022 00:00:00', 'dd.mm.yyyy hh24:mi:ss')   1

... then the first two (between),(>= and <=) will return

        ID DATE_DD             DATE_HH           
---------- ------------------- -------------------
        22 20.08.2022 00:00:00 20.08.2022 03:00:00 
        21 19.08.2022 00:00:00 19.08.2022 04:00:00

... and the one from your question returns this:

        ID DATE_DD             DATE_HH           
---------- ------------------- -------------------
        23 21.08.2022 00:00:00 21.08.2022 02:00:00 
        22 20.08.2022 00:00:00 20.08.2022 03:00:00 
        21 19.08.2022 00:00:00 19.08.2022 04:00:00

As you can see it is the same dataset as the one filtered on DATE_DD column. Hope this will help you to see the difference and the reason to use the date filtering like this. Regards...

CodePudding user response：

Your proposed rewrite is not equivalent and could give different results.

Original version (I've included the parameter values and the filtering condition in the results to make it easier to follow):

select to_char(order_date,'YYYY-MM-DD HH24:MI') as order_date
     , :ord_dt1, :ord_dt2
     , '< :ord_dt2   1' as rule
from   table1
where  customer_id = nvl(:cust_no, customer_id)
and    order_date >= to_date(:ord_dt1, 'yyyymmdd')
and    order_date <  to_date(:ord_dt2, 'yyyymmdd')   1
order by order_date;

ORDER_DATE       :ORD_DT1   :ORD_DT2   RULE
---------------- ---------- ---------- --------------
2022-08-02 00:00 20220802   20220803   < :ord_dt2   1
2022-08-03 16:30 20220802   20220803   < :ord_dt2   1

2 rows selected.

Your proposed rewrite:

select to_char(order_date,'YYYY-MM-DD HH24:MI') as order_date
     , :ord_dt1, :ord_dt2
     , '<= :ord_dt2' as rule
from   table1
where  customer_id = nvl(:cust_no, customer_id)
and    order_date >= to_date(:ord_dt1, 'yyyymmdd')
and    order_date <= to_date(:ord_dt2, 'yyyymmdd')
order by order_date;

ORDER_DATE       :ORD_DT1   :ORD_DT2   RULE
---------------- ---------- ---------- -----------
2022-08-02 00:00 20220802   20220803   <= :ord_dt2

1 row selected.

Parameter :ord_dt2 when converted to a date evaluates to 2022-08-03 00:00, so :ord_dt2 1 is 2022-08-04 00:00.

2022-08-03 16:30 is less than 2022-08-04 00:00, but not less than or equal to 2022-08-03 00:00.

Sample data:

create table table1 (customer_id, order_date)
as
select 100, to_date('2022-08-02 00:00', 'YYYY-MM-DD HH24:MI') from dual union all
select 100, to_date('2022-08-03 16:30', 'YYYY-MM-DD HH24:MI') from dual union all
select 100, to_date('2022-08-04 19:15', 'YYYY-MM-DD HH24:MI') from dual union all
select 100, to_date('2022-08-05 00:00', 'YYYY-MM-DD HH24:MI') from dual;

Variable setup in SQL*Plus:

var ord_dt1 varchar2(8)
var ord_dt2 varchar2(8)

exec :ord_dt1 := '20220802'; :ord_dt2 := '20220803'