Cyber Drop

What is the difference between count (*) and count (1). One can argue all their life about this.

There are sites on the Internet saying,

Since the COUNT function will return the same results regardless of what NOT NULL field(s) you include as the COUNT function parameters (ie: within the brackets), you can change the syntax of the COUNT function to COUNT(1) to get better performance as the database engine will not have to fetch back the data fields. For example, based on the example above, the following syntax would result in better performance:

SELECT department, COUNT (1) as "Number of employees" FROM employees WHERE salary > 25000 GROUP BY department;

Now, the COUNT function does not need to retrieve all fields from the employees table as it had to when you used the COUNT (*) syntax. It will merely retrieve the numeric value of 1 for each record that meets your criteria.

Do you think it’s correct? No? The result to this question may also depend on the oracle version you use.

Well, I am not trying to prove anything here. Just wanted to say that please read Ask Tom’s page on COUNT (*) vs. COUNT (1) A lot of useful information out there for you to learn and also to think about. There was a user saying,

Hi Tom,

There is another opinion in:

OCP Introduction to Oracle 9i: SQL Exam Guide, p. 124

"Do not use count (*) to determine the number of rows in a table. Use count (1) or count (ROWID) instead. These options are faster because they bypass some unnecessary operations in Oracle's SQL processing mechanism."

And the reply to it was…

OPINIONS DON'T COUNT

FACTS -- FACTS COUNT.

First, count (1) internally is rewritten as count (*). That is a FACT.

Don’t you agree with this? Well, read more on Ask Tom’s page on COUNT (*) vs. COUNT (1) when you have time. It’s a real lengthy discussion which worth spending time on.