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
22
23
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
15
16
17
18
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;
/