Re: MOST EFFICIENT BULK UPDATE?

druanthia_at_hotmail.com (Michael O) wrote in message 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?

FORALL isn't likely to help you here-- you'd have to get the data from your select statement into collections (BULK COLLECT) and then insert from those collections into the table. That's going to be less efficient than the single SQL statement you have.

> 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');

My first thought would be to look into analytic functions to do the sum. Assuming you have 8.1.6? or above, that should give you a decent performance boost. Since you didn't specify your indexes, there may be indexes that could be helpful as well.

Justin Cave Received on Thu Oct 31 2002 - 12:16:13 CST