COUNT(*) Vs. COUNT(1)

The debat COUNT(*) vs. COUNT(1) is on for years now. So I thought of doing experiment on the same and here are the result:

I have done the test for indexed table as well as non-indexed table. The table has 6 millions of rows in both the tables and the rowwidth is approx 4000 bytes.

1. COUNT(1) is internally converted to COUNT(*).

SQL> EXPLAIN PLAN FOR
2 select max(REG_COUNT) from f_reg
3 group by MG_BKEY
4 having COUNT(1) > 1;

Explained.

SQL> SET LINESIZE 130
SET PAGESIZE 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY);
SQL> SQL> Plan hash value: 272420531

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 50738 (5)| 00:10:09 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 1 | 6 | 50738 (5)| 00:10:09 |
| 3 | TABLE ACCESS FULL| F_REG | 6632K| 37M| 49776 (4)| 00:09:58 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(COUNT(*)>1)

15 rows selected.

The DBMS_XPLAN gives you the plan and predicates used by oracle to execute a query. You can see above that the filter "COUNT(1) > 1" is converted to "COUNT(*) > 1".

2. There is no difference in COUNT(*) and COUNT(1).

Case a: Test on indexed table

set autotrace traceonly
select count(1) from f_reg;

Execution Plan
----------------------------------------------------------
Plan hash value: 622071604

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4429 (3)| 00:00:54 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| F_REG_PK | 6632K| 4429 (3)| 00:00:54 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
19808 consistent gets
0 physical reads
0 redo size
414 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


select count(*) from f_reg;

Execution Plan
----------------------------------------------------------
Plan hash value: 622071604

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4429 (3)| 00:00:54 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| F_REG_PK | 6632K| 4429 (3)| 00:00:54 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
19808 consistent gets
0 physical reads
0 redo size
414 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


Case b: Test on non-indexed table

set autotrace traceonly
select count(1) from f_reg_1_bkup;

Execution Plan
----------------------------------------------------------
Plan hash value: 2458129084

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48989 (1)| 00:09:48 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| F_REG_1_BKUP | 6632K| 48989 (1)| 00:09:48 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
330 recursive calls
0 db block gets
221666 consistent gets
176232 physical reads
0 redo size
414 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed



select count(*) from f_reg_1_bkup;

Execution Plan
----------------------------------------------------------
Plan hash value: 2458129084

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48989 (1)| 00:09:48 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| F_REG_1_BKUP | 6632K| 48989 (1)| 00:09:48 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
221585 consistent gets
175908 physical reads
0 redo size
414 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

You can see, there is no difference at all in statistics or timings in case of indexed table, whereas in case of non-indexed table there is a slight difference in consistenet gets which is less in case of COUNT(*) and no difference in elapsed time.

P.S. The tests were carried out in Oracle 10g.