Proper Use of SELECT COUNT(*)

A common error among developers is to answer the question “Do I have at least one of ___ ?” by executing a SELECT COUNT(*) against the table/view. This is a very inefficient way to answer that question. SELECT COUNT answers a different question: “How many do I have?” If you really only need to find out if you have N rows, use an explicit cursor and FETCH once. If you need to find out if you have more than one row, use an explicit cursor and FETCH N times.

Use COUNT only when the actual number of occurrences is needed.

Do not use the COUNT function to answer either of the following questions:

• Is there at least one row matching certain criteria?
• Is there more than one row matching certain criteria?

Instead, use an explicit cursor inside a function.

You should only use COUNT when you need to answer the question: “How many rows match a certain criteria?”

Suppose that you have been asked to write a program that returns TRUE if there is at least one book in a given category. You could write it like this:

CREATE OR REPLACE FUNCTION atleastone (
   category_in IN book.category%TYPE)
   RETURN BOOLEAN
IS
   numbooks INTEGER;
BEGIN
   SELECT COUNT(*) INTO numbooks
     FROM book
    WHERE category = category_in;
   RETURN (numbooks > 0);
END;

But you are asking the RDBMS to do lots of unnecessary work. It might find, for instance, that there are 12 million books in the NON-FICTION category. A better approach is:

CREATE OR REPLACE FUNCTION atleastone (
   category_in IN book.category%TYPE)
   RETURN BOOLEAN
IS
   retval BOOLEAN;

   CURSOR category_cur IS
      SELECT 1
        FROM book
       WHERE category = category_in;
   category_rec NUMBER;
BEGIN
   OPEN category_cur;
   FETCH category_cur INTO category_rec;
   retval := category_cur%FOUND;
   CLOSE category_cur;
   RETURN retval;
END;

In other words: all you have to do is see if there is a single row and you are done. With this practice, you get optimal performance out of your query and the readability of your code also improves, since it is a more accurate translation of the requirement.

http://www.quest-pipelines.com/pipelines/plsql/tips04.htm#OCTOBER