This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Jun 3, 2010 7:03 AM by TheOtherGuy RSS

Difference between count(*) & count(1)

755914 Newbie
Currently Being Moderated
hi,
I want to know the Difference between count(*) & count(1).
Which one is efficient & why.
  • 1. Re: Difference between count(*) & count(1)
    Frank Kulash Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
  • 3. Re: Difference between count(*) & count(1)
    755914 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.
  • 5. Re: Difference between count(*) & count(1)
    ajallen Pro
    Currently Being Moderated
    <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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.
  • 9. Re: Difference between count(*) & count(1)
    damorgan Oracle ACE Director
    Currently Being Moderated
    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.
  • 10. Re: Difference between count(*) & count(1)
    Rafu Journeyer
    Currently Being Moderated
    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.
  • 11. Re: Difference between count(*) & count(1)
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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...
  • 12. Re: Difference between count(*) & count(1)
    RobvanWijk Oracle ACE
    Currently Being Moderated
    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.
  • 13. Re: Difference between count(*) & count(1)
    BluShadow Guru Moderator
    Currently Being Moderated
    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)
  • 14. Re: Difference between count(*) & count(1)
    RobvanWijk Oracle ACE
    Currently Being Moderated
    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.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points