ROWNUM = 1 to replace count(*)

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
posted @ 2019-01-15 17:33  kakaisgood  阅读(256)  评论(0编辑  收藏  举报