1 2 3 Previous Next 34 Replies Latest reply: Jul 29, 2007 5:49 AM by 584983 RSS

    Count(*)/Count(1)

    535627
      What is the difference between count(*) and count(1)?
      Which is prefered?
      Please advise.

      Thanks..
        • 1. Re: Count(*)/Count(1)
          amardeep.sidhu
          There is no difference as Tom Kyte shows. Check out

          http://asktom.oracle.com/pls/asktom/f?p=100:11:2348117919541151::::P11_QUESTION_ID:1156159920245

          Sidhu
          http://amardeepsidhu.blogspot.com
          • 2. Re: Count(*)/Count(1)
            Billy~Verreynne
            > What is the difference between count(*) and count(1)?

            You write them differently. One with an asterisk, the other with the digit one.

            > Which is prefered?

            The former as it is the syntax commonly used in SQL literature and guides and manuals. The latter does not provide any performance enhancements on current Oracle versions.
            • 3. Re: Count(*)/Count(1)
              49084
              SELECT count(ROWID) is the fastest. :D





              (just to stir things up a little).
              • 4. Re: Count(*)/Count(1)
                amardeep.sidhu
                Prove it :P

                Sidhu
                http://amardeepsidhu.blogspot.com
                • 5. Re: Count(1) is faster
                  584983
                  SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 14 18:21:14 2007

                  Copyright (c) 1982, 2005, Oracle. All rights reserved.

                  SQL> conn sys as sysdba
                  Enter password: *****
                  Connected to an idle instance.
                  SQL> startup
                  ORACLE instance started.

                  Total System Global Area 167772160 bytes
                  Fixed Size 1247900 bytes
                  Variable Size 92276068 bytes
                  Database Buffers 71303168 bytes
                  Redo Buffers 2945024 bytes
                  Database mounted.
                  Database opened.
                  SQL> conn scott/tiger@ocp10g ;
                  Connected.
                  SQL> set lines 10000
                  SQL> set autotrace on
                  SQL> set timing on
                  SQL> select count(*) from dept1;


                  COUNT(*)
                  ----------
                  8388608

                  Elapsed: 00:07:00.79

                  Execution Plan
                  ----------------------------------------------------------
                  Plan hash value: 1828124803

                  --------------------------------------------------------------------
                  | Id | Operation | Name | Rows | Cost (%CPU)| Time |
                  --------------------------------------------------------------------
                  | 0 | SELECT STATEMENT | | 1 | 1801 (4)| 00:00:22 |
                  | 1 | SORT AGGREGATE | | 1 | | |
                  | 2 | TABLE ACCESS FULL| DEPT1 | 2107K| 1801 (4)| 00:00:22 |
                  --------------------------------------------------------------------


                  Statistics
                  ----------------------------------------------------------
                  208 recursive calls
                  0 db block gets
                  31678 consistent gets
                  31644 physical reads
                  0 redo size
                  414 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

                  SQL> conn sys/shree@ocp10g as sysdba ;
                  Connected.
                  SQL> shutdown immediate ;
                  Database closed.
                  Database dismounted.
                  ORACLE instance shut down.
                  SQL> conn / as sysdba ;
                  Connected to an idle instance.
                  SQL> startup;
                  ORACLE instance started.

                  Total System Global Area 167772160 bytes
                  Fixed Size 1247900 bytes
                  Variable Size 88081764 bytes
                  Database Buffers 75497472 bytes
                  Redo Buffers 2945024 bytes
                  Database mounted.
                  Database opened.
                  SQL> conn scott/tiger@ocp10g ;
                  Connected.
                  SQL> set lines 10000
                  SQL> set autotrace on
                  SQL> set timing on
                  SQL> select count(1) from dept1;

                  COUNT(1)
                  ----------
                  8388608

                  Elapsed: 00:04:26.39

                  Execution Plan
                  ----------------------------------------------------------
                  Plan hash value: 1828124803

                  --------------------------------------------------------------------
                  | Id | Operation | Name | Rows | Cost (%CPU)| Time |
                  --------------------------------------------------------------------
                  | 0 | SELECT STATEMENT | | 1 | 1801 (4)| 00:00:22 |
                  | 1 | SORT AGGREGATE | | 1 | | |
                  | 2 | TABLE ACCESS FULL| DEPT1 | 2107K| 1801 (4)| 00:00:22 |
                  --------------------------------------------------------------------


                  Statistics
                  ----------------------------------------------------------
                  212 recursive calls
                  0 db block gets
                  31678 consistent gets
                  31645 physical reads
                  0 redo size
                  414 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



                  To clear caches I have restarted the database each time while doing the queries.
                  • 6. Re: Count(*)/Count(1)
                    577207
                    • 7. Re: Count(1) is faster
                      APC
                      To clear caches I have restarted the database each time while doing the queries.
                      You could just have
                      alter system flush SHARED_POOL
                      /
                      Cheers, APC
                      • 8. Re: Count(1) is faster
                        94799
                        To clear caches I have restarted the database each time while doing the queries.
                        You posted this on AskTom yesterday.

                        He pointed out that your test case is flawed and indicated why.

                        So you blatantly ignore him and post it here anyway.

                        Thanks for that.
                        • 9. Re: Count(1) is faster
                          584983
                          Yes, but I wanted a test with no doubts at all.
                          • 10. Re: Count(1) is faster
                            584983
                            I have replied to Tom also ....Please check.
                            • 11. Re: Count(1) is faster
                              94799
                              Then I suggest you need to read Tom's answer again.

                              Did you actually try your test in reverse order as he suggested?
                              • 12. Re: Count(1) is faster
                                49084
                                I have replied to Tom also ....Please check.
                                Yes, but you didn't do what he asked: reverse the order in which you run the queries.
                                • 13. Re: Count(1) is faster
                                  APC
                                  I have replied to Tom also ....Please check.
                                  No, you check. Tom suggested you...

                                  " run the count(*) after the count(1) and then ask yourself what effect the file system buffer cache might possibly be having here..."

                                  Have you done that?

                                  Look, Tom Kyte isn't right all the time but it's certainly the way to bet.

                                  Cheers, APC
                                  • 14. Re: Count(*)/Count(1)
                                    584983
                                    As you can see I have restarted the database while executing query each time .Where does the question of system cache arise.It will be fresh.Please clear my doubt.Question of reversing the queries may be opted only if I would have not restarted the database each time.

                                    I think Tom has asked me to reverse the order because I think he had not seen that the I have restarted the database.That SQL I have pasted is quiet big.
                                    1 2 3 Previous Next