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

**ligang** · 08-27-2002, 06:00 AM

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 ...

**Sameer** · 08-27-2002, 06:04 AM

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

**pando** · 08-27-2002, 06:43 AM

they are same

**JMac** · 08-27-2002, 07:18 AM

counting via the index is only useful if column is NOT NULL. Null values won't be indexed.

**pando** · 08-27-2002, 07:32 AM

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

**ligang** · 08-27-2002, 09:01 PM

Maybe you try the RBO ? ( delete the statistics )

**SANJAY_G** · 08-27-2002, 11:09 PM

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>

**Tarry** · 08-28-2002, 08:22 AM

Bottom line: one is a mere disguise of the other.

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

Thread Tools

Display

Posting Permissions