Home » SQL & PL/SQL » SQL & PL/SQL » Difference between count(*) and count(1) (Oracle 9i)
Difference between count(*) and count(1) [message #431210] Mon, 16 November 2009 03:54 Go to next message
kish_ulike
Messages: 30
Registered: June 2008
Location: chennai
Member

When we execute select count(*) from table_name it returns the number of rows.

What does count(1) do? What does 1 signifies over here? Is this same as count(*) as it gives the same result on execution?
Re: Difference between count(*) and count(1) [message #431211 is a reply to message #431210] Mon, 16 November 2009 03:59 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
There was an article on asktom.
There is no performance difference since Oracle internally changes it into count(1).
Re: Difference between count(*) and count(1) [message #431213 is a reply to message #431210] Mon, 16 November 2009 04:05 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1156159920245

Please read this

Sriram
Re: Difference between count(*) and count(1) [message #431215 is a reply to message #431211] Mon, 16 November 2009 04:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
So close, and yet still wrong.

SELECT COUNT(<any constant value>) gets converted internally into SELECT COUNT(*)
Re: Difference between count(*) and count(1) [message #431218 is a reply to message #431210] Mon, 16 November 2009 04:11 Go to previous messageGo to next message
kish_ulike
Messages: 30
Registered: June 2008
Location: chennai
Member

Thanks!!!!!!
Re: Difference between count(*) and count(1) [message #431267 is a reply to message #431218] Mon, 16 November 2009 06:54 Go to previous messageGo to next message
sajith741
Messages: 11
Registered: November 2009
Location: Singapore
Junior Member
Oracle is smart! Razz
It knows count(1) and count(*) are the same....
Maybe in very old versions <=7, count(1) should have been slower Smile
Re: Difference between count(*) and count(1) [message #431269 is a reply to message #431267] Mon, 16 November 2009 06:56 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Quote:
Maybe in very old versions <=7, count(1) should have been slower

Do you have any evidence for this?
Re: Difference between count(*) and count(1) [message #431275 is a reply to message #431269] Mon, 16 November 2009 07:09 Go to previous messageGo to next message
sajith741
Messages: 11
Registered: November 2009
Location: Singapore
Junior Member
I am sorry, would not be able to prove it as I do not have access to such an old Oracle version currently...
Re: Difference between count(*) and count(1) [message #431276 is a reply to message #431269] Mon, 16 November 2009 07:22 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, the only "proof" I have ever found was actually that count(*) is faster than count(1).

Reply to AskTom

Laughing
Re: Difference between count(*) and count(1) [message #431279 is a reply to message #431276] Mon, 16 November 2009 07:33 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Quality. Laughing
Re: Difference between count(*) and count(1) [message #497133 is a reply to message #431279] Fri, 04 March 2011 02:10 Go to previous messageGo to next message
knw15pwr
Messages: 134
Registered: March 2010
Senior Member
Sorry to re-open this age old debate.
I just wanted to confirm if COUNT(*) and COUNT(1) being the same thing holds true when we are counting the data that is being returned from multiple table and related joins. (Sorry, I ask here as i do not have an environment to test it)
I've read the entire asktom thread but its mostly a simple examples that have
select count(*/1) from table


Would the same logic hold true in this case too:
select count(*) 
from table_1, table_2..etc. 
where table_1.column11=table_2.column21 and table_1.column12=table_2.column22..etc

or 

select count(*) from (results of another complex query that has joins between multiple big tables)
Re: Difference between count(*) and count(1) [message #497136 is a reply to message #497133] Fri, 04 March 2011 02:26 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Same.

Regards
Michel
Re: Difference between count(*) and count(1) [message #497157 is a reply to message #497136] Fri, 04 March 2011 03:42 Go to previous messageGo to next message
knw15pwr
Messages: 134
Registered: March 2010
Senior Member
Thanks Michel. I had the same feeling, but my senior mentioned that it would be different in this case. (Index used, counts only first column blah blah blah...)
I fail to understand why it is so hard to convince people Sad
Re: Difference between count(*) and count(1) [message #497160 is a reply to message #497157] Fri, 04 March 2011 03:49 Go to previous message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Because people don't like to believe they could have ever believed something that's completely wrong?
If you want to confirm it just run a sql_trace on each version of the count query and compare - just make sure you run the query a few times to account for caching.
Previous Topic: archive logs filtering
Next Topic: TRANSLATE
Goto Forum:
  


Current Time: Thu Sep 29 09:01:38 CDT 2016

Total time taken to generate the page: 0.06295 seconds