OraFAQ Forum: SQL & PL/SQL » Difference between count(*) and count(1)

Home » SQL & PL/SQL » SQL & PL/SQL » Difference between count(*) and count(1) (Oracle 9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Difference between count(*) and count(1) [message #431210]

Mon, 16 November 2009 03:54

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?

Report message to a moderator

Re: Difference between count(*) and count(1) [message #431211 is a reply to message #431210]

Mon, 16 November 2009 03:59

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

Report message to a moderator

Re: Difference between count(*) and count(1) [message #431213 is a reply to message #431210]

Mon, 16 November 2009 04:05

ramoradba
Messages: 2457
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

Report message to a moderator

Re: Difference between count(*) and count(1) [message #431215 is a reply to message #431211]

Mon, 16 November 2009 04:07

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(*)

Report message to a moderator

Re: Difference between count(*) and count(1) [message #431218 is a reply to message #431210]

Mon, 16 November 2009 04:11

kish_ulike
Messages: 30
Registered: June 2008
Location: chennai

Member

Thanks!!!!!!

Report message to a moderator

Re: Difference between count(*) and count(1) [message #431267 is a reply to message #431218]

Mon, 16 November 2009 06:54

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

Report message to a moderator

Re: Difference between count(*) and count(1) [message #431269 is a reply to message #431267]

Mon, 16 November 2009 06:56

pablolee
Messages: 2882
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?

Report message to a moderator

Re: Difference between count(*) and count(1) [message #431275 is a reply to message #431269]

Mon, 16 November 2009 07:09

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

Report message to a moderator

Re: Difference between count(*) and count(1) [message #431276 is a reply to message #431269]

Mon, 16 November 2009 07:22

ThomasG
Messages: 3212
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

Report message to a moderator

Re: Difference between count(*) and count(1) [message #431279 is a reply to message #431276]

Mon, 16 November 2009 07:33

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Quality.

Report message to a moderator

Re: Difference between count(*) and count(1) [message #497133 is a reply to message #431279]

Fri, 04 March 2011 02:10

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)

Report message to a moderator

Re: Difference between count(*) and count(1) [message #497136 is a reply to message #497133]

Fri, 04 March 2011 02:26

Michel Cadot
Messages: 68719
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Same.

Regards
Michel

Report message to a moderator

Re: Difference between count(*) and count(1) [message #497157 is a reply to message #497136]

Fri, 04 March 2011 03:42

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

Report message to a moderator

Re: Difference between count(*) and count(1) [message #497160 is a reply to message #497157]

Fri, 04 March 2011 03:49

cookiemonster
Messages: 13960
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.

Report message to a moderator

Previous Topic:	archive logs filtering
Next Topic:	TRANSLATE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 08 19:30:30 CST 2024