G-8110

🆓

Warning

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

Rule Details

Severity Level: Critical
Quality Area: Reliability
Fixing times: 10 mins
Local-high
Database Support: OracleDB (v7.3.4 +)

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

Non-Compliant Example

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;
/

Issues

Line	Column	Message
6	11	SELECT COUNT(*) used to check the existence of a row.

Compliant Solution - ★★★★★

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;
/

References

same as Trivadis G-8110