Re: Update performance issue in stored procedure....

In article <fongdaEo4EvD.85C_at_netcom.com>, fongda_at_netcom.com wrote:

> In article <01bd3534$d1f4e120$5901010a_at_richardchen.info.taishinbank>,
> RLC <richchen_at_ms6.hinet.net> wrote:
> >Dear all Oracle pros,
> >
> >Currently I would like to update a cloumn in table P which has about 1
> >millions rows. The update value is from another table A which have about
> >3000 rows.
> >
> >The procedure I wrote is:
> >
> >CREATE OR REPLACE PROCEDURE GO(Tmpkey char)
> >IS BEGIN
> >update P set id=(select A.id from A where A.custtype='*' and P.cid=A.cid)
> >where timekey=tmpkey;
> >commit;
> >END;
> >
> >And it costs me about 2 hours to get the result.
> >
> >Does anyone has better idea to tuning the SQL command to increase the
> >update performance ??
>
> You can use a cursor-for-loop to store the values of A into an array and
> then update p from the array...it should be faster.
>
> --
>

You don't give any details of the indexes available on table P, or of the proportion of rows in P and A which contribute to the update, but it look as
if you might do well to have an index on (cid,timekey) and render the statement as:
update P set id =

   (select A.id from A
    where A.custtype='*'
     and P.cid=A.cid)
where (cid,timekey) in
(select cid,tmpkey
 from A)

This requires a full-table scan of A (3000 rows), for each row of which you update all rows of P with corresponding values of cis AND timekey. BTW, don't worry about selecting a program variable in the subquery: it's a standard ploy to avoid separating the components of the access key into a simple predicate (on timekey) and a subquery (on cid).

HTH. Chrysalis. Received on Mon Feb 09 1998 - 00:00:00 CST