Re: count(1) vs. count(*)

Great. Another myth goes down in flames.

And I've seen this one repeated in numerous places. Last time, I think, in an OCP prep book for 9i.

Thanks.

Daniel Morgan

Alan wrote:

> I've done
>
> SELECT COUNT(*)
> SELECT COUNT(ROWNUM)
> and even
> SELECT MAX(ROWNUM)
>
> on tables with 1,000,000+ rows.
> No significant difference...
>
> "Daniel Morgan" <damorgan_at_exesolutions.com> wrote in message
> news:3CC579DE.3833EBA2_at_exesolutions.com...
> > Bricklen Anderson wrote:
> >
> > > uh oh, this discussion again...
> > >
> > > Maurice Samuels wrote:
> > > >
> > > > hi,
> > > > can someone explain to me why the count(1) syntax works and is faster
> than a count(*)?
> > > > i looked in the oracle docs and while it doesn't specifically go into
> the count(1) syntax, it does read
> > > > that the count operator works as follows:
> > > > COUNT({* | [DISTINCT|ALL] expr})
> > > >
> > > > then in the EXPRESSIONS section of the docs, it reads that an
> expression can be text, number, etc.
> > > > i'm just a little perplexed by the count(1) syntax.
> > > > thanks in advance.
> > > > -maurice
> > > > samuels_at_seas.upenn.edu
> >
> > Come on Bricklen, don't just point out the obvious ... stick your neck out
> and help him. <g>
> >
> > Maurice ... search the google archives.
> >
> > How was that for avoiding a confragration.
> >
> > One of these days I'm going to insert 100,000,000 rows in a table and try
> it out. Anyone have the time and
> > incliniation? If so try: SELECT COUNT(*), SELECT COUNT(1), and SELECT
> COUNT(ROWID).
> >
> > Daniel Morgan
> >
Received on Tue Apr 23 2002 - 14:32:07 CDT