Quick query to check for the existence of a row in a table

I've seen several times that when developer needs to check for the existence of a row in a table he counts how many times row occurs in a table.

Something like:

declare
l_cnt number;
begin
    select count(*) into l_cnt
    from test_table
    where row = something;
if l_cnt>0 then
    exists, do something
else
    not exists, do something
end if;
end;
/

This query is correct, but it is not very fast when test_table is big table with many rows.

Much better approach is to stop query when it hits first row. Counting how many times row occurs in a table is unnecessary.



Checking row existence is sometimes used as a part of triggers or procedures that execute very often. In that cases using quicker query check can be great opinion to improve performance.

There are many ways to rewrite your query to speed up things a little and I will show you just few of them. This is probably familiar to most developers and DBA's but I'll will add solutions to the post anyway.


OS: OEL5
DB: Oracle 10.2.0.4

Create simple table without indexes and populate that table with 2000000 rows.

drop table testtab;

create table testtab (id     number,
                             code number,
                             text  varchar2(50),
                             tdate date);

Table created.


insert /*+ append */ into testtab
select rownum, mod(rownum,1000), rpad('x',50,'x'), sysdate+rownum
from all_objects o1,
     all_objects o2
where
   rownum <= 2000000
/

2000000 rows created.

SQL> commit;

Commit complete.

In test cases I will search for an existence of row with code 999.

First solution:

SQL> set timing on
SQL> alter system flush buffer_cache;

System altered.

declare
l_cnt number;
begin
    select count(*) into l_cnt
    from testtab
    where code=999;
if l_cnt>0 then
    dbms_output.put_line('Exists!');
else
    dbms_output.put_line('NOT Exists!');
end if;
end;
/

Exists!

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.32

In this case I spent more then 4 secs to check existence of specified row.



Second solution:

SQL> set timing on
SQL> alter system flush buffer_cache;

System altered.

declare
l_cnt number;
begin
    select count(*) into l_cnt
    from dual
    where exists (select code
          from testtab
          where code=999);
if l_cnt>0 then
    dbms_output.put_line('Exists!');
else
    dbms_output.put_line('NOT Exists!');
end if;
end;
/

Exists!

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07

You can notice significant improvement in this case.
Checking existence lasted only 00.07 secs.
This is my preferred way to check existence - query stops after hitting first row.



Third solution:

SQL> set timing on
SQL> alter system flush buffer_cache;

System altered.

declare
l_cnt number;
begin
    select count(*) into l_cnt
    from testtab
    where code = 999
    and rownum = 1;
if l_cnt>0 then
    dbms_output.put_line('Exists!');
else
    dbms_output.put_line('NOT Exists!');
end if;
end;
/

Exists!

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06

This is another option using rownum=1 inside query. Result is displayed after finding first matching row.


Fourth solution:

SQL> set timing on
SQL> alter system flush buffer_cache;

System altered.

begin
for x in (select count(*) cnt
          from dual
          where exists (select NULL from testtab
                where code=999))
loop
    if (x.cnt = 1)
    then
     dbms_output.put_line('Exists!');
    else
     dbms_output.put_line('NOT Exists!');
    end if;
end loop;
end;
/

Exists!

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07

I found this solution on AskTom site.


Fifth solution:

SQL> set timing on
SQL> alter system flush buffer_cache;

System altered.

begin
for x in (select /*+ first_rows(1) */ count(*) cnt
          from testtab
          where code=999
      and rownum < 2)
loop
    if (x.cnt = 1)
    then
     dbms_output.put_line('Exists!');
    else
     dbms_output.put_line('NOT Exists!');
    end if;
end loop;
end;
/

Exists!

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06

Another solution from AskTom site. This time without querying dual table.

Drop test table:

drop table testtab;

Table dropped.

You can find many other solutions for checking row existence using Google.

REFERENCES:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3069487275935