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

Niall Litchfield wrote:

> not 100 million but 800k (because its my crappy pc). t1 is created by
> iterating duplicating all_objects and then doing an INSERT /*+ APPEND */
> INTO T1 SELECT * FROM T1 a number of times.
>
> SQL> select count(*) from t1;
>
> COUNT(*)
> ----------
> 858176
>
> Elapsed: 00:00:13.01
> SQL> select count(1) from t1;
>
> COUNT(1)
> ----------
> 858176
>
> Elapsed: 00:00:12.09
> SQL> select count(*) from t1;
>
> COUNT(*)
> ----------
> 858176
>
> Elapsed: 00:00:12.08
> SQL> select count(rowid) from t1;
>
> COUNT(ROWID)
> ------------
> 858176
>
> Elapsed: 00:00:13.00
> SQL> ;
> 1* select count(rowid) from t1
> SQL> /
>
> COUNT(ROWID)
> ------------
> 858176
>
> Elapsed: 00:00:12.09
> SQL> select count(object_id) from t1;
>
> COUNT(OBJECT_ID)
> ----------------
> 858176
>
> Elapsed: 00:00:13.01
> SQL> ;
> 1* select count(object_id) from t1
> SQL> /
>
> COUNT(OBJECT_ID)
> ----------------
> 858176
>
> Elapsed: 00:00:12.09
> SQL> spool off
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
>
> ******************************************
>
> "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
> >

In my book that answers the question. There is no significant difference.

Thanks.

Daniel Morgan Received on Tue Apr 23 2002 - 12:37:15 CDT