Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Why aren't these queries the same?
On Fri, 8 Mar 2002, damorgan wrote:
> As long as you are looking to speed things up ... try replaeing
> count(*) with count(1) or count(rowid).
Erm, are you sure? Have a look at:
http://www.speakeasy.org/~jwilton/oracle/count-star.html
> Ed Stevens wrote:
>
> > but they don't return the same results.
> >
> > 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.
Really? The original poster must be a hard worker, because he was certainly working late! Sysdate only equals trunc(sysdate) at 12 midnight.
Allow me to suggest that he
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
and start poking around again. I think this problem will swiftly reveal itself then.
Best of luck!
-- Jeremiah Wilton http://www.speakeasy.net/~jwiltonReceived on Fri Mar 08 2002 - 23:11:48 CST
> > I'm going through a process of identifying our biggest I/O hogs and working with
> > the devleopers to fix them. One statement has us stumped. The original looks
> > like this
> >
> > select count(*)
> > from scott.table_a
> > where (trunc(sysdate) - trunc(scott.table_a.mod_date) >= 12)
> > /
> >
> > so what they are looking for are instances of mod_date that are 12 or more days
> > old.
> >
> > We re-wrote the query to look like this
> >
> > select count(*)
> > from scott.table_a
> > where mod_date < (sysdate - 12)
> > /