Re: How can I do it optimally ?

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.

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;

Obviously rough but it should work.

--
Michael Krolewski
Rosetta Inpharmatics
mkrolewski_at_rii.com
              Usual disclaimers


Sent via Deja.com
http://www.deja.com/