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/~jwilton



> > 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)

> > /