最快查询以检查Oracle中是否存在行？

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.

是的,这些列肯定会被编入索引。

4 个解决方案

#1

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行)。

#2

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;

#3

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 = 1 returns as soon as it finds the first matching row.

COUNT(*)肯定不是最好的方法,因为它需要计算所有行,而ROWNUM = 1一旦找到第一个匹配的行就返回。

Here's the PL/SQL code:

这是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;

#4

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;

#1