CODEWITHSUNDEEP
oracle-databaseplsql
Josh Kodroff
Josh Kodroff

Reputation: 28121

Quickest query to check for the existence of a row in 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?

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:

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.

And yes, those columns will most definitely be indexed.

Upvotes: 12

Views: 28680

Answers (4)

Nick Pierpoint
Nick Pierpoint

Reputation: 17769

I think using EXISTS gives a more natural answer to the question than trying to optimise a COUNT query using ROWNUM.

Let Oracle do the ROWNUM optimisation for you.

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;

Upvotes: 7

Michal Pravda
Michal Pravda

Reputation: 829

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;

Upvotes: 1

Tony Andrews
Tony Andrews

Reputation: 132580

Using COUNT(*) is OK if you also use rownum=1:

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).

Upvotes: 18

Quassnoi
Quassnoi

Reputation: 425411

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.

Here's the PL/SQL code:

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;

Upvotes: 5

Related Questions