Re: Why aren't these queries the same?

The performance difference is because of the trunc on mod_date not on sysdate.

You could also consider

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

This should be exactly correct as well as fast. "Ed Stevens" <Ed_Stevens_at_nospam.noway.nohow> wrote in message news:3c87f24c.295970823_at_ausnews.austin.ibm.com...
> 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.)
Received on Sat Mar 09 2002 - 10:24:24 CST