Difference between count(1) and count(*) in oracle

I believe count(1) used to be faster in older versions of Oracle. But by now, I'm pretty sure the optimizer is smart enough to know that count(*) and count(1) mean you want the number of rows and creates an appropriate execution plan.

Here you go:

create table t as select * from all_objects;

Table T created.

create index tindx on t( object_name );

Index TINDX created.

select count(*) from t;

  COUNT(*)
----------
     21534

select * from table(dbms_xplan.display_cursor( NULL, NULL, 'allstats last' ));

Plan hash value: 2940353011

--------------------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |      1 |        |      1 |00:00:00.01 |     100 |     93 |
|   1 |  SORT AGGREGATE       |       |      1 |      1 |      1 |00:00:00.01 |     100 |     93 |
|   2 |   INDEX FAST FULL SCAN| TINDX |      1 |  18459 |  21534 |00:00:00.01 |     100 |     93 |
--------------------------------------------------------------------------------------------------

select count(1) from t;

  COUNT(1)
----------
     21534

Plan hash value: 2940353011

-----------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |      1 |        |      1 |00:00:00.01 |     100 |
|   1 |  SORT AGGREGATE       |       |      1 |      1 |      1 |00:00:00.01 |     100 |
|   2 |   INDEX FAST FULL SCAN| TINDX |      1 |  18459 |  21534 |00:00:00.01 |     100 |
-----------------------------------------------------------------------------------------

So not only is it smart enough to know it can use the index to optimize this query, but it uses the exact same execution plan for the different versions (the plan has value is the same).

I think the origin of this rumour is the assumption that the database must internally expand * into the full column list, and so substituting some literal like count('Dracula') cleverly avoids this overhead. But it does not, and it never has. I first heard this around Oracle 6 in 1990 (another variation was that you should use the PK column), and it was not true then.

One of the standard proofs (apart from checking whether it actually makes any difference to performance, which it won't) is to check the 'predicates' section of an execution plan where count(1) is used in a predicate:

SQL> select dummy from dual group by dummy having count(1) = 1

SQL> @xplan
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Compatibility is set to 12.2.0.0.0

Plan hash value: 795860295

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |     2 |     3  (34)| 00:00:01 |
|*  1 |  FILTER               |      |       |       |            |          |
|   2 |   SORT GROUP BY NOSORT|      |     1 |     2 |     3  (34)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | DUAL |     1 |     2 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - filter(COUNT(*)=1)

Notice that the optimiser has substituted COUNT(*).

All count(1) does is give you more keystrokes to type and make you look like someone who believes rumours.

I've tested two simple queries calculating COUNT(*) or COUNT(1) on 1M rows in Oracle and a few other RDBMS, and haven't found any differences in Oracle. See this post for details.

However, in PostgreSQL 11.3, it seems that COUNT(1) is about 10% slower, because PostgreSQL doesn't transform the expression to COUNT(*), and thus checks for nullability on each row when running COUNT(1).