Difference between count(*) & count(1)

Hi,

There's no difference between COUNT (*) and COUNT (1). They return the same results, and are equally efficient.

This has been talked about many, many times. There is no difference. See link:

difference between count(1) and count(*)
Select Count(1): How it works

Atishay wrote:
Thanks 4 reply.Don't mind but can u give some facts to prove this.

Did you read the links I posted? It presents test cases showing performance comparisons.

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

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1156151916789#25335122556076 ;)

Hi,

Atishay wrote:
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.

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.
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
primary key though not as good as count(1). "

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.

This last point is especially relevant when considering something such as the following:

CREATE TABLE t (
col1 VARCHAR2(20) NOT NULL,
col2 VARCHAR2(20));

ALTER TABLE t
ADD CONSTRAINT pk_t
PRIMARY KEY (col1);

Counting not null columns, with indexes, allows Oracle to count index entries and skip the table entirely: Often the fastest route.

But again ... not what was asked in the original post.

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

alter session set events='10053';

select count(1) from dual;

select count(*) from dual;

alter session set events='10053 off';

For count(1) transformation is done

CNT:   Considering count(col) to count(*) on query block SEL$1 (#0)
*************************
Count(col) to Count(*) (CNT)
*************************
CNT:     COUNT() to COUNT(*) done.

For count(*) transformation not needed

CNT:   Considering count(col) to count(*) on query block SEL$1 (#0)
*************************
Count(col) to Count(*) (CNT)
*************************
CNT:     COUNT() to COUNT(*) not done.

Atishay wrote:

I got , but i was reading the same document and there itself i found that comment regarding count(1).

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

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

Rafu wrote:
alter session set events='10053';

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

Rob van Wijk wrote:
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.

Padders showed something similar about 3 years ago...

Re: Count(*)/Count(1)

BluShadow wrote:
Padders showed something similar about 3 years ago...

Hi Blu,

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.