RE: Better way (more efficient) to find existance of row

From: Powell, Mark <mark.powell2_at_hp.com>
Date: Wed, 22 May 2013 20:05:02 +0000
Message-ID: <1E24812FBE5611419EFAFC488D7CCDD1186BC65F_at_G5W2716.americas.hpqcorp.net>

Or just eliminate the count. Perform the update, check number of rows updated, if zero then insert.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Norman Dunbar Sent: Wednesday, May 22, 2013 7:06 AM
To: oracle-l_at_freelists.org
Subject: Re: Better way (more efficient) to find existance of row

Morning Paresh,

On 21/05/13 18:23, Paresh Yadav wrote:
> Using exceptions to handle if-else is a bad idea. The performance will
> be slow.

Have you tried this and run timings on it? One of my firct contract jobs years ago was to go through many millions of lines of PL/SQL, written by a third party, and find places to improve it.

There were numerous occurrences of this:

select count(*)
into whatever
from table
where some_condition;

if whatever = 0 then

   insert into table values ....;
else

   update table set .......;
end if;

We did some timings and found that, on average, using exceptions was faster. The problem being that the where clause will run through the entire table, unless restricted by a rownum = 1, unless there's an index (there invariably wasn't!)

That was in the days before the MERGE statement as mentioned by Niall, and although I have not yet done any timings myself, I suspect (!) that MERGE will be even faster than using exceptions. When I get a few mins to set up a table with a few million rows, I'll try it out and report back, unless someone else already has the info.

> Better to use If else and try to put most probable outcome of the
> comparison in the if part.

Well, any good developer knows to put the most likely outcome first in an IF/CASE/whatever statement - don't they? ;-)

Cheers,
Norm.

-- 
Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL

Company Number: 05132767
--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Wed May 22 2013 - 22:05:02 CEST