This discussion is archived
1 2 3 Previous Next 34 Replies Latest reply: Jul 29, 2007 3:49 AM by 584983 RSS

Count(*)/Count(1)

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

Thanks..
  • 1. Re: Count(*)/Count(1)
    amardeep.sidhu Pro
    Currently Being Moderated
    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)
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    > 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 Explorer
    Currently Being Moderated
    SELECT count(ROWID) is the fastest. :D





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

    Sidhu
    http://amardeepsidhu.blogspot.com
  • 5. Re: Count(1) is faster
    584983 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
  • 7. Re: Count(1) is faster
    APC Oracle ACE
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Yes, but I wanted a test with no doubts at all.
  • 10. Re: Count(1) is faster
    584983 Newbie
    Currently Being Moderated
    I have replied to Tom also ....Please check.
  • 11. Re: Count(1) is faster
    94799 Explorer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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