Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: PL\SQL optimization for update or insert behavior

Re: PL\SQL optimization for update or insert behavior

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Wed, 25 Jul 2001 18:50:30 +0200
Message-ID: <996079834.15043.0.pluto.d4ee154e@news.demon.nl>

"Jon" <jumper_at_engineer.com> wrote in message news:1b9d847.0107250540.789992d1_at_posting.google.com...
> I am trying to create a PL statement that will either update an
> existing record or insert a new record if no existing records are
> found. This will eventually be used in a JDBC PreparedStatement for
> batch processing. Assume that there is a pretty random distribution of
> inserts and updates. I've come up with a few possible ways of doing
> this but I'm not sure which will run faster. The smallest of gains are
> important since it will be run possibly thousands of times (or more).
> The requirements for the project forbid me to use stored procedures so
> that is not an option.
>
> Which of the follow statements would be the most efficient (or is
> there a better way)? Any help would be greatly appreciated.
>
> Jon
>
> --------------- Statement 1: --------
>
>
> DECLARE
> recordFound EXCEPTION;
> spoo ROWID;
> BEGIN
> SELECT ROWID into spoo from JCTEST where account_number = '103';
> raise recordFound;
> EXCEPTION
> when recordFound then
> UPDATE JCTEST SET amount = 23.23 where account_number = '103';
> when no_data_found then
> INSERT into JCTEST VALUES ('103', 101.70);
> END;
>
> -------------- Statement 2 ---------
> DECLARE
> recordFound EXCEPTION;
> spoo ROWID;
> acc varchar2(5):='103';
> amt NUMBER :=101.70;
> BEGIN
> SELECT ROWID into spoo from JCTEST where account_number = acc;
> raise recordFound;
> EXCEPTION
> when recordFound then
> UPDATE JCTEST SET amount = amt where account_number = acc;
> when no_data_found then
> INSERT into JCTEST VALUES (acc, amt);
> END;
>
> ----------- Statement 3 ----------
> DECLARE
> recordFound EXCEPTION;
> spoo NUMBER;
> BEGIN
> SELECT COUNT(*) into spoo from JCTEST where account_number = '103';
> if spoo > 0 then
> UPDATE JCTEST SET amount = 23.23 where account_number = '103';
> else
> INSERT into JCTEST VALUES ('103', 101.70);
> END IF;
> END;
And here is method 5

DECLARE
cursor getjctest(p_account_number in number) is select amount
from jctest
where account_number = p_account_number
for update of amount;

acc varchar2(5):='103';
p_amount number;
amt NUMBER :=101.70;
BEGIN
open getjctest(acc);
fetch getjctest into p_amount;
if getjctest%found then

   update jctest
   set amount = amt
   where current of getjctest;
else
  insert into jctest
  values (acc, amount);
end if;
end;
/

The other examples all use hardcoded literals. You should avoid them at all of cost as they will result in hard parses, in your jdbc situation this will just kill performance and have your cpu(s) choke and suffocate.
The use of a cursor with for update will place a row level lock on your table, the other examples all escalate into table level locks. Finally, you don't need the exception anymore as every cursor has a found and not found attribute
Implicit cursors BTW also have those attributes. Use sql%found and sql%notfound. Any select into is a statement with an implicit cursor.

Issues like this are discussed in your docs and in the book of Steven Feuerstein on PL/SQL published by O'Reilly.

Convert this block into a procedure with account number and amount as IN parameters, and you are set with a transparent method.

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Wed Jul 25 2001 - 11:50:30 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US