| 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
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 [mailto:Arn.Klammer_at_austrac.gov.au]
Sent: Thursday, October 25, 2001 6:40 AM
To: Multiple recipients of list ORACLE-L
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.
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>
>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
>col1
>
>Deepak
>--- aaa aaa <mccdba_at_hotmail.com> wrote:
> > Can anyone tell me what is difference between :
> >
> > select count(*) ... from ..
> >
> > select count(0) ...
> >
> > select coun (1) ...
> > select count(2) ...
> >
> >
> > Thanks.
> >
> >
> >
>_________________________________________________________________
> > Get your FREE download of MSN Explorer at
> > http://explorer.msn.com/intl.asp
> >
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: aaa aaa
> > INET: mccdba_at_hotmail.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX:
> > (858) 538-5051
> > San Diego, California -- Public Internet
> > access / Mailing Lists
> >
>--------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be removed
> > from). You may
> > also send the HELP command for other information
> > (like subscribing).
>
>
>__________________________________________________
>Do You Yahoo!?
>Make a great connection at Yahoo! Personals.
>http://personals.yahoo.com
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Deepak Thapliyal
> INET: deepakthapliyal_at_yahoo.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
-- Author: dist cash INET: mccdba_at_hotmail.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ********************************************************************** 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. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ********************************************************************** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arn Klammer INET: Arn.Klammer_at_austrac.gov.au Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Thu Oct 25 2001 - 09:48:10 CDT
 |
 |