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


Oracle LazyDBA home page