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

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.

-- 


Talk to you later!

Dara 

                       __\/__
                      / ^  ^ \
                   (\| (o)(o) |/)
------------------oOOOo--oo--oOOOo-------------------------
|  Dara Fong             e-mail: fongda<at>netcom<dot>com |
 -----------------------------------------------------------
|  Intelligence is like underwear, everyone has it but    |
|  you don't have to show it off.                         |
 -----------------------------------------------------------
|  Any unsolicited commercial e-mail and/or the inclusion |
|  of my user-id in any mailing list without my express   |
|  prior written approval, including the receipt of a mass|
|  e-mail message and/or the unauthorized reselling of    |
|  this user-id to mailing list vendors, will be met with |
|  a complaint to your internet provider. It is           |
|  recommended you do not attempt these practices. Should |
|  you choose to ignore this warning, you will be subject |
|  to any remedy which may be exercised by your internet  |
|  provider. You have been warned.                        | 
-----------------------------Oooo.-------------------------
                   .oooO     (   )
                   (   )      ) /
                    \ (      (_/
                     \_)