Re: How do I perform fast updates?

From: Sybrand Bakker <postmaster_at_sybrandb.demon.nl>
Date: Mon, 18 Oct 1999 14:37:32 +0200
Message-ID: <940250269.26680.0.pluto.d4ee154e@news.demon.nl>

To the second question: no, that would hardly make any difference. You could actually (and should probably) accomplish this in one statement. But first you need to address a generic issue, which is why the procedure is slow.
Assuming downloaddate is a proper date column and it is suitably indexed, by using a to_char on it, you force the optimizer to ignore the index. You will get a full table scan. This applies to both statements you are using, and of course the second is worse.
You need at least to replace to_char(downloaddate) by downloaddate = to_date(ndownloaddate)

To do this in 1 statement (and make sure everything is being done IN the database and not outside)

use
update t_master_download t
set price = (select price from newprices n

                  where n.sedol = t.sedol
                   and    n.downloaddate = t.downloaddate)

where exists
(select 'x'
 from newprices n1
 where n1.sedol = t.sedol
 and n1.downloaddate = t.downloaddate  and n1.downloaddate = to_date('01-DEC-92','DD-MON-YY')

Hth,

--
Sybrand Bakker, Oracle DBA

Terry Maguire <tmaguire_at_iiu.nospam.ie> wrote in message news:7uev8c$i5a$1_at_ezekiel.eunet.ie...
> 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 - 07:37:32 CDT