Re: Why aren't these queries the same?

Just out of interest....

select count(*) from testtable: 398,624 records in 8.52 seconds, 8.72 seconds and 8.01 seconds
select count(1) from testtable: 398,624 records in 8.22 seconds, 8.91 seconds and 8.21 seconds
select count(rowid) from testtable: 398,624 records in 8.61 seconds, 8.22 seconds and 8.92 seconds

That's with a clean shutdown and startup between each test run, on a somewhat indequate Celeron 333, single hard disk.

On average, count(*) takes 8.41 seconds, count(1) takes 8.44 seconds, and count(rowid) takes 8.58 seconds.

So yeah, it's another of those (unbenchmarked) myths.

Regards
HJR

--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================


"Paul Brewer" <paul_at_paul.brewers.org.uk> wrote in message
news:3c8a7f7b_1_at_mk-nntp-1.news.uk.worldonline.com...


> "damorgan" <damorgan_at_exesolutions.com> wrote in message

> news:3C8943DC.E1B25D99_at_exesolutions.com...

> > As long as you are looking to speed things up ... try replaeing count(*)

> with

> > count(1) or count(rowid).

> >

> > Daniel Morgan

> >

> >

> >

> > Ed Stevens wrote:

> >

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

> > > /

> > >

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

> > >

> > > --

> > > Ed Stevens

> > > (Opinions expressed do not necessarily represent those of my

employer.)


> >

>


> Sorry, Daniel, but this is another of those myths...

>


> Paul

>
>
>