Difference b/w COUNT(*) and COUNT(1)

If you specify expr in COUNT(expr), COUNT returns the number of rows where expr is not null. You
can count either all rows, or only distinct values of expr.

I think in this case under 1 SQL understands column 1 in your table, so it will return the count of the records where the value of the first column is not null.

If you specify the asterisk (*) like COUNT(*), this function returns all rows, including duplicates and nulls. COUNT never returns null.

Additionally, I was informed recently that if you use COUNT(rowid) that you'll get the same results as COUNT(*) however the result will be far quicker as it is looking at a single indexed column.

Regards,

JT

Hi Raghav,
using an index (e.g. SELECT COUNT(customer_id) FROM customers;) to count the rows in a table has some merit: The index on a non-null column will be smaller than the table.

The result between the use of COUNT(*) and COUNT(0) is the same.
But with the COUNT(*) method no rows are actually procesessed by the SORT shown in the execution plan. This is a "spezial optimization": Oracle will avoid a sort when you specify count(*).

Regards
Tinnus

nothing, they are the same, incur the same amount of work -- do the same thing, take the same amount of resources.


You can see this via:

SQL> alter session set sql_trace=true;

Session altered.

SQL> select count(*) from all_objects;

  COUNT(*)
----------
     27044

SQL> select count(1) from all_objects
  2  /

  COUNT(1)
----------
     27044


and the tkprof will show:



select count(*) from all_objects


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      5.56       5.56          0     234998          4           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      5.58       5.58          0     234998          4           1

select count(1) from all_objects


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      5.46       5.47          0     234998          4           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      5.48       5.49          0     234998          4           1

Same number of blocks read/written/processed, same cpu times (basically) same elapsed times (basically).

they are identical.

Anyone who thinks different (and I know you are out there) will have to post a test case like the above or some scientific proof otherwise to be taken seriously....

-- this is not mine, but from oracle expert person

think,
Ranjith

Not satisfactory.

More relevant answer