How do I perform fast updates?

Hi all

I'm currently working with Oracle 7.3.3 (soon to upgrade).

We have a large table that holds stock/share information. We need to do a 'price' update to the table. The problem I'm having is that the stored procedure I'm using runs very slowly. I checked Dejanews but couldn't find any answers. Here is the code:

Procedure t_updateprices

is

nsedol number;
nprice number;

ndownloaddate varchar;

cursor GetSedol is
Select sedol,price from newprices where to_char(downloaddate)=ndownloaddate;

Begin

ndownloaddate='01-DEC-92'

Open GetSedol;

Loop

 fetch GetSedol into nsedol,nPrice;

        exit when GetSedol%notfound;

 update t_master_download
 set
 price=nprice
 where sedol=nsedol
 and to_char(downloaddate)=ndownloaddate;

End Loop;

 close GetSedol;

 commit;

end;

The 'downloaddate' is set to '01-DEC-92'. We intend to update every months worth of data right up to '01-DEC-98'. Each month contains about 3270 rows.

How can I increase the performance of this update? Is it possible to use cursors to better effect, to achieve a performance gain?

Any help would be greatly appreciated.

Terry Maguire
IIU
IFSC House
Custom House Quay
Dublin 1
Ireland Received on Mon Oct 18 1999 - 06:02:46 CDT