Re: Why aren't these queries the same?

On Thu, 07 Mar 2002 17:37:18 GMT, "John Dorlon" <john_at_ezsql.net> wrote:

>> I did confirm that trunc(sysdate) returned the same value as sysdate and
>> trunc(scott.table_a.mod_date) returned the same value as
>scott.table_a.mod_date.
>
>Ed,
>
> trunc(sysdate) does not return the same value as sysdate. I think the
>reason that it looks the same when you checked it is because your default
>date format does not show hours minutes and seconds.
>
>Trunc(sysdate) = midnight
>sysdate = right now.
>
>-John
>
>

Exactly what we discovered. Using

where (trunc(sysdate) - trunc(scott.table_a.mod_date) >= 1

returns all rows timestamped yesterday or earlier, while using

where mod_date < (sysdate - 1)

returns all rows timestamped over 24 hours ago. Close, but not quite the same thing!

But this time it had a happy ending. The query was to select records for a periodic purge, so the difference of a few hours worth of records was not critical, and the revised query showed a 99% reduction in i/o in TKPROF analysis.

--
Ed Stevens
(Opinions expressed do not necessarily represent those of my employer.)