1 2 Previous Next 22 Replies Latest reply: Jun 30, 2007 3:01 AM by William Robertson RSS

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

    544878
      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
          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
            This is a myth...
            Better to run
            select count('FAST') from your_table;
            ;-)

            Nicolas.
            • 4. Re: differenct between count(0), count(1) and count(*)...???
              14728
              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
                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
                  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
                    There is to be true no difference ...
                    • 8. Re: differenct between count(0), count(1) and count(*)...???
                      544878
                      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
                        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(*)...???
                          cd_2
                          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
                            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
                              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(*)...???
                                cd_2
                                Have you tried SELECT Count(*) twice in a row for example?

                                C.
                                • 14. Re: differenct between count(0), count(1) and count(*)...???
                                  BluShadow
                                  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