COUNT(*) or COUNT(1) or COUNT(anything else)…

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…

Looking at a number of questions and answers on the AskTom site, this seems to have been done to death, so I’ll just post a link to what appears to be a definitive couple of answers: here and here.

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.

🙂

By continuing to use the site, you agree to the use of cookies. more information

In common with almost all professionally run websites, this website logs the IP address of each visitor in order to keep it running reliably. This is also essential for protecting the website and its visitors from malicious attacks, including infection with malware.

This website provides information as a service to visitors such as yourself, and to do this reliably and efficiently, it sometimes places small amounts of information on your computer or device (e.g. mobile phone). This includes small files known as cookies. The cookies stored by this website cannot be used to identify you personally.

We use cookies to understand what pages and information visitors find useful, and to detect problems such as broken links, or pages which are taking a long time to load.

We sometimes use cookies to remember a choice you make on one page, when you have moved to another page if that information can be used to make the website work better. For example:
- avoiding the need to ask for the same information several times during a session (e.g. when filling in forms), or
- remembering that you have logged in, so that you don’t have to re-enter your username and password on every page.

You can prevent the setting of cookies by adjusting the settings on your browser (see your browser Help for how to do this). Be aware that disabling cookies will affect the functionality of this and many other websites that you visit.

Close