Better way of checking existence of record in query/table [message #376101] |
Tue, 16 December 2008 01:02  |
himang
Messages: 282 Registered: March 2005 Location: Bangalore
|
Senior Member |

|
|
Sometimes we have to check if record(s) exist in the table or for a query - Now we use 2 different approaches to write suitable sql query.
The first is
BEGIN
SELECT 'X'
From dual
Where exists (select 'x' from table1 t1, table2 t2 where t1…= t2….);
-- TO DO WHAT WE NEED IF RECORD EXISTS
Exception
When NO_DATA_FOUND then
-- TO DO WHAT WE NEED IF RECORD DOES NOT EXIST
End;
The other is :-
select count(1)
into v_count
from table1 t1, table2 t2
where t1…= t2….
And rownum <2;
If v_count = 0
Then
--TO DO WHAT WE NEED IF RECORD DOES NOT EXIST
Else
-- TO DO WHAT WE NEED IF RECORD EXISTS
End if;
The main question is which decision is better 1 or 2 for performance reason or it really doesn't makes any difference?
|
|
|
|
Re: Better way of checking existence of record in query/table [message #376111 is a reply to message #376101] |
Tue, 16 December 2008 01:57   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Quote: | The main question is which decision is better 1 or 2 for performance reason or it really doesn't makes any difference
|
The more important question is: which of them correctly treats its parallel execution by multiple processes? If "TO DO WHAT WE NEED IF RECORD EXISTS" is UPDATE and "TO DO WHAT WE NEED IF RECORD DOES NOT EXIST" is INSERT (both having relation with queried result), then none of the above. Investigate the MERGE statement instead.
|
|
|
|
|
|
|
|
|
Re: Better way of checking existence of record in query/table [message #376373 is a reply to message #376365] |
Wed, 17 December 2008 03:01   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I'm not quite sure what you're getting at Michel - sometimes you need to check to see if a record, or a record of a certain type, exists in a table,
Which one to use depends on how often you expect the check to succeed.
The EXISTS query is about 10-15% faster in general, but if there is no matching data, then the overhead of raising and handling the exception mean that the COUNT quety is over 10x faster.
So if there will almost alway be data there, I'd use the EXISTS, otherwise I'd go with the COUNT.
Timings were produced like this:create table test_045 (col_1 number, col_2 number);
1) For a No matching rows test:
insert into test_045 select level,0 from dual connect by level <= 1000000;
2) For a Many Matching rows test:
insert into test_045 select level,case when mod(level,100)=1 then 1 else 0 end from dual connect by level <= 1000000;
3) For a single match near the start of the table
insert into test_045 select level,case when level=100 then 1 else 0 end from dual connect by level <= 1000000;
4) For a single match at the end of the table
insert into test_045 select level,case when level=1000000 then 1 else 0 end from dual connect by level <= 1000000;
create index test_045_idx on test_045(col_2);
declare
v_time pls_integer;
v_val pls_integer;
v_iter pls_integer := 100000;
begin
v_time := dbms_utility.get_time;
for i in 1..v_iter loop
BEGIN
SELECT 1
into v_val
From dual
Where exists (select null from test_045 where col_2 = 1);
null;
Exception
When NO_DATA_FOUND then
null;
End;
end loop;
dbms_output.put_line(dbms_utility.get_time - v_time);
v_time := dbms_utility.get_time;
for i in 1..v_iter loop
select count(1)
into v_val
from test_045
where col_2 = 1
And rownum <2;
end loop;
dbms_output.put_line(dbms_utility.get_time - v_time);
end;
/
|
|
|
|
|