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 ...
Printable View
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 ...
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
they are same
counting via the index is only useful if column is NOT NULL. Null values won't be indexed.
no diffrenceCode:
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
Maybe you try the RBO ? ( delete the statistics )
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>
Bottom line: one is a mere disguise of the other.