SELECT COUNT(*) vs. fetching twice with an explicit cursor

Question

I have read a book whose title is "Oracle PL SQL Programming" (2nd ed.) by Steven Feuerstein & Bill Pribyl. On page 99, there is a point suggested that

Do not "SELECT COUNT(*)" from a table unless you really need to know the total number of "hits." If you only need to know whether there is more than one match, simply fetch twice with an explicit cursor.

Could you anyone explain this point more to me by providing example? Thank you.

Update:

As Steven Feuerstein & Bill Pribyl recommends us not to use SELECT COUNT() to check whether records in a table exist or not, could anyone help me edit the code below in order to avoid using SELECT COUNT(*) by using explicit cursor instead? This code is written in the Oracle stored procedure.

I have a table emp(emp_id, emp_name, ...), so to check the provided employee ID corret or not:

CREATE OR REPLACE PROCEDURE do_sth ( emp_id_in IN emp.emp_id%TYPE )
IS
v_rows INTEGER;
BEGIN
    ...

    SELECT COUNT(*) INTO v_rows
    FROM emp
    WHERE emp_id = emp_id_in;

    IF v_rows > 0 THEN
        /* do sth */
    END;

    /* more statements */
    ...

END do_sth;

Answer 1

There are a number of reasons why developers might perform select COUNT(*) from a table in a PL/SQL program:

1) They genuinely need to know how many rows there are in the table.

In this case there is no choice: select COUNT(*) and wait for the result. This will be pretty fast on many tables, but could take a while on a big table.

2) They just need to know whether a row exists or not.

This doesn't warrant counting all the rows in the table. A number of techniques are possible:

a) Explicit cursor method:

DECLARE
   CURSOR c IS SELECT '1' dummy FROM mytable WHERE ...;
   v VARCHAR2(1);
BEGIN
   OPEN c;
   FETCH c INTO v;
   IF c%FOUND THEN
      -- A row exists
      ...
   ELSE
      -- No row exists
      ...
   END IF;
END;

b) SELECT INTO method

DECLARE
   v VARCHAR2(1);
BEGIN
   SELECT '1' INTO v FROM mytable 
   WHERE ... 
   AND ROWNUM=1; -- Stop fetching if 1 found
   -- At least one row exists
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      -- No row exists
END;

c) SELECT COUNT(*) with ROWNUM method

DECLARE
   cnt INTEGER;
BEGIN
   SELECT COUNT(*) INTO cnt FROM mytable 
   WHERE ... 
   AND ROWNUM=1; -- Stop counting if 1 found
   IF cnt = 0 THEN
      -- No row found
   ELSE
      -- Row found
   END IF;
END;

3) They need to know whether more than 1 row exists.

Variations on the techniques for (2) work:

a) Explicit cursor method:

DECLARE
   CURSOR c IS SELECT '1' dummy FROM mytable WHERE ...;
   v VARCHAR2(1);
BEGIN
   OPEN c;
   FETCH c INTO v;
   FETCH c INTO v;
   IF c%FOUND THEN
      -- 2 or more rows exists
      ...
   ELSE
      -- 1 or 0 rows exist
      ...
   END IF;
END;

b) SELECT INTO method

DECLARE
   v VARCHAR2(1);
BEGIN
   SELECT '1' INTO v FROM mytable 
   WHERE ... ;
   -- Exactly 1 row exists
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      -- No row exists
   WHEN TOO_MANY_ROWS THEN
      -- More than 1 row exists
END;

c) SELECT COUNT(*) with ROWNUM method

DECLARE
   cnt INTEGER;
BEGIN
   SELECT COUNT(*) INTO cnt FROM mytable 
   WHERE ... 
   AND ROWNUM <= 2; -- Stop counting if 2 found
   IF cnt = 0 THEN
      -- No row found
   IF cnt = 1 THEN
      -- 1 row found
   ELSE
      -- More than 1 row found
   END IF;
END;

Which method you use is largely a matter of preference (and some religious zealotry!) Steven Feuerstein has always favoured explicit cursors over implicit (SELECT INTO and cursor FOR loops); Tom Kyte favours implicit cursors (and I agree with him).

The important point is that to select COUNT(*) without restricting the ROWCOUNT is expensive and should therefore only be done when a count is trully needed.

As for your supplementary question about how to re-write this with an explicit cursor:

CREATE OR REPLACE PROCEDURE do_sth ( emp_id_in IN emp.emp_id%TYPE )
IS
v_rows INTEGER;
BEGIN
    ...

    SELECT COUNT(*) INTO v_rows
    FROM emp
    WHERE emp_id = emp_id_in;

    IF v_rows > 0 THEN
        /* do sth */
    END;

    /* more statements */
    ...

END do_sth;

That would be:

CREATE OR REPLACE PROCEDURE do_sth ( emp_id_in IN emp.emp_id%TYPE )
IS
    CURSOR c IS SELECT 1
                FROM emp
                WHERE emp_id = emp_id_in;
    v_dummy INTEGER;
BEGIN
    ...

    OPEN c;    
    FETCH c INTO v_dummy;
    IF c%FOUND > 0 THEN
        /* do sth */
    END;
    CLOSE c;

    /* more statements */
    ...

END do_sth;

But really, in your example it is no better or worse, since you are selecting the primary key and Oracle is clever enough to know that it only needs to fetch once.