Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Difference between count(1) and count(*)

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

From: Srinivasan Vasan <Vasan.Srinivasan_at_churchill.com>
Date: Fri, 9 Jul 2004 09:45:54 +0100
Message-ID: <4661DD1EF5C4EF4B9D2BCE3A3E52EF3304C321D8@brcexm04.churchill.com> 





And if you are on 8.1.7.4, it still makes some difference if you are running
RBO as shown below:



Using Sys.Source$ for the test



Row Count using all columns



  COUNT(*)





   1756877



Elapsed: 00:00:05.25


Row Count using a specific numbered column



  COUNT(1)





   1756877



Elapsed: 00:00:05.50


Row Count using the pseudo-column rownum



MAX(ROWNUM)





    1756877



Elapsed: 00:00:06.72


Row Count using the ROWID column



COUNT(ROWID)





     1756877



Elapsed: 00:00:05.67


Counting a Not-NULL column



COUNT(OBJ#)





    1756877



Elapsed: 00:00:05.56


Counting a NULL column



COUNT(SOURCE)





      1756877



Elapsed: 00:00:05.63



Cheers, 



Vasan (x5707) 


Mailpoint 28



Vasan Srinivasan                                   * 020 8313 5707
Infrastructure Service Manager              * 020 8313 5646


Oracle Technologies


Churchill Insurance, IT Department


Purple Floor, Phase 1, Churchill Court


1 Westmoreland Road,


Bromley, Kent, BR1 1DP.


* Vasan.Srinivasan_at_churchill.com


Mobile * 07710 154 987


http://oratech




Views Presented here are not necessarily the views

                          of my Employer
============================================ 






-----Original Message-----



From: DENNIS WILLIAMS [mailto:DWILLIAMS_at_LIFETOUCH.COM] 
Sent: 08 July 2004 18:09


To: 'oracle-l_at_freelists.org'


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


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


___________________________________________________________________________ 


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. 

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

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




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

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


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



Received on Fri Jul 09 2004 - 03:42:43 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US