How big is the difference between count (*) and COUNT (1)?

The number of records in the database table is:

Sql> Select COUNT (*) from table_name t;

COUNT (*)
----------
6873

1, using the statistical results of COUNT (*):

Sql> alter session Set Nls_language = "American";

Session altered.

Sql> set timing on;
Sql> set autotrace on;
Sql> Select A.document_id,count (*) from table_name A group by A.DOCUMENT_ID have Count (a.document_id) >1;

document_id COUNT (*)
----------- ----------
0 71

elapsed:00:00:05.20

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT optimizer=choose (cost=90 card=339 bytes=16
75g

1 0 FILTER
2 1 SORT (GROUP by) (cost=90 card=339 bytes=1695)
3 2 TABLE ACCESS (FULL) ' table_name ' (cost=78 card=6828
bytes=34140)

Statistics
----------------------------------------------------------
0 Recursive calls
0 db Block gets
837 consistent gets
0 physical Reads
0 Redo Size
230 bytes sent via sql*net to client
242 bytes received via sql*net from client
2 sql*net roundtrips To/from Client
1 Sorts (memory)
0 Sorts (disk)
1 rows processed

Sql>
2, using the statistical results of COUNT (1):

Sql> alter session Set Nls_language = "American";

Session altered.

Sql> set timing on;
Sql> set autotrace on;
Sql> Select A.document_id,count (1) from table_name a group by A.DOCUMENT_ID have Count (a.document_id) >1;

document_id COUNT (1)
----------- ----------
0 71

elapsed:00:00:05.57

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT optimizer=choose (cost=90 card=339 bytes=16
75g

1 0 FILTER
2 1 SORT (GROUP by) (cost=90 card=339 bytes=1695)
3 2 TABLE ACCESS (FULL) ' table_name ' (cost=78 card=6828
bytes=34140)

Statistics
----------------------------------------------------------
0 Recursive calls
0 db Block gets
837 consistent gets
0 physical Reads
0 Redo Size
230 bytes sent via sql*net to client
242 bytes received via sql*net from client
2 sql*net roundtrips To/from Client
1 Sorts (memory)
0 Sorts (disk)
1 rows processed

Sql>

In fact: Two is not much different. Use COUNT (1) More than COUNT (*).

Comparison of database tables after analysis:

3, using the results of COUNT (*):

sql> ANALYZE table table_name compute statistics;

Table analyzed.

elapsed:00:00:02.92
Sql> Select A.document_id,count (*) from table_name A GROUP by A.DOCUMENT_ID has count (a.document_i

document_id COUNT (*)
----------- ----------
0 71

elapsed:00:00:05.43

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT optimizer=choose (cost=93 card=341 bytes=13
64)

1 0 FILTER
2 1 SORT (GROUP by) (cost=93 card=341 bytes=1364)
3 2 TABLE ACCESS (FULL) ' table_name ' (cost=82 card=6873
bytes=27492)

Statistics
----------------------------------------------------------
0 Recursive calls
0 db Block gets
837 consistent gets
0 physical Reads
0 Redo Size
231 Bytes sent via sql*net to client
241 Bytes received via sql*net from client
2 sql*net roundtrips To/from Client
1 Sorts (memory)
0 Sorts (disk)
1 rows processed

Sql>

4, using the result of Count (1):

sql> ANALYZE table table_name compute statistics;

Table analyzed.

elapsed:00:00:02.89
Sql> Select A.document_id,count (1) from table_name a group by A.DOCUMENT_ID have Count (a.document_id) >1;

document_id COUNT (1)
----------- ----------
0 71

elapsed:00:00:04.95

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT optimizer=choose (cost=93 card=341 bytes=13
64)

1 0 FILTER
2 1 SORT (GROUP by) (cost=93 card=341 bytes=1364)
3 2 TABLE ACCESS (FULL) ' table_name ' (cost=82 card=6873
bytes=27492)

Statistics
----------------------------------------------------------
0 Recursive calls
0 db Block gets
837 consistent gets
0 physical Reads
0 Redo Size
231 Bytes sent via sql*net to client
242 bytes received via sql*net from client
2 sql*net roundtrips To/from Client
1 Sorts (memory)
0 Sorts (disk)
1 rows processed

Sql>

This shows that after analyzing a table, using Count (1) is less time-consuming than count (*).

And: Before and after the table analysis: Count (*) is more time-consuming than count (*) after parsing, and count (1) is less than count (1) after parsing, and for performance improvement, the first analysis of the table before using Count (1) saves more time.

Something: But when the amount of data in the table is larger:

Sql> Select COUNT (*) from TABLE_NAME;

COUNT (*)
----------
37054

5, using the results of COUNT (*):

Sql> alter session Set Nls_language = "American";

Session altered.

Sql> set timing on;
Sql> set autotrace on;
sql> ANALYZE table table_name compute statistics;

Table analyzed.

elapsed:00:00:28.28
Sql> Select A.document_id,count (*) from table_name A group by A.DOCUMENT_ID have Count (a.document_id) >1;

document_id COUNT (*)
----------- ----------
0 187
317994 2

elapsed:00:00:05.98

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT optimizer=choose (cost=457 card=1844 bytes=
7376)

1 0 FILTER
2 1 SORT (GROUP by) (cost=457 card=1844 bytes=7376)
3 2 TABLE ACCESS (FULL) ' table_name ' (cost=416 card=37
054 bytes=148216)

Statistics
----------------------------------------------------------
0 Recursive calls
0 db Block gets
4315 consistent gets
0 physical Reads
0 Redo Size
254 Bytes sent via sql*net to client
242 bytes received via sql*net from client
2 sql*net roundtrips To/from Client
1 Sorts (memory)
0 Sorts (disk)
2 rows processed

Sql>

6, using the result of Count (1):

Sql> alter session Set Nls_language = "American";

Session altered.

Sql> set timing on;
Sql> set autotrace on;
sql> ANALYZE table table_name compute statistics;

Table analyzed.

elapsed:00:00:26.57
Sql> Select A.document_id,count (1) from table_name a group by A.DOCUMENT_ID have Count (a.document_id) >1;

document_id COUNT (1)
----------- ----------
0 187
317994 2

elapsed:00:00:06.03

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT optimizer=choose (cost=457 card=1844 bytes=
7376)

1 0 FILTER
2 1 SORT (GROUP by) (cost=457 card=1844 bytes=7376)
3 2 TABLE ACCESS (FULL) ' table_name ' (cost=416 card=37
054 bytes=148216)

Statistics
----------------------------------------------------------
           0 Recursive calls
          0 db block gets
       4315 consistent gets
           0 Physical reads
          0 Redo size
   & nbsp;    254 Bytes sent via sql*net to client
        241 Bytes Rec Eived via sql*net from client
          2 sql*net roundtrips to/from Client
          1 sorts (memory)
           0 Sorts (disk)
          2 rows Processed

Sql>
When a table has more data, it is more time-consuming to use COUNT (1) than to use COUNT (*) when parsing a table.

From the execution plan, the effect of count (1) and COUNT (*) is the same.
However, after the table has been parsed, count (1) will be less time-consuming than count (*) (1w of data), but not much.
This is also related to the number of records in the table. If the amount of data other than 1w is done, the count (1) is more than count (*) when the table is parsed.

Also, when the amount of data reaches 10w, the use of Count (1) is slightly less than when using count (*).

If your datasheet does not have a primary key, then count (1) is faster than COUNT (*)
If you have a primary key, the primary key (the Union primary key) is also faster than COUNT (*) as a condition for count.
If your watch has only one field, then count (*) is the fastest.
COUNT (*) count (1) is compared. The primary or the data field corresponding to count (1).
If Count (1) is a clustered index, ID, that must be count (1) fast. But the difference is very small.
Because Count (*), automatically optimizes the assigned field. So there's no need to go to count (1), with Count (*), SQL will help you complete the optimization
therefore: count (1) and COUNT (*) are basically no different.

SQL tuning is primarily about reducing the number of consistent gets and physical reads.