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

In the first edition of Corrigan and Gurry's book Oracle Performance Tuning (1993), which covers Version 6 (and Version 7 which was just coming out), they state that they have conducted tests and count(*) consistently runs between 15 and 20 percent faster than count(1). So those of you still doing new development on Version 6 will want to take note of this.

Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
I said it "looked" clear - Riddick

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Michael Brown Sent: Thursday, July 08, 2004 12:06 PM
To: oracle-l_at_freelists.org
Subject: RE: Difference between count(1) and count(*)

If I remember what Tom Kyte said about it correctly, there was a version where it did make a difference (6, early 7 maybe) and thus a myth was born. According to Tom, the parser is now coded so that count(1) and count(*) use exactly the same code path so any differences you see between the two would occur on multiple runs of the same statement, i.e. they are caused by external events.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Peter Miller Sent: Thursday, July 08, 2004 9:40 AM
To: oracle-l_at_freelists.org
Subject: 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
-----------------------------------------------------------------

----------------------------------------------------------------
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
-----------------------------------------------------------------