Why select count(*) is faster than select count(1)?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Why select count(*) is faster than select count(1)?

  1. #1
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    Why select count(*) is faster than select count(1)?

    I thought count(*) will take longer as oracle need to find out all the column names from dictionary ...
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  2. #2
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    COUNT(*) is faster than COUNT(1), If the rows are being returned via an index, counting the indexed column – for example, COUNT(EMPNO) is faster still.

    Sameer

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    they are same

  4. #4
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    counting via the index is only useful if column is NOT NULL. Null values won't be indexed.

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Code:
    SQL> analyze table emp compute statistics;
    
    Table analyzed.
    
    SQL> set autotrace traceonly exp stat
    SQL> select count(*) from emp;
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
       1    0   SORT (AGGREGATE)
       2    1     INDEX (FULL SCAN) OF 'EMP_PK' (UNIQUE) (Cost=1 Card=14)
    
    
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              1  consistent gets
              0  physical reads
              0  redo size
            379  bytes sent via SQL*Net to client
            499  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL> select count(1) from emp;
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
       1    0   SORT (AGGREGATE)
       2    1     INDEX (FULL SCAN) OF 'EMP_PK' (UNIQUE) (Cost=1 Card=14)
    
    
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              1  consistent gets
              0  physical reads
              0  redo size
            379  bytes sent via SQL*Net to client
            499  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    no diffrence

  6. #6
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    Maybe you try the RBO ? ( delete the statistics )
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  7. #7
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    It looks same even after deleting statistics.

    Sanjay

    Code:
    fdbp1@FDBSD> analyze table rro compute statistics;
    
    Table analyzed.
    
    fdbp1@FDBSD> set autotrace on
    fdbp1@FDBSD> select count(*) from rro;
    
      COUNT(*)
    ----------
         19752
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=69 Card=1)
       1    0   SORT (AGGREGATE)
       2    1     TABLE ACCESS (FULL) OF 'RRO' (Cost=69 Card=19752)
    
    
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              3  db block gets
            449  consistent gets
              0  physical reads
              0  redo size
            181  bytes sent via SQL*Net to client
            205  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    fdbp1@FDBSD> select count(1) from rro;
    
      COUNT(1)
    ----------
         19752
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=69 Card=1)
       1    0   SORT (AGGREGATE)
       2    1     TABLE ACCESS (FULL) OF 'RRO' (Cost=69 Card=19752)
    
    
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              3  db block gets
            449  consistent gets
              0  physical reads
              0  redo size
            181  bytes sent via SQL*Net to client
            205  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    fdbp1@FDBSD> analyze table rro delete statistics;
    
    Table analyzed.
    
    fdbp1@FDBSD> select count(*) from rro;
    
      COUNT(*)
    ----------
         19752
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE
       1    0   SORT (AGGREGATE)
       2    1     TABLE ACCESS (FULL) OF 'RRO'
    
    
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              3  db block gets
            449  consistent gets
              0  physical reads
              0  redo size
            181  bytes sent via SQL*Net to client
            205  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    fdbp1@FDBSD> 
    fdbp1@FDBSD> select count(1) from rro;
    
      COUNT(1)
    ----------
         19752
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE
       1    0   SORT (AGGREGATE)
       2    1     TABLE ACCESS (FULL) OF 'RRO'
    
    
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              3  db block gets
            449  consistent gets
              0  physical reads
              0  redo size
            181  bytes sent via SQL*Net to client
            205  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    fdbp1@FDBSD>

  8. #8
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,591
    Bottom line: one is a mere disguise of the other.
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width