RE: Diff Between Count( * ) and Count( 1 )

RE: Diff Between Count( * ) and Count( 1 )

 

  

OK You guys... I was a little distracted yesterday, but I'm back now.

I was not implying that a full table scan was exclusive to count(*)... I just mentioned that it does, and you could have just has easily concluded that count(anything) would also do one.

Check Anthony Malinaro's book pages 161, 163, 532, 541 (Ed :)). "When you "count star," as in COUNT(*), what you are really counting is rows (regardless of actual value, which is why rows containing NULL and non-NULL values are counted). But when you COUNT a column, you are counting the number of non-NULL values in that column".

Now I gather, Count(expr) will return all the not null rows that evaluate to that expression, and count(0), count(1), etc. return true for that expression, no?

Interesting is to notice the timings and expressions below: Rowid, beats out all except count(column)... where column is a number, Source is close but is varchar2.


DBMON @ ntdev> Prompt Row Count using all columns
Row Count using all columns
DBMON @ ntdev> select count(*) from sys.source$;

COUNT(*)
----------
97674

1 row selected.

Elapsed: 00:00:00.43
DBMON @ ntdev> Prompt Row Count using a specific numbered column
Row Count using a specific numbered column
DBMON @ ntdev> select count(1) from sys.source$;

COUNT(1)
----------
97674

1 row selected.

Elapsed: 00:00:00.23
DBMON @ ntdev> Prompt Row Count using the pseudo-column rownum
Row Count using the pseudo-column rownum
DBMON @ ntdev> select max(rownum) from sys.source$;

MAX(ROWNUM)
-----------
97674

1 row selected.

Elapsed: 00:00:00.23
DBMON @ ntdev> Prompt Row Count using the ROWID column
Row Count using the ROWID column
DBMON @ ntdev> select count(rowid) from sys.source$;

COUNT(ROWID)
------------
97674

1 row selected.

Elapsed: 00:00:00.17
DBMON @ ntdev> Prompt Counting a Not-NULL column
Counting a Not-NULL column
DBMON @ ntdev> select count(obj#) from sys.source$;

COUNT(OBJ#)
-----------
97674

1 row selected.

Elapsed: 00:00:00.18
DBMON @ ntdev> Prompt Counting a NULL column
Counting a NULL column
DBMON @ ntdev> select count(source) from sys.source$;

COUNT(SOURCE)
-------------
97674

1 row selected.

Elapsed: 00:00:00.17



COUNT(*) => COUNT(*)
COUNT(0) => COUNT(*)
COUNT(1) => COUNT(*)
COUNT(9) => COUNT(*)
COUNT(+1) => COUNT(*)
COUNT(-1) => COUNT((-1))
COUNT(1 + 1) => COUNT(2)
COUNT(1 - 1) => COUNT(0)
COUNT('A') => COUNT(*)
COUNT('A' || 'A') => COUNT('AA')
COUNT(37 * 45 + 12) => COUNT(1677)
COUNT(ROWNUM) => COUNT(ROWNUM)
COUNT(TO_CHAR(1)) => COUNT('1')
COUNT(SYSDATE) => COUNT([Email Address Removed] Patterson
Database Administrator
joel.[Email address protected]
x72546
904 727-2546

-----Original Message-----
From: Edwards, Ed [mailto:Ed.[Email address protected]
Sent: Monday, July 02, 2007 3:09 PM
To: Patterson, Joel; [Email address protected]
Subject: RE: Diff Between Count( * ) and Count( 1 )
Importance: High

That's a 10-4, good buddy! Hehe.


-----Original Message-----
From: Joel.[Email address protected] [mailto:Joel.[Email address protected]
Sent: Monday, July 02, 2007 2:55 PM
To: Edwards, Ed; [Email address protected]
Subject: RE: Diff Between Count( * ) and Count( 1 )



OK you guys, I may stand corrected. I am in the middle of a few things all of a sudden, offsite, with a cool FL lightning storm threatening my computer.

See you'all.

Joel Patterson
Database Administrator
joel.[Email address protected]
x72546
904 727-2546

-----Original Message-----
From: Edwards Ed [mailto:Ed.[Email address protected]
Sent: Monday, July 02, 2007 1:41 PM
To: Patterson, Joel
Subject: RE: Diff Between Count( * ) and Count( 1 )

**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as normal
**Anything below this line is the original email text


Well, well, well!
It's joel! Hehe. How have you been? Hey, Joel, what's the difference between count(*) and count(rowid)? Hehe.






Please take a few minutes to provide feedback on the quality of service you received from our staff. The Department of Education values your feedback as a customer. Commissioner of Education Jeanine Blomberg is committed to continuously assessing and improving the level and quality of services provided to you.Simply use the link below. Thank you in advance for completing the survey.


[Email address protected]




-----Original Message-----
From: Joel [Email address protected]
Sent: Monday, July 02, 2007 1:35 PM
To: LazyDBA Discussion
Subject: RE: Diff Between Count( * ) and Count( 1 )

Count doesn't count nulls. Count(*) counts rows... I believe rowid, so you will get a full table scan and a count of the rows. Count(1) counts the first column, so if there are nulls they are not counted.

Joel Patterson
Database Administrator
joel.[Email address protected]
x72546
904 727-2546

-----Original Message-----
From: Martin Farber [mailto:oracledba-ezmlmshield-x43659120.[Email address protected]
Sent: Sunday, July 01, 2007 11:31 PM
To: LazyDBA Discussion
Subject: Re: Diff Between Count( * ) and Count( 1 )

Count(*) does not read the entire row. As a matter of fact, of there is a
unique index - it just counts index entries. Otherwise it does a blockwise
scan up to the high water mark. Count(1) or the count of a non-null column
is no faster or slower.

That probably hasn't been true since Oracle 6.

Keep up! 8^)

<<MrO>>

----- Original Message -----
From: "Prajeet" <oracledba-ezmlmshield-x71457485.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Sunday, July 01, 2007 7:50 PM
Subject: RE: Diff Between Count( * ) and Count( 1 )


>
> Hi,
>
> As far as I know it is as below.
>
> For counting rows in a table we have three options i.e., '*', '1' or
column name.
>
> If you use count(coumn name), oracle reads only columns with non null
values and.
> If you use count(*), then oracle reads whole row of the table.
> And if we use count(1), for every row 1 is returned, and these values are
counted by oracle to give the result set.
>
> These all give their respective results, count(column name) can give
different result from others but performance wise count(1) can be the best
option because in this case oracle don't need to read the whole row where as
in case of count(*), oracle server has to read whole row (every row).
>
>
>
> Thanks & Regards,
> Prajeet Singh Kahlon
>
> UBS Investment Bank
>
>
> -----Original Message-----
> From: Jeff Ferrel [mailto:oracledba-ezmlmshield-x67923213.[Email address
protected]
> Sent: Saturday, June 30, 2007 5:20 AM
> To: LazyDBA Discussion
> Subject: RE: Diff Between Count( * ) and Count( 1 )
>
> I think one has a *, and the other has a 1.
>
>
> Jeffrey C. Ferrel
> DMS III
> State of Nevada
> 775.687.9327
> [Email address protected]
>
> -----Original Message-----
> From: Israel Laureano Lazcano
[mailto:oracledba-ezmlmshield-x8416304.[Email address protected]
> Sent: Friday, June 29, 2007 2:13 PM
> To: LazyDBA Discussion
> Subject: Re: Diff Between Count( * ) and Count( 1 )
>
> As far as I know, there's no difference.
>
> dgaikwad escribió:
>
> >Hi DBA's
> >
> >Can anyone tell me Which is to use COUNT(*) or COUNT(1) and Why?
> >
> >What is exact Difference between them?
> >
> >
> >
> >Thanks & Regards,
> >Dinesh G.
> >
> >
> >---------------------------------------------------------------------
> >TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post
> >a dba job: http://jobs.lazydba.com To Subscribe :
> >http://www.LazyDBA.com To unsubscribe:
> >http://www.lazydba.com/unsubscribe.html
> >
> >
> >
> >
> >
> >
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com To
unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com To
unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
> Visit our website at http://www.ubs.com
>
> This message contains confidential information and is intended only
> for the individual named. If you are not the named addressee you
> should not disseminate, distribute or copy this e-mail. Please
> notify the sender immediately by e-mail if you have received this
> e-mail by mistake and delete this e-mail from your system.
>
> E-mails are not encrypted and cannot be guaranteed to be secure or
> error-free as information could be intercepted, corrupted, lost,
> destroyed, arrive late or incomplete, or contain viruses. The sender
> therefore does not accept liability for any errors or omissions in the
> contents of this message which arise as a result of e-mail transmission.
> If verification is required please request a hard-copy version. This
> message is provided for informational purposes and should not be
> construed as a solicitation or offer to buy or sell any securities
> or related financial instruments.
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

Oracle LazyDBA home page