What is the difference between count (*) and count (1?

The number of records in the database table is as follows:

SQL> select count (*) from table_name t;

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

1. Statistical results using count:

SQL> alter session set nls_language = "American ";

Session altered.

SQL> set timing on;
SQL> set autotrace on;
SQL> select a.doc ument_id, count (*) from table_name a group by a.doc ument_id having count(a.doc ument_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
95)

1 0 FILTER
2 1 SORT (group by) (Cost = 90 Card = 339 Bytes = 1695)
3 2 table access (FULL) OF 'table _ name' (Cost = 78 Card = 6828
Bytes = 34140)

 

Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
837 consistent gets
0 physical reads
0 redo size
230 bytes sent via SQL * Net to client
242 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
2. Use the count (1) statistical result:

SQL> alter session set nls_language = "American ";

Session altered.

SQL> set timing on;
SQL> set autotrace on;
SQL> select a.doc ument_id, count (1) from table_name a group by a.doc ument_id having count(a.doc ument_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
95)

1 0 FILTER
2 1 SORT (group by) (Cost = 90 Card = 339 Bytes = 1695)
3 2 table access (FULL) OF 'table _ name' (Cost = 78 Card = 6828
Bytes = 34140)

 

Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
837 consistent gets
0 physical reads
0 redo size
230 bytes sent via SQL * Net to client
242 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

Actually, there is no big difference between the two! Use count (1) is more time than count!

Comparison after analysis of database tables:

3. Result of using count:

SQL> analyze table table_name compute statistics;

Table analyzed.

Elapsed: 00:00:02. 92
SQL> select a.doc ument_id, count (*) from table_name a group by a.doc ument_id having count(a.doc ument_ 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) OF 'table _ name' (Cost = 82 Card = 6873
Bytes = 27492)

 

Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
837 consistent gets
0 physical reads
0 redo size
231 bytes sent via SQL * Net to client
241 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

1 2 Next Page