Re: MOST EFFICIENT BULK UPDATE?

why it is slow ...

• because the high latency ( seq read wait )
• because wait for log buffer
• because lag of dbwrite ( to much dirty blocks )

in all cases forall will not help

in most casces forall with update will never help ...

because you wait for the read of blocks in cache

you can save microseconds with forall and you loose a lot of milliseconds with every wait for single block read

so you need to reduce the single block reads

this is difficult

first you need to identify on which objects you have the most waits

you can save waits with huge buffer cache, but you need to read every block at least once !!!

read 1 mio blocks with 10ms access time => 10000 seconds and you can't save one sec!!

you can work in update with a cursor which selects the data in the buffercache
and then you update with rowid

than you can time the cursor and measure the single block reads this data should stay in the buffer cache for changing the blocks

and then measure the additional work with update ( for updating indexes which are changed )

it is very difficult to optimize big updates, but forall won't help in most cases ...

"Michael O" <druanthia_at_hotmail.com> schrieb im Newsbeitrag news:734b0477.0210310010.574a6789_at_posting.google.com...
> Hello. I have a table in a Data Warehouse (doc_sumr01) which contains
> records that are each a single line of a multi-line document. Each
> document has a document number (doc_nbr) and the line item value
> (doc_val), as well as an empty summary field (doc_sum_val). There are
> about a million records in this table. I want to sum up the total
> value of each document and put the summed value into the (doc_sum_val)
> field for each record (don't even think about non-normalization
> issues, this is a data warehouse used for reporting). I have created
> a self-joining update statement, but this is as slow as our White
> House pResident. So I am seeking an alternative, possibly using
> FORALL. Any suggestions?
>
> MY TABLE (simplified):
>
> DOC_KEY NOT NULL NUMBER
> DOC_CODE NOT NULL VARCHAR2(2)
> DOC_NBR NOT NULL VARCHAR2(10)
> DOC_SUM_VAL NOT NULL NUMBER(15,2)
> DOC_VAL NOT NULL NUMBER(15,2)
>
>
> SAMPLE VALUES:
>
> DOC_KEY DOC_CODE DOC_NBR DOC_SUM_VAL DOC_VAL
> 1 OA 100001 0 5.00
> 2 OA 100001 0 10.00
> 3 OA 100005 0 12.00
> 4 OA 100005 0 13.00
> 5 OA 100007 0 20.00
>
>
> EXPECTED VALUES AFTER UPDATE:
> DOC_KEY DOC_CODE DOC_NBR DOC_SUM_VAL DOC_VAL
> 1 OA 100001 15.00 5.00
> 2 OA 100001 15.00 10.00
> 3 OA 100005 25.00 12.00
> 4 OA 100005 25.00 13.00
> 5 OA 100007 20.00 20.00
>
>
> SAMPLE UPDATE STATEMENT:
>
> UPDATE doc_sumr01 ds1
> SET doc_sum_val = (
> SELECT SUM(ds2.doc_val)
> FROM doc_sumr01 ds2
> WHERE ds2.doc_nbr = ds1.doc_nbr
> AND ds2.doc_code = 'OA');
>
>
> ANY HELP IS APPRECIATED!!!
Received on Thu Oct 31 2002 - 09:36:25 CST