Home » SQL & PL/SQL » SQL & PL/SQL » Better way of checking existence of record in query/table (Oracle 10g)
Better way of checking existence of record in query/table [message #376101] Tue, 16 December 2008 01:02 Go to next message
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 #376104 is a reply to message #376101] Tue, 16 December 2008 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
None of the above.

Regards
Michel
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 #376122 is a reply to message #376104] Tue, 16 December 2008 03:03 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Michel Cadot wrote on Tue, 16 December 2008 13:11
None of the above.

Regards
Michel


Then what is the better way??

Quote:
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.

Its not about UPDATE or INSERT, it can be used for any purpose depending on whether the record is existing or not...

Regards
Re: Better way of checking existence of record in query/table [message #376137 is a reply to message #376122] Tue, 16 December 2008 04:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The better depends on what you want to do around this.
It depends on the whole issue.

Regards
Michel
Re: Better way of checking existence of record in query/table [message #376313 is a reply to message #376101] Tue, 16 December 2008 22:40 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Quote:
The better depends on what you want to do around this.
It depends on the whole issue.

Regards
Michel


May be I didn't putup my question correctly. As you can see both the select performs the same function (whether record exists or not), so which way of the SELECT (COUNT or EXISTS) is better in terms of performance. I understand that for INSERT/UPDATE, we can use MERGE, but I am not concerned at the TO DO part (as it can be anything - calling another function, assigning value to variable etc.). But is there any difference in terms of performance between these 2 ways of SELECTs?

Regards
Re: Better way of checking existence of record in query/table [message #376333 is a reply to message #376313] Wed, 17 December 2008 00:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't even start to answer to a question when I know that both are wrong.

Regards
Michel
Re: Better way of checking existence of record in query/table [message #376365 is a reply to message #376101] Wed, 17 December 2008 02:21 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Quote:
I don't even start to answer to a question when I know that both are wrong.

Regards
Michel


Not sure what you mean by both are wrong? Confused What is the correct way then?
Re: Better way of checking existence of record in query/table [message #376370 is a reply to message #376365] Wed, 17 December 2008 02:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 16 December 2008 11:35
The better depends on what you want to do around this.
It depends on the whole issue.

Regards
Michel


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 Go to previous messageGo to next message
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;
/
Re: Better way of checking existence of record in query/table [message #376400 is a reply to message #376101] Wed, 17 December 2008 04:00 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Thanks JRowbottom for a detailed explanation. That what exactly I was looking for. Though I understand it also depends on the underlying query and what is the ultimate result we are expecting.

Thanks again. It was indeed helpful as per my understanding.

Michel - May be still I didn't made my question clear to you... Sad
Re: Better way of checking existence of record in query/table [message #376450 is a reply to message #376400] Wed, 17 December 2008 06:52 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It will depend to an extent on the underlying query. You've got the timing harnes I used - try it for yourself and see which works faster with your query and your data.
Previous Topic: checking for blank line
Next Topic: return to For loop
Goto Forum:
  


Current Time: Thu Dec 08 12:52:02 CST 2016

Total time taken to generate the page: 0.25032 seconds