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: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 8 Jul 2004 14:40:53 -0400
Message-ID: <KNEIIDHFLNJDHOOCFCDKAEDAFAAA.mwf@rsiz.com> 





Cool. Also, there was a question about select(null). I didn't consult the
ANSI SQL spec, so I'm not sure what it is supposed to mean. But the results
are pretty interesting. I tacked a reminder about the difference between (*)
and (some_column) at the end, too.



SQL> desc junk

 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------


----

 A                                                  DATE





SQL> select count(*) from junk;



  COUNT(*)





         2



SQL> select * from junk;



A




29-OCT-02



29-OCT-02


SQL> insert into junk values (null);



1 row created.



SQL> set null ~


SQL> select * from junk;



A




29-OCT-02



29-OCT-02



~



SQL> select count(null) from junk;



COUNT(NULL)





          0



SQL> commit;



Commit complete.



SQL> select count(null) from junk;



COUNT(NULL)





          0    <--- interesting. Without consulting anything for the actual
defined meaning, I would have expected 1. This entire tuple (single column
though it may be) is in fact null for one of the rows in table junk. If that
count(null) syntax means anything, I would expect it to count entirely null
tuples. Otherwise, I'm thinking it should report 'no such column' or some
other such syntactical objection. Sigh. Pretty much any nonsense constant
seems to behave the same as count(*) as regards answers.



SQL> select count(*) from junk;



  COUNT(*)





         3



SQL> select count(a) from junk;



  COUNT(A)





         2    <--- tuples that are null for a counted column don't get
counted.



Now, consider this table:


SQL> set null ~
SQL> set timing on;
SQL> desc junk3
 Name                                                              Null?


Type


Extra column1 is simply an extra copy of source.
Extra column2 is (obj#*100000)+line  (my maximum object number in this
database is under 100,000 so this is nicely unique)
No statistics, no indexes. Just the table.



SQL> select count(rowid) from junk3;



COUNT(ROWID)





      138942



Elapsed: 00:00:06.03


SQL> select count(*) from junk3;



  COUNT(*)





    138942



Elapsed: 00:00:06.03


SQL> select count(obj#) from junk3;



COUNT(OBJ#)





     138942



Elapsed: 00:00:06.03


SQL> select count(line) from junk3;



COUNT(LINE)





     138942



Elapsed: 00:00:06.03


SQL> select count(source) from junk3;



COUNT(SOURCE)





       138942



Elapsed: 00:00:06.05


SQL> select count(extra_column1) from junk3;



COUNT(EXTRA_COLUMN1)





              138942



Elapsed: 00:00:06.04


SQL> select count(extra_column2) from junk3;



COUNT(EXTRA_COLUMN2)





              138942



Elapsed: 00:00:06.04



So far, so good.



Now let's frig things up. Add column extra_column3 number, and update it so
it has the same contents as extra_column2.



And also, create junk4 as select * from junk3.



SQL> select max(extra_column3) from junk4;



MAX(EXTRA_COLUMN3)





        3316802187



Elapsed: 00:00:05.06


SQL> update junk4 set extra_column3 = NULL where extra_column3 = 3316802187;
<---- just for fun so you can see it really must be checking



1 row updated.



Elapsed: 00:00:06.09


SQL> commit;



Commit complete.



Elapsed: 00:00:00.00


SQL> select count(extra_column3) from junk4;   <----- select * so no
extraneous chaining or migration



COUNT(EXTRA_COLUMN3)





              138941



Elapsed: 00:00:05.07


SQL> select count(extra_column3) from junk3;   <----- all frig'd up.



COUNT(EXTRA_COLUMN3)





              138942



Elapsed: 00:02:13.02



So, while it is possible to frig a table up so it takes quite a while to
count by some column, it does not have to do with nullability. Now I suppose
it could, since if a column is not null, effectively scanning rowids should
give the same result, rather than an extra overhead for really pulling in
the column. As of the version on this laptop, no joy on that shortcut.



To make a long story short, if you just want to count the rows including
nulls the defined syntax of count(*) is about as good as anything and it has
the value of being clear about what you are doing.



If you need to exclude nulls on some column, use count(column_name).



If the count on some column takes a long time compared to other columns,
your table is probably screwed up pathologically, such as the effects of
adding and populating a column to a lot of rows in nearly full blocks.



SQL> select * from v$version;



BANNER





Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
PL/SQL Release 9.0.1.1.1 - Production


CORE    9.0.1.1.1       Production


TNS for 32-bit Windows: Version 9.0.1.1.0 - Production
NLSRTL Version 9.0.1.1.1 - Production



Wow. my machine is very slow compared to yours.



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


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



Received on Thu Jul 08 2004 - 13:37:52 CDT












Original text of this message













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