Oracle EXISTS 子句与 ROWNUM = 1 的区别

Question

很长一段时间以来，我一直在使用EXISTS子句来确定给定条件下是否存在一个记录在一个给定的表中。 例如 - 如果我想查看"employee"表中是否存在一个姓为'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

这正确吗？有人可以确认一下吗。

提前感谢。

- user2836468

Answer 1

启用autotrace选项并尝试这两个选项，看哪一个产生的consistent gets更少。我认为它们的表现应该差不多，但是对我而言，使用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

- Stephen ODonnell

Answer 2

在stackoverflow上有类似的问题。

Adam Musch在这里表示没有实质性的区别： 如何最快地检查数据库表中是否存在某些记录？

这里是另一个关于rownum = 1性能的主题： 在“exists”样式查询中，什么条件下ROWNUM=1会显著提高性能？

然而，我在一个未索引的表上尝试了两种方法，使用EXISTS方法的成本略高：

显然，更高的计划是由于需要额外调用DUAL。

示例：

CREATE TABLE rownum_test (x)
  AS SELECT rownum FROM all_objects;

DECLARE
  v_exists NUMBER;
BEGIN
  FOR v_i IN 1..34050 LOOP
    SELECT 1
      INTO v_exists
      FROM dual
    WHERE EXISTS (SELECT 1 FROM rownum_test WHERE x = v_i);
  END LOOP;
END; -- 13,2 seconds

DECLARE
  v_exists NUMBER;
BEGIN
  FOR v_i IN 1..34050 LOOP
    SELECT 1
      INTO v_exists
      FROM rownum_test
    WHERE x = v_i AND rownum = 1;
  END LOOP;
END; -- 13,3 seconds

另一方面，测试表明 ROWNUM 方法略慢 - 但可能是因为我的简单测试数据不够好。

在 Oracle 11G R2 上进行测试。

- Przemyslaw Kruglej

---

谢谢你们两个（Stephen和Przemyslaw）！ - user2836468

嗨，Przemyslaw - 我刚读了Adam Musch的帖子。根据他的帖子 - 如果列被索引，没有显着的差异。 - user2836468

很抱歉 - 我不小心按了发送键。 - user2836468

继续上一个评论 - 如果该列已经建立索引，他似乎推荐使用EXISTS子句。 - user2836468

你的意思是当它未被索引时；）（这是Adam Musch写的）。我想这应该在不同的Oracle版本和小/中/大型表上进行检查，无论是索引还是未索引，并比较Stephen ODonnell在他的帖子中提供的统计数据。 - Przemyslaw Kruglej