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

  • From: DENNIS WILLIAMS <DWILLIAMS@xxxxxxxxxxxxx>
  • To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 8 Jul 2004 12:09:03 -0500

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@xxxxxxxxxxxxx 
I said it "looked" clear - Riddick


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Michael Brown
Sent: Thursday, July 08, 2004 12:06 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Peter Miller
Sent: Thursday, July 08, 2004 9:40 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx]
Sent: 08 July 2004 12:47
To: 'oracle-l@xxxxxxxxxxxxx'
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@xxxxxxxxxx]=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@xxxxxxxxxxxxx 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@xxxxxxxxxxxxx

put 'unsubscribe' in the subject line.

--

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

FAQ is at //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@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: