Tuesday, 1 October 2013

Oracle EXISTS clause Vs ROWNUM = 1

Oracle EXISTS clause Vs ROWNUM = 1

For a long time, I have been using the EXISTS clause to determine if at
least one record exists in a given table for a given condition. for
example - if I wanted to see if an employee by lastname = 'smith' exists
in the "employee" table, I used the following query
select 1 into v_exists_flag from dual where exists ( select 1 from
employee where lastname = 'smith' )
This is definitely more efficient than using the count(*) clause.
select count(*) into v_count from employee where lastname = 'smith'
if v_count > 0 then....
But, recently someone mentioned that using ROWNUM = 1 has better
performance than using the EXISTS clause as shown below
select 1 into v_count from employee where lastname = 'smith' and rownum = 1
Is this correct? Can someone confirm this.
Thanks in advance

No comments:

Post a Comment