Monday, February 8, 2010

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

8 comments:

  1. Nice!
    If only a single developer cares about that mentioned here...

    Damir Vadas
    http://damir-vadas.blogspot.com/

    ReplyDelete
  2. If I made just one developer to care little more this post served it's purpose :D

    btw, thanks for comment!

    Regards,
    Marko

    ReplyDelete
  3. Maybe I found another (fastest way)for 0.53 sec pro search...in some loop that may be a lot:
    SQL> declare
    2 l_cnt number;
    3 begin
    4 select 1 into l_cnt
    5 from dual
    6 where exists (select code
    7 from testtab
    8 where code=999);
    9 if nvl(l_cnt,-1)>0 then
    10 dbms_output.put_line('Exists!');
    11 else
    12 dbms_output.put_line('NOT Exists!');
    13 end if;
    14 end;
    15 /
    Exists!

    PL/SQL procedure successfully completed.

    Elapsed: 00:00:00.17
    SQL> alter system flush buffer_cache;

    System altered.

    Elapsed: 00:00:00.04
    SQL> declare
    2 l_cnt number;
    3 begin
    4 select count(*) into l_cnt
    5 from dual
    6 where exists (select code
    7 from testtab
    8 where code=999);
    9 if l_cnt>0 then
    10 dbms_output.put_line('Exists!');
    11 else
    12 dbms_output.put_line('NOT Exists!');
    13 end if;
    14 end;
    15 /
    Exists!

    PL/SQL procedure successfully completed.

    Elapsed: 00:00:00.07
    SQL>

    Cheers!

    ReplyDelete
  4. small correction for my example:
    declare
    l_cnt number;
    begin
    select 1 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;
    exception
    when no_data_found then
    dbms_output.put_line('NOT Exists!');
    end;
    /

    Exception is important for this approach!
    ;-)

    ReplyDelete
  5. Hi Damir,

    nice example :)

    I have two questions:

    1. Why you nead this lines in your example:
    else
    dbms_output.put_line('NOT Exists!');

    2. Could you please explain me why this approach is the fastest way. I cannot figure that out :)

    Thanks!

    Regards,
    Marko

    ReplyDelete
  6. Marko,

    1) You're right. It is not needed at all-only exception part when record is not found.

    2) I was measure timing...as shown in my case example so I found it fastest. Does it mean you didn't get the same result? If so, then CBO is different so I think /* +FIRST_ROW */ rule should be added...
    ;-)
    Rg,
    Damir

    ReplyDelete
  7. My executions are around 00:00:00.07 and that value varies through query executions so I cannot prove what example is the fastest.

    Thanks for another fast example ;)

    Regards,
    Marko

    ReplyDelete
  8. Thank you for sharing this Oracle Query to check for the existence of a row in a table, oracleconnections

    ReplyDelete