Skip to content

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)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
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)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
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;
/