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. This could have a big performance impact.

If we do a select count(*) where rownum = 1 there is also some overhead as there are two context switches between the PL/SQL and SQL engines.

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;
   k_zero   constant simple_integer := 0;
   k_salary constant employee.salary%type := 5000;
begin
   select count(*)
     into l_count
     from employee
    where salary < k_salary;
    if l_count > k_zero then
       <<emp_loop>>
       for r_emp in (select employee_id
                       from employee)
       loop
          if r_emp.salary < k_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
   k_salary constant employee.salary%type := 5000;
begin
    <<emp_loop>>
    for r_emp in (select e1.employee_id
                    from employee e1
                   where exists(select e2.salary
                                  from employee e2
                                 where e2.salary < k_salary))
    loop
       my_package.my_proc(in_employee_id => r_emp.employee_id); 
    end loop emp_loop;
end;
/