This content has been marked as final.
Show 15 replies
-
1. Re: Difference between count(*) & count(1)
Frank Kulash Jun 2, 2010 3:13 PM (in response to 755914)Hi,
There's no difference between COUNT (*) and COUNT (1). They return the same results, and are equally efficient. -
2. Re: Difference between count(*) & count(1)
Centinul Jun 2, 2010 3:14 PM (in response to 755914)This has been talked about many, many times. There is no difference. See link:1 person found this helpful
difference between count(1) and count(*)
Select Count(1): How it works -
3. Re: Difference between count(*) & count(1)
755914 Jun 2, 2010 3:16 PM (in response to Frank Kulash)Thanks 4 reply.Don't mind but can u give some facts to prove this. As i came across an article saying
"count(1) ..
This is the fastest as the numberical literal is the fastest to be summed up.
Max rownum will lead to implicit ordering while count(id) will also be effecient as the value is a
primary key though not as good as count(1). " -
4. Re: Difference between count(*) & count(1)
Centinul Jun 2, 2010 3:19 PM (in response to 755914)1 person found this helpfulAtishay wrote:
Did you read the links I posted? It presents test cases showing performance comparisons.
Thanks 4 reply.Don't mind but can u give some facts to prove this. -
5. Re: Difference between count(*) & count(1)
ajallen Jun 2, 2010 3:29 PM (in response to 755914)<tic>
Actually, there is a very significant difference.
count(1) is used by those who are new to the business and are relying on a couple of articles they read, or lectures they heard, that are based on unsupported hypotheses.
count(*) is use by those who have been in the business long enough to know better.
Therefore, count(1) tells me I am dealing with an inexperienced developer, while count(*) tell me I am dealing with someone with a bit of experience - may be even a programmer.
</tic> -
6. Re: Difference between count(*) & count(1)
755914 Jun 2, 2010 3:31 PM (in response to Centinul)Thanks,
I got , but i was reading the same document and there itself i found that comment regarding count(1).
Edited by: Atishay on Jun 2, 2010 9:01 PM -
7. Re: Difference between count(*) & count(1)
Hoek Jun 2, 2010 3:46 PM (in response to 755914)http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1156151916789#25335122556076 ;) -
8. Re: Difference between count(*) & count(1)
Frank Kulash Jun 2, 2010 4:40 PM (in response to 755914)Hi,Atishay wrote:
Be cautious with any source that says "numberical". Even if it's an error of a translator or editor, the same translator or editor could make other mistakes.
Thanks 4 reply.Don't mind but can u give some facts to prove this. As i came across an article saying
"count(1) ..
This is the fastest as the numberical literal is the fastest to be summed up.
Summing has nothing to do with the case. You'll get the same results, with the same efficiency, if you use any non-NULL literal, such as COUNT (0) or COUNT ('X'). (There might be really tiny diffedrences in compile time if you use a complicated literal, like COUNT (1.23E45).)Max rownum will lead to implicit ordering while count(id) will also be effecient as the value is a
This source seems to be saying that COUNT (1) is faster than COUNT (column_name), or faster than other methods that get the same results without using COUNT at all. That's true, but not what you asked.
primary key though not as good as count(1). " -
9. Re: Difference between count(*) & count(1)
damorgan Jun 2, 2010 4:37 PM (in response to Frank Kulash)This last point is especially relevant when considering something such as the following:
Counting not null columns, with indexes, allows Oracle to count index entries and skip the table entirely: Often the fastest route.CREATE TABLE t ( col1 VARCHAR2(20) NOT NULL, col2 VARCHAR2(20)); ALTER TABLE t ADD CONSTRAINT pk_t PRIMARY KEY (col1);
But again ... not what was asked in the original post. -
10. Re: Difference between count(*) & count(1)
Rafu Jun 2, 2010 9:23 PM (in response to damorgan)11g 10053 trace Query transformations
For a count(col) on a not null column the optimized may convert the query to count(*). It not saying that is considering to use count(1). Actually for count(1) the optimizer will do a query transformation.
Try out yourself
For count(1) transformation is donealter session set events='10053'; select count(1) from dual; select count(*) from dual; alter session set events='10053 off';
For count(*) transformation not neededCNT: Considering count(col) to count(*) on query block SEL$1 (#0) ************************* Count(col) to Count(*) (CNT) ************************* CNT: COUNT() to COUNT(*) done.
CNT: Considering count(col) to count(*) on query block SEL$1 (#0) ************************* Count(col) to Count(*) (CNT) ************************* CNT: COUNT() to COUNT(*) not done.
-
11. Re: Difference between count(*) & count(1)
Billy~Verreynne Jun 3, 2010 6:56 AM (in response to 755914)Atishay wrote:
Seems like some code (perhaps older code) in Oracle is also confused - I have seen nested system SQLs generated by Oracle that also uses "+count(1)+".
I got , but i was reading the same document and there itself i found that comment regarding count(1).
In fact, every now and then one should dump the entire shared pool contents (source only) and see what's there. It's like a mini easter egg hunt. Never know just what you'll find.. especially from Oracle generated SQLs. Just yesterday I ran across a series of nested SQLs - not using bind variable for selecting a value from the virtual parameter table/view. Each SQL identical - except the for the predicate where the parameter name is matched to a literal, instead of using a bind variable with that literal value. And this in 11gR2 too... -
12. Re: Difference between count(*) & count(1)
Rob van Wijk Jun 3, 2010 7:15 AM (in response to Rafu)Rafu wrote:
Thanks Rafu.
alter session set events='10053';
I've read far too many discussions about this topic, but I never realised it could be seen so clear in a 10053 trace.
The ultimate proof, I'd say.
Regards,
Rob. -
13. Re: Difference between count(*) & count(1)
BluShadow Jun 3, 2010 10:01 AM (in response to Rob van Wijk)Rob van Wijk wrote:
Padders showed something similar about 3 years ago...
I've read far too many discussions about this topic, but I never realised it could be seen so clear in a 10053 trace.
The ultimate proof, I'd say.
Re: Count(*)/Count(1) -
14. Re: Difference between count(*) & count(1)
Rob van Wijk Jun 3, 2010 10:24 AM (in response to BluShadow)BluShadow wrote:
Hi Blu,
Padders showed something similar about 3 years ago...
Thanks for the link. I forgot about it, but now I remember. It is indeed very impressive, but the evidence is a bit more indirect than Rafu's. I'd rather point to the 10053 output in future questions because it is a little more obvious.
Regards,
Rob.