This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Jun 30, 2007 1:01 AM by William Robertson RSS

differenct between count(0), count(1) and count(*)...???

544878 Newbie
Currently Being Moderated
Hi,

Please clarify the difference b/w count(0), count(1) and count(*)...???.

SQL> set timing on
SQL> select count(0) from trade
2 /

COUNT(0)
----------
112158506

Elapsed: 00:00:03.08
SQL> ed
Wrote file afiedt.buf

1* select count(1) from trade
SQL> /

COUNT(1)
----------
112158506

Elapsed: 00:00:02.01
SQL> ed
Wrote file afiedt.buf

1* select count(*) from trade
SQL> /

COUNT(*)
----------
112158506

Elapsed: 00:00:02.03
SQL>

Is there any differences??

Thanks
SATHYA
  • 1. Re: differenct between count(0), count(1) and count(*)...???
    ushitaki Newbie
    Currently Being Moderated
    count(*) is counting up all of hitting.

    count(expression) is counting up not null of expression.
    0 is not null, 1 is not null.
    So, we can get same results.

    If you had not special reasion, you had better use count(*).


    Additional examples
    SQL> select count(*) from scott.emp;
    
      COUNT(*)
    ----------
            13
    
    SQL> select count(mgr) from scott.emp;
    
    COUNT(MGR)
    ----------
            12
    
    SQL> select count(null) from scott.emp;
    
    COUNT(NULL)
    -----------
              0
    
    SQL> select count(0) from scott.emp;
    
      COUNT(0)
    ----------
            13
    
    SQL> select count(distinct mgr) from scott.emp;
    
    COUNT(DISTINCTMGR)
    ------------------
                     5
    
    SQL> select count(distinct 0) from scott.emp;
    
    COUNT(DISTINCT0)
    ----------------
                   1
    
    SQL> 
    SQL> select count(distinct *) from scott.emp;
    select count(distinct *) from scott.emp
                          *
    ERROR at line 1:
    ORA-00936: missing expression
    Message was edited by:
    ushitaki
  • 2. Re: differenct between count(0), count(1) and count(*)...???
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    This is a myth...
    Better to run
    select count('FAST') from your_table;
    ;-)

    Nicolas.
  • 3. Re: differenct between count(0), count(1) and count(*)...???
    NicloeiW Journeyer
    Currently Being Moderated
    but it really works
  • 4. Re: differenct between count(0), count(1) and count(*)...???
    14728 Explorer
    Currently Being Moderated
    It will run faster on subsequent runs due to data being held in the buffers...
  • 5. Re: differenct between count(0), count(1) and count(*)...???
    581608 Newbie
    Currently Being Moderated
    hi,

    Count(*) will try to access all DB blocks of the retrieved data before returning the Count.

    Count(<Literal>) will not do so. Hence it is faster than count(*).

    Regards
    K.Rajkumar
  • 6. Re: differenct between count(0), count(1) and count(*)...???
    SamB Newbie
    Currently Being Moderated
    Looks the same to me
    admin@10gR2> create table big_table as select * from all_objects;

    Table created.

    admin@10gR2> set autotrace traceonly
    admin@10gR2> alter system flush shared_pool
      2  /

    System altered.

    admin@10gR2> select count(1) from big_table;


    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 599409829

    ------------------------------------------------------------------------
    | Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |           |     1 |   185   (2)| 00:00:03 |
    |   1 |  SORT AGGREGATE    |           |     1 |            |          |
    |   2 |   TABLE ACCESS FULL| BIG_TABLE | 72970 |   185   (2)| 00:00:03 |
    ------------------------------------------------------------------------

    Note
    -----
       - dynamic sampling used for this statement


    Statistics
    -----------------------------------------------------
            322  recursive calls
              0  db block gets
            947  consistent gets
              0  physical reads
              0  redo size
            413  bytes sent via SQL*Net to client
            381  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              4  sorts (memory)
              0  sorts (disk)
              1  rows processed

    admin@10gR2> alter system flush shared_pool
      2  /

    System altered.

    admin@10gR2> select count(*) from big_table;


    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 599409829

    ------------------------------------------------------------------------
    | Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |           |     1 |   185   (2)| 00:00:03 |
    |   1 |  SORT AGGREGATE    |           |     1 |            |          |
    |   2 |   TABLE ACCESS FULL| BIG_TABLE | 72970 |   185   (2)| 00:00:03 |
    ------------------------------------------------------------------------

    Note
    -----
       - dynamic sampling used for this statement


    Statistics
    ----------------------------------------------------------
            322  recursive calls
              0  db block gets
            947  consistent gets
              0  physical reads
              0  redo size
            413  bytes sent via SQL*Net to client
            381  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              4  sorts (memory)
              0  sorts (disk)
              1  rows processed
  • 7. Re: differenct between count(0), count(1) and count(*)...???
    576951 Newbie
    Currently Being Moderated
    There is to be true no difference ...
  • 8. Re: differenct between count(0), count(1) and count(*)...???
    544878 Newbie
    Currently Being Moderated
    any other differences?

    For example:
    count(1) - it will take the primay key and then count the records. I don't know this is true, but i am saying anything like this?
  • 9. Re: differenct between count(0), count(1) and count(*)...???
    Satyaki_De Guru
    Currently Being Moderated
    Nicolas, i recieve this output - what might be the reason according to you --
    satyaki>set timi on
    
    satyaki>
    satyaki>select count(*) from psgr;
    
      COUNT(*)
    ----------
          6153
    
    Elapsed: 00:00:03.09
    satyaki>
    satyaki>
    satyaki>select count(0) from psgr;
    
      COUNT(0)
    ----------
          6153
    
    Elapsed: 00:00:07.07
    satyaki>
    satyaki>
    satyaki>select count(1) from psgr;
    
      COUNT(1)
    ----------
          6153
    
    Elapsed: 00:00:00.07
    satyaki>
    satyaki>
    satyaki>select count('FAST') from psgr;
    
    COUNT('FAST')
    -------------
             6153
    
    Elapsed: 00:00:02.08
    satyaki>
    satyaki>
    satyaki>
    
    satyaki>
    satyaki>select count('X') from psgr;
    
    COUNT('X')
    ----------
          6153
    
    Elapsed: 00:00:01.00
    
    satyaki>select count('FAS') from psgr;
    
    COUNT('FAS')
    ------------
            6153
    
    Elapsed: 00:00:00.08
    I'll be waiting for your reply. And, also input by others.

    Regards.

    Satyaki De.
  • 10. Re: differenct between count(0), count(1) and count(*)...???
    60660 Journeyer
    Currently Being Moderated
    Count(*) will try to access all DB blocks of the
    retrieved data before returning the Count.

    Count(<Literal>) will not do so. Hence it is faster
    than count(*).
    Any chance of getting additional information that'll back your statement?
    Otherwise it does sound like complete und utter bollocks to me.

    C.
  • 11. Re: differenct between count(0), count(1) and count(*)...???
    Satyaki_De Guru
    Currently Being Moderated
    As i've tried to check it in this way but receive error --
    satyaki>
    satyaki>alter system flush shared_pool
      2  /
    alter system flush shared_pool
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    
    
    Elapsed: 00:00:00.07
    satyaki>
    Because, i don't have sufficient privs to check that. So, i've to rely on all of you.

    Regards.

    Satyaki De.
  • 12. Re: differenct between count(0), count(1) and count(*)...???
    Satyaki_De Guru
    Currently Being Moderated
    Initially, from your post it seems that both are taking same time. Then why is it behabing such a strange way? Any explanation?

    Regards.

    Satyaki De.
  • 13. Re: differenct between count(0), count(1) and count(*)...???
    60660 Journeyer
    Currently Being Moderated
    Have you tried SELECT Count(*) twice in a row for example?

    C.
  • 14. Re: differenct between count(0), count(1) and count(*)...???
    BluShadow Guru Moderator
    Currently Being Moderated
    Count(*) will try to access all DB blocks of the
    retrieved data before returning the Count.

    Count(<Literal>) will not do so. Hence it is
    faster
    than count(*).
    Any chance of getting additional information that'll
    back your statement?
    Otherwise it does sound like complete und utter
    bollocks to me.

    C.
    Exactly my thoughts cd.

    How can counting using a literal actually count all the rows without actually accessing all the rows in all the data blocks?

    This has been answered so many times before that I can't actually be bothered to search out the links on this forum and AskTom that prove there is no difference.
1 2 Previous Next