RE: Difference between count(1) and count(*)
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 9 Jul 2004 11:49:33 +0200
ah, a combination of two of my favorite topics:
the ANSI/ISO standard and null-values ;-) so I can't resist ...
the COUNT function basically accepts an expression as its argument.
normally, you would specify a column name -- or use the "*" syntax
which is defined to have a special meaning: it always returns the
cardinality (i.e. number of rows)
so as a side comment: I think you should use count(*) for that purpose, and
not count(1) or whatever.
now, let's forget about the "*" special case, and concentrate on the
expressions.
to keep things simple, let's forget about count(distinct ...) as well,
because it is not relevant in this context.
like I said before, normally you would use a column name as an argument:
SQL> select count(empno), count(comm), count(comm+1) from emp;
COUNT(EMPNO) COUNT(COMM) COUNT(COMM+1)
------------ ----------- -------------
14 4 4
Note that aggregate functions are supposed to ignore null-values, according
to the ANSI/ISO standard,
whether or not you like that -- I personally have my doubts :-)
the next step "away from reality" is specifying a constant expression:
SQL> select count(6*7), count(42), count(null) from emp;
COUNT(6*7) COUNT(42) COUNT(NULL)
---------- --------- -----------
14 14 0
Nothing weird here: for the EMP table, consisting of 14 rows, a set with 14
"virtual column" values is counted.
So the first two examples are counting a set of 14 literals, all being the
same of course, but since you don't specify the DISTINCT operator, the
duplicates are counted. and the count(null) must return zero, because it is
supposed to count the number of values in a set consisting of 14
null-values. since null-values are ignored by the count function (see above)
you are actually counting ther number of values in an empty set -- which is
zero, again fully compliant with the ANSI/ISO standard.
Kind regards,
Lex.
---------------------------------------------
visit my website at http://www.naturaljoin.nl
---------------------------------------------
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Mark W. Farnham
Sent: Thursday, July 08, 2004 19:41
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Difference between count(1) and count(*)
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
----------------------------------------------------------------- --------
--------------------------------------------
OBJ# NOT NULL
NUMBER
LINE NOT NULL
NUMBER
SOURCE
VARCHAR2(4000)
EXTRA_COLUMN1
VARCHAR2(4000)
EXTRA_COLUMN2 NOT NULL
NUMBER
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@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
-----------------------------------------------------------------
-- Binary/unsupported file stripped by Ecartis --
-- Type: text/x-vcard
-- File: Lex de Haan.vcf
----------------------------------------------------------------
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:
