From NDATFM@labor.state.ny.us Thu, 25 Oct 2001 08:28:55 -0700 From: "Mercadante, Thomas F" Date: Thu, 25 Oct 2001 08:28:55 -0700 Subject: RE: [Q] what difference between count(0), count(1) and Message-ID: MIME-Version: 1.0 Content-Type: text/plain Title: RE: [Q] what difference between count(0), count(1) and All,   I just ran some explain plans for select count(*).   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.   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 -----Original Message-----From: Amar Kumar Padhi [mailto:TS2017@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 [mailto:Arn.Klammer@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@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 >Reply-To: ORACLE-L@fatcity.com >To: Multiple recipients of list ORACLE-L >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 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@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@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@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@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). _________________________________________________________________ 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: dist cash   INET: mccdba@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@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@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@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).