Re: Convert PLSQL update with forall

Hello Dominic

I understand a single sql statement can be quicker but there is no way. I have simplify the code but it is more complex than that, more logics involved that is why I am looking FORALL optimization. But are you saying running 2 million updates is going to be slower than 4000 uodate (considering arraysize of 500)?

Thank you

2018-05-20 19:16 GMT+02:00 Dominic Brooks <dombrooks_at_hotmail.com>:

> In principle, there is no reason why a FORALL would make this quicker.
> Fetching data from sql into plsql to then drive other sql should be avoided.
>
> Best approach *should* be a single MERGE statement, no loops.
>
> But there are always caveats and exceptions.
>
> Cheers
> Dominic
>
> Sent from my iPhone
>
> > On 20 May 2018, at 17:37, amonte <ax.mount_at_gmail.com> wrote:
> >
> > Hello
> >
> > I have a plsql procedure which contains a complex cursor and a couple of
> update statements using the cursor output, it looks like
> >
> >
> > begin
> >
> > for indx in (complex query)
> > loop
> >
> > if indx.c10 = 'NO' then
> > update t1
> > set c3 = c2 + c4
> > where c1 = indx.c1
> > and c2 = indx.c2;
> > else
> > update t1
> > set c5 = c7 + c8
> > where c1 = indx.c1
> > and c2 = indx.c2
> > end if;
> >
> > end loop;
> > end;
> > /
> >
> > The cursor (join of 7 tabls and a few EXISTS subqueries) returns
> aproximately 2 million rows and the process is not as fast as desired. I
> was looking into FORALL to improve this procedure but I cannot find a way
> due to the conditional updates (two different update statements). Anyone's
> got an idea if FORALL can be implemented in such situations?
> >
> > Thank you very much
> >
> > Alex
> >
>

--
http://www.freelists.org/webpage/oracle-l