Re: count(rowid) vs count(*)

My tests using 7.3.4 indicate that using count(*) is about the same or a little faster than count(1) or count(rowid).

On a table with 3.36 million rows, here are my results:

count(*)           2 minutes, 20 seconds
count(rowid)    2 minutes, 22 seconds
count(1)           2 minutes, 22 seconds

-tee

<<<remove the underscores (_) to reply to my email address>>>

Roger Jackson wrote:

> Andy,
>
> I would agree with you. I've actually also found that the Oracle DBA
> Certification Exam Guide, written by Oracle Press also specifies that you
> should use count(rowid) or count(1) instead of count(*).
>
> But it really doesn't explain why.
>
> It looks like I will have to do the tests myself.
>
> Andy Marden <amarden_at_altavista.net> wrote in message
> news:3789E2C0.F05EB6E6_at_altavista.net...
> > Tests that I ran - a while back now, must be on 7.1 I guess, showed
> > this to be untrue, and that count(*) performed better than count(1).
> > Maybe this has been optimised (it would make sense wouldn't it?)
> >
> > Andy
> >
> > Roger Jackson wrote:
> > >
> > > Hi,
> > >
> > > I was reading an article just recently which stipulated that you should
> not
> > > use count(*) to determine the number of rows in a table, but use
> count(1) or
> > > count(ROWID) instead. These options are faster because they bypass some
> > > unnecessary operations in Oracle's SQL processing mechanism.
> > >
> > > Can somebody explain to me why this would be faster and what mechanisms
> are
> > > bypassed.
> > >
> > > TIA
> > >
> > > Roger
Received on Tue Jul 13 1999 - 12:26:52 CDT