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
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步