在 Oracle 中检查行是否存在的最快查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1088156/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Quickest query to check for the existence of a row in Oracle?
提问by Josh Kodroff
I'm using Oracle, and I have a very large table. I need to check for the existence of any row meeting some simple criteria. What's the best way to go about this using simple SQL?
我正在使用 Oracle，并且我有一个非常大的表。我需要检查是否存在满足一些简单标准的任何行。使用简单的 SQL 来解决这个问题的最佳方法是什么？
Here's my best guess, and while it may turn out to be fast enough for my purposes, I'd love to learn a canonical way to basically do SQL Server's "exists" in Oracle:
这是我最好的猜测，虽然它可能对我的目的来说足够快，但我很想学习一种规范的方法来基本上在 Oracle 中执行 SQL Server 的“存在”：
select count(x_id) from x where x.col_a = value_a and x.col_b = value_b;
The count() would then be returned as a boolean in another tier. The main point is that I want Oracle to do the bare minimum for this query - I only need to know if there are any rows matching the criteria.
然后 count() 将在另一层中作为布尔值返回。主要的一点是我希望 Oracle 为这个查询做最少的事情——我只需要知道是否有任何符合条件的行。
And yes, those columns will most definitely be indexed.
是的，这些列肯定会被索引。
回答by Tony Andrews
Using COUNT(*) is OK if you also use rownum=1:
如果您还使用 rownum=1，则使用 COUNT(*) 是可以的：
declare
l_cnt integer;
begin
select count(*)
into l_cnt
from x
where x.col_a = value_a
and x.col_b = value_b
and rownum = 1;
end;
This will always return a row, so no need to handle any NO_DATA_FOUND exception. The value of l_cnt will be 0 (no rows) or 1 (at least 1 row exists).
这将始终返回一行，因此无需处理任何 NO_DATA_FOUND 异常。l_cnt 的值将为 0（无行）或 1（至少存在 1 行）。
回答by Nick Pierpoint
I think using EXISTS gives a more natural answer to the question than trying to optimise a COUNT query using ROWNUM.
我认为使用 EXISTS 可以比尝试使用 ROWNUM 优化 COUNT 查询更自然地回答这个问题。
Let Oracle do the ROWNUM optimisation for you.
让 Oracle 为您做 ROWNUM 优化。
create or replace function is_exists (
p_value_a varchar2,
p_value_b varchar2)
return boolean
is
v_exists varchar2(1 char);
begin
begin
select 'Y' into v_exists from dual
where exists
(select 1 from x where x.col_a = p_value_a and x.col_b = p_value_a);
exception
when no_data_found then
v_exists := null;
end;
return v_exists is not null;
end is_exists;
回答by Quassnoi
SELECT NULL
FROM x
WHERE x.col_a = value_a
AND x.col_b = value_b
AND rownum = 1
COUNT(*)is certainly not the best way since it will need to count all the rows, while ROWNUM = 1returns as soon as it finds the first matching row.
COUNT(*)当然不是最好的方法，因为它需要计算所有行，而ROWNUM = 1一旦找到第一个匹配行就返回。
Here's the PL/SQLcode:
这是PL/SQL代码：
DECLARE
ex INT;
BEGIN
BEGIN
SELECT NULL
INTO ex
FROM dual
WHERE 1 = 1
AND rownum = 1;
DBMS_OUTPUT.put_line('found');
EXCEPTION
WHEN no_data_found THEN
DBMS_OUTPUT.put_line('not found');
END;
END;
回答by Michal Pravda
begin
select 'row DOES exist'
into ls_result
from dual
where exists (select null from x where x.col_a = value_a and x.col_b = value_b);
exception
when no_data_found then
ls_result := ' row does NOT exist';
end;
