We help IT Professionals succeed at work.

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

raghav3
raghav3 asked
on
Hi experts,
I want to know the difference between count(*) and Count(1) in oracle.
Pls reply back soon.
Thanks
Raghav
Comment
Watch Question

BRONZE EXPERT
Top Expert 2008

Commented:
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.
jtriftsMI and Automation
BRONZE EXPERT

Commented:
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

Commented:
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
BRONZE EXPERT
Commented:
This question has been debated for many years.

Oracle says they are SAME, if CBO is enable and at least one of the objects of the select statement has
been analyzed. [Note: It should be Oracle 8.0 and above. Oracle7 may behave different.]

See "How the Oracle CBO Chooses a Path for the SELECT
         COUNT(*) Command"
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=1247

Oracle AskTom also says they are same.
See
"difference between count(1) and count(*)"
http://asktom.oracle.com/pls/ask/f?p=4950:8:180623::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1156159920245,%7Bcount%7D%20and%20%7B1%7D

Author

Commented:
Not satisfactory.

Author

Commented:
More relevant answer

Explore More ContentExplore courses, solutions, and other research materials related to this topic.