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

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

>