G-8110: Never use SELECT COUNT(*) if you are only interested in the existence of a row.
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;
/
|