| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: [Q] what difference between count(0), count(1) and
<SPAN
class=633192115-25102001>All,
<SPAN
class=633192115-25102001>
I just
ran some explain plans for select count(*).
<SPAN
class=633192115-25102001>
It
looks to me that it uses the first index it finds created on any Not Null column
for select count of *, 0, 1, 2 etc to perform the count. In my case, it
used the first alphabetical index it found for the table.
<SPAN
class=633192115-25102001>
A
select count(column_name) uses an index on that column if it exists, otherwise
uses a full table scan.
Hope
this helps.
Tom
Mercadante Oracle Certified
Professional
<FONT face=Tahoma
size=2>-----Original Message-----From: Amar Kumar Padhi
[mailto:TS2017_at_emirates.com]Sent: Thursday, October 25, 2001 11:28
AMTo: Multiple recipients of list ORACLE-LSubject: RE:
[Q] what difference between count(0), count(1) and
Afaik, Oracle has to undergo the additional overhead of
converting a * with the actual table columns. This is the default behaviour.
Writing count(1) saves this overhead. This was applicable in 7.3. I am not
aware if 8 or 8i behave differently if count(*) is mentioned.
rgds amar
-----Original Message----- From: Arn
Klammer [<A
href="">mailto:Arn.Klammer_at_austrac.gov.au]
Sent: Thursday, October 25, 2001 6:40 AM <FONT
size=2>To: Multiple recipients of list ORACLE-L <FONT
size=2>Subject: Re: [Q] what difference between count(0), count(1) and
As I understand it, count(*) counts all rows. Anything
other than "*" is treated as an expression and it returns the number of rows
where that expression evaluates to not null. So what Deepak said below
about count(col1) giving the count of non-null instances of col1 is
correct. That also means that count(0), count(1), etc should behave the
same as count(*), as 0, 1, etc are not null.
Now, is count(*) less efficient in execution than
count(1)? I've been asked this before, and I don't know. I
would've thought count(*) and count(1) would be the same,
execution-wise. Is this correct?
-a
>>> mccdba_at_hotmail.com 25/10/2001 11:30:17
>>> This message has been scanned by
MAILSweeper. <FONT
size=2>************************************************************
Thank you for answer. How about count(0)?
>From: Deepak Thapliyal
<deepakthapliyal_at_yahoo.com> >Reply-To:
ORACLE-L_at_fatcity.com >To: Multiple recipients of
list ORACLE-L <ORACLE-L_at_fatcity.com> <FONT
size=2>>Subject: Re: [Q] what difference between count(0), count(1) and
count(*) >Date: Wed, 24 Oct 2001 11:41:11
-0800 > >i think
count(*) gives count of all columns .. where >as
count(col1) gives count for col1 ignoring nulls in <FONT
size=2>>col1 > <FONT
size=2>>Deepak >--- aaa aaa
<mccdba_at_hotmail.com> wrote: > > Can anyone
tell me what is difference between : > >
> > select count(*) ... from ..
> > > >
select count(0) ... > > <FONT
size=2>> > select coun (1) ... >
> select count(2) ... > >
> > > > Thanks.
> > > > <FONT
size=2>> > <FONT
size=2>>_________________________________________________________________
size=2>> > > > -- <FONT size=2>> > Please see the official ORACLE-L FAQ: <FONT size=2>> > <A href=""
<FONT
size=2>**********************************************************************This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager.
size=2>**********************************************************************
 |
 |