RE: Difference between count(1) and count(*)

1,749,951 rows returned in every case

count(rowid) - approx 3.2 secs
count(obj#)  - approx 3.5 secs
count(1)     - approx 3.6 secs
count(*)     - approx 3.6 secs
count(rownum)- approx 4.6 secs
count(source)- approx 8.6 secs

-----Original Message-----
From: Srinivasan Vasan [mailto:Vasan.Srinivasan_at_churchill.com] Sent: 08 July 2004 12:47
To: 'oracle-l_at_freelists.org'
Subject: RE: Difference between count(1) and count(*)

Try the following test (using your own table instead of sys.source$ if = you
wish) on your setup and forever dispel any myths: :-)
=20

set trimspool on

set timing on

spool test_count.lst

Prompt Using Sys.Source$ for the test

Prompt

Prompt Row Count using all columns

select count(*) from sys.source$;

Prompt Row Count using a specific numbered column

select count(1) from sys.source$;

Prompt Row Count using the pseudo-column rownum

select max(rownum) from sys.source$;

Prompt Row Count using the ROWID column

select count(rowid) from sys.source$;

Prompt Counting a Not-NULL column

select count(obj#) from sys.source$;

Prompt Counting a NULL column

select count(source) from sys.source$;

spool off

set timing off

set trimspool off

=20

Cheers,=20

=20

Vasan.

-----Original Message-----
From: Jared Still [mailto:jkstill_at_cybcon.com]=20 Sent: 08 July 2004 08:20
To: Oracle-L Freelists
Subject: Re: Difference between count(1) and count(*)

=20

On Wed, 2004-07-07 at 23:28, Lyndon Tiu wrote:

> On Thu, 8 Jul 2004 11:27:28 +0530 oracle-l_at_freelists.org wrote:

> > Dear All,

> > Can you let me know the difference between count(*) and count(1) ?

>=20

> count(1) is supposed to be more efficient and faster.

=20

Notice the qualifier: 'supposed to be'

=20

Was the claim backed up by evidence?

=20

Jared

=20

=20

=20

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org

put 'unsubscribe' in the subject line.

--

Archives are at http://www.freelists.org/archives/oracle-l/

FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

-----------------------------------------------------------------


_________________________________________________________________________=
__=20


This email and any attached to it are confidential and intended only for =
the
individual or entity to which it is addressed.  If you are not the =
intended
recipient, please let us know by telephoning or emailing the sender.  =
You
should also delete the email and any attachment from your systems and =
should
not copy the email or any attachment or disclose their content to any =
other
person or entity.  The views expressed here are not necessarily those of
Churchill Insurance Group plc or its affiliates or subsidiaries. Thank =
you.=20

Churchill Insurance Group plc.  Company Registration Number - 2280426.
England.=20

Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1
1DP.=20




----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------