Tuesday, July 27, 2010

Oracle Count(1) vs Count(*)

Well, it might have been an everlasting discussion about which one of these to use, count(1) or count(*).

I guess, this article of Thomas Kyte already clarified the situation long long ago (well, for IT industry 2005 is long ago anyway, especially given the speed at which we are moving.)

Essentially, what askTom says that, count(*) is better than count(1) since count(1) translates to count(*) internally anyway. I wonder then, why would someone want to use count(1) anyway.

There is at least one more step involved in getting to the actual result. And there is another possible tweak, count(1) has to evaluate an expression as well, "count(1) where 1 is not null". Though its a tautology equivalent, it has to be evaluated nonetheless.

Further, there was some misconception about how the result is returned, whether its read from the data dictionary, this view or table or something like that. I dont think so. The result is calculated at the exact run time,when the query is run, and it actually goes ahead and counts the records in the table.

Should set the record straight...

in reference to: Ask Tom "COUNT(*) Vs COUNT(1) on tables with CLOB..." (view on Google Sidewiki)

No comments:

Post a Comment