sql - Oracle EXISTS 子句与 ROWNUM = 1

Question

长期以来，我一直在使用 EXISTS 子句来确定在给定条件下给定表中是否至少存在一条记录。例如 - 如果我想查看“员工”表中是否存在姓氏 = 'smith' 的员工，我使用了以下查询

select 1
  into v_exists_flag
  from dual
 where exists (select 1 
                 from employee 
                where lastname = 'smith'
              )

这绝对比使用 count(*) 子句更有效。

select count(*) 
  into v_count 
  from employee
 where lastname = 'smith'

如果 v_count > 0 那么....

但是，最近有人提到使用 ROWNUM = 1 比使用 EXISTS 子句具有更好的性能，如下所示

select 1
  into v_count
  from employee
 where lastname = 'smith'
   and rownum = 1

这是正确的吗？有人可以证实这一点。

提前致谢

Answer 1

在启用自动跟踪的情况下尝试这两个选项，看看哪个选项的一致性获取较少。我认为它们的性能大致相同，但对我来说，rownum 示例更易于阅读。

例如:

SQL> create table t1 as select object_name from all_objects;

Table created.

SQL> create index t1_idx1 on t1 (object_name);

Index created.

SQL> set autot on

SQL> select 1 from t1 where object_name = 'TOP_N' and rownum = 1;

     1
----------
     1
Statistics
----------------------------------------------------------
  0  recursive calls
  0  db block gets
  2  consistent gets
  0  physical reads
  0  redo size
519  bytes sent via SQL*Net to client
523  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  1  rows processed

SQL> select 1 from dual where exists (select object_name from t1 where object_name = 'TOP_N'); 

     1
----------
     1

Statistics
----------------------------------------------------------
  0  recursive calls
  0  db block gets
  2  consistent gets
  0  physical reads
  0  redo size
519  bytes sent via SQL*Net to client
523  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  1  rows processed