It should be a fairly simple task – count the number of rows in a table. I recently had the “pleasure” of discussing this with a colleague who was adamant that the best way to count the number of rows in a table was to use something explicit, e.g. COUNT(1) or “even better” COUNT(ROWID).
The basis for his thinking seemed to be OCP Introduction to Oracle 9i: SQL Exam Guide which apparently says something like
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 pocessing mechanism.
Now, I’ve been told both things in the past – I was always taught to use COUNT(*), but some people that I’ve worked with have always used COUNT(0) or similar. So, as with all these kinds of questions, I turned to Tom Kyte for more information…
As ever, there are a number of neat points made in the threads, but for those that don’t want to read through them all, here’s my concise summary:
- COUNT(1) is optimised to COUNT(*) in the database.
- COUNT(*) is meaningful (it shows we are counting everything), whereas COUNT(rowid) means nothing.
- COUNT(*) is the right way to go.
- This remains true for any version of the database greater than 8.
- This remains true regardless of the column types in the table
- Matt was right.