Oracle PL/SQL Best Practices

Name

SQL-08: Use COUNT only when the actual number of occurrences is needed.

Synopsis

Don’t 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 use COUNT only when you need to answer the question: “How many rows match a certain criteria?”

Example

Suppose I have been asked to write a program that returns TRUE if there is at least one book in a given category. I 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 I am 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
      SELECT 1
        FROM book
       WHERE category = category_in;
BEGIN
   OPEN category_cur;
   FETCH category_cur INTO category_rec;
   retval := category_cur%FOUND;
   CLOSE category_cur;
   RETURN retval;
END;

In other words: all I have to do is see if there is a single row, and I am done.

Benefits

With this practice, you get optimal performance out of your query.

The readability of your code also improves, since it’s a more accurate translation of ...