G-8110: Never use SELECT COUNT(*) if you are only interested in the existence of a row.

Major

Efficiency

Reason

If you do a SELECT count() all rows will be read according to the WHERE clause, even if only the availability of data is of interest. For this we have a big performance overhead. If we do a SELECT count() ... WHERE ROWNUM = 1 there is also a overhead as there will be two communications between the PL/SQL and the SQL engine. See the following example for a better solution.

Example (bad)

DECLARE
   l_count PLS_INTEGER;
   co_zero   CONSTANT SIMPLE_INTEGER := 0;
   co_salary CONSTANT employees.salary%TYPE := 5000;
BEGIN
   SELECT count(*)
     INTO l_count
     FROM employees
    WHERE salary < co_salary;
    IF l_count > co_zero THEN
       <<emp_loop>>
       FOR r_emp IN (SELECT employee_id
                       FROM employees)
       LOOP
          IF r_emp.salary < co_salary THEN
             my_package.my_proc(in_employee_id => r_emp.employee_id); 
          END IF;
       END LOOP emp_loop;
    END IF;
END;
/

Example (good)

DECLARE
   co_salary CONSTANT employees.salary%TYPE := 5000;
BEGIN
    <<emp_loop>>
    FOR r_emp IN (SELECT e1.employee_id
                    FROM employees e1
                   WHERE EXISTS(SELECT e2.salary
                                  FROM employees e2
                                 WHERE e2.salary < co_salary))
    LOOP
       my_package.my_proc(in_employee_id => r_emp.employee_id); 
    END LOOP emp_loop;
END;
/