• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

oracle sql exists clause

Is there a more efficient/faster or more elegant way to do this statement with just the exists clause? I use these in triggers all over the place so I'd like to improve it if possible.
SQL Developer SQL Tuning Advisor says "no recomendation to improve it"
set serveroutput on;
declare l_count number;
begin
SELECT COUNT(*)
    INTO l_count
    FROM dual
    WHERE EXISTS
      (SELECT employee_id FROM hr.employees WHERE manager_id = 114);

if l_count > 0 then
 dbms_output.put_line ('found some');
end if;
    
end;

Open in new window

0
claghorn
Asked:
claghorn
2 Solutions
 
ajexpertCommented:
How about this?
set serveroutput on;

DECLARE
   l_count   NUMBER :=0;
BEGIN
   SELECT   COUNT (employee_id)
     INTO   l_count
     FROM   hr.employees
    WHERE   manager_id = 114;

   IF l_count > 0 THEN
      DBMS_OUTPUT.put_line ('found some');
   END IF;
END;

Open in new window

0
 
ianmills2002Commented:
I agree with the solution by ajexpert.

I would also ensure that the hr.employees table has an index on the column manager_id.
0
 
ob2sCommented:
Hi,

Your original code should be more efficient than the solutions given so far. Using EXISTS allows the optimizer to stop the subquery after the first match found.  In your example, you don't really need count(*) either, since only one value is returned. Instead, you could just select a value of 1 when EXISTS(...) is true, otherwise return NULL:

set serveroutput on;
declare l_count number;
begin
SELECT 1
    INTO l_count
    FROM dual
    WHERE EXISTS
      (SELECT employee_id FROM hr.employees WHERE manager_id = 114);

if l_count > 0 then
 dbms_output.put_line ('found some');
end if;
    
end;

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
No matter what solution you use you might also want to limit the results to just the first row:  WHERE manager_id = 114 and rownum=1
0
 
ajexpertCommented:
>> efficient/faster way?

You should check explain plan

I  am sure involving dual table has more cost...
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now