Re: Convert PLSQL update with forall
Date: Sun, 20 May 2018 18:16:20 +0100
Message-ID: <CACQ9E3spfPgguHAL8bjhd-VhAJcwO4ahjpbDUiS-UnYS8WC7DQ_at_mail.gmail.com>
2 ways.
> Hello
1. Split the collection into two and do for all on each of them
2. Push the where clause into where clause of the updates.
The difference is that in option 1 you don't do filtering of collection in
SQL.
On Sun, 20 May 2018, 16:43 amonte, <ax.mount_at_gmail.com> wrote:
>
> 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-lReceived on Sun May 20 2018 - 19:16:20 CEST