在什么条件下，ROWNUM = 1可以显着提高“存在"网络中的性能. syle查询 [英] Under what conditions does ROWNUM=1 significantly increase performance in an "exists" syle query

问题描述

我在

I read some of the discussion in this question and thought to myself that in my PL/SQL code I have "exists" style queries all over the place that don't use the ROWNUM=1 optimisation.

我的问题是:

1. 引入ROWNUM = 1是否会显着提高性能?
2. 如果是这样，那么在什么条件下性能会得到特别提高(例如，大量的连接，对未索引列的约束，大表，大结果集)

我试图确定值得重写所有现有查询以添加ROWNUM = 1优化.

I'm trying to determine of it is worth rewriting all of my existing queries to add a ROWNUM=1 optimisation.

我正在考虑的查询是那些可能具有多个联接并可能查询大表的查询.它们的一般形式为:

The queries I'm thinking of are ones that may have multiple joins and may query large tables. They have the general form of:

SELECT 1
INTO ln_count
FROM table_1, table_2...., table_n
WHERE <various joins and conditions>;

IF ln_count > 0 THEN
  <do stuff>
END IF;

我正在考虑将它们更改为:

I'm considering changing them to:

SELECT 1
INTO ln_count
FROM table_1, table_2...., table_n
WHERE <various joins and conditions>
AND ROWNUM = 1;

IF <local variable> > 0 THEN
  <do stuff>
END IF;

推荐答案

对于无法通过简单的单索引查找解决的查询，它确实可以显着提高性能(平均百分之几十).表联接. 但是，它可能隐藏数据/应用程序错误.

It does improve performance significantly (tens of percent average) on queries which cannnot be solved by simple single index lookup e.g. table joins. However it has a potential to hide data/application error.

让我们有一张桌子:

create table t (id number(10,0), padding varchar2(1000));  

-故意不要使用PK来使示例尽可能简单.填充用于模拟每个记录中的实际数据加载

--intentionally don't use PK to make the example as simple as possible. The padding is used to simulate real data load in each record

有很多记录:

insert into t (id, padding)
select rownum, rpad(' ', 1000) from dual connect by level < 10000

现在，如果您问类似的问题

Now if you ask something like

select 1 into ll_exists
from t where id = 5;

无论数据库是在第一个数据块中找到唯一的匹配记录(由于它可以通过许多不同的方式插入，我们无法知道，还是在最后一个数据块中)，它都必须遍历整个表.那是因为它不知道只有一条匹配的记录.另一方面，如果您使用...并且rownum = 1，则它会在找到记录后停止遍历数据，因为您告诉它没有(或不需要)另一条匹配的记录.

the DB must go through the whole table whether it found the only matching record in the first data block (which by the way we cannot know because it could be inserted by many different ways) or in the last. That's because it doesn't know that there is only one matching record. On the other hand if you use ... and rownum = 1 than it can stop traversing through data after the record is found because you told it that there is not (or not needed) another matching record.

缺点是，如果数据包含多个可能的记录，则使用rownum约束可能会得到不确定的结果. 如果查询是

The drawback is that with the rownum constraint you may get undeterministic results if the data contains more than one possible record. If the query was

select id into ll_id
from t where mod (id, 2) = 1
and rownum = 1;

然后我可能会从数据库收到答案1，3以及123 ...不能保证顺序，这就是结果. (没有rownum子句，我会得到TOO_MANY_ROWS异常.这取决于情况更糟的情况)

then I may receive from the DB answer 1 as well as 3 as well as 123 ... order is not guaranteed and this is the consequence. (without the rownum clause I would get a TOO_MANY_ROWS exception. It depends on situation which one is worse)

如果您真的想查询哪个测试存在，那就用这种方式写.

If you really want query which tests existence then WRITE IT THAT WAY.

begin

select 'It does' 
  into ls_exists
from dual where
exists (your_original_query_without_rownum);

do_something_when_it_does_exist
exception
  when no_data_found then
    do_something_when_it_doesn't_exist
end;

这篇关于在什么条件下，ROWNUM = 1可以显着提高“存在"网络中的性能. syle查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！