Re: How can I do it optimally ?

In article <94aulc$m1l$1_at_nnrp1.deja.com>,   Mike Krolewski <mkrolewski_at_rii.com> wrote:
> In article <949c74$1ng$1_at_news.ukrsotsbank.com>,
> "luba_at_usb" <luba_at_usb.ua> wrote:
> > How can I do it optimally ?
> > update Tab1
> > set Tab1.field2=(
> > select sum (decode (field1,1,field2,0)+decode
 (field1,2,field3,0))
> > from Tab1 where field1 in (1,2))
> > where field1=1 ;
> >
> >
>
> Assuming you really want the sum(..) across all row of this table, the
> optimal idea is to do the 'select sum(...)', separately then perform a
> separate update. I could be wrong but I believe this sum would be
> evaluated at each row, not the best plan. It will get the correct
> results as the table is not updated until the commit happens.

Wrong. Table is updated. And current session can see all updates right after they happen. Commit just makes all changes visible for all other sessions.

>
> Doing one row at a time would not work as the sum would be
 continuously
> changing.
>
> A solution:
>
> declare
> firstSum number;
> begin
> select sum(... ) into firstSum from ...;
> update tab1 set field2 = firstSum where field1 = 1;
> end;
>
> Obviously rough but it should work.
> --
> Michael Krolewski
> Rosetta Inpharmatics
> mkrolewski_at_rii.com
> Usual disclaimers
>
> Sent via Deja.com
> http://www.deja.com/
>

Sent via Deja.com
http://www.deja.com/ Received on Sun Jan 21 2001 - 20:58:27 CST