Re: Best practice question: Updating Aggregates

casey.kirkpatrick_at_gmail.com wrote:
> Dan - sorry for the delay. I have to say - your response seems odd to
> say the least.
>
> There are 100 rows which can be grouped into a total of 20 printable
> records. Given my goal (print the 20 records and update the 100 rows),
> what is the best practice for reliable, maintainable code?
>
> Are you suggesting that for the 100 row table, I should BULK COLLECT 10
> rows at a time (or whatever is most efficient), store the values of
> those 10 rows into a local array, update those 10 rows with a UPDATE
> FORALL statement, and then fetch the next 10 rows; etc? Then, after
> getting all 100 rows into this local array, I can write in PL/SQL all
> of the logic needed to group the data (all 100 rows) into the 20
> printable records (perhaps in an aggregate local array?), and THEN loop
> through the aggregate array to print the grouped data?
>
> If not - please, do explain your exact method for using BULK COLLECT to
> address the problem at hand.

Sorry for my delay. I was just in Washington DC at NASA.

You should definitely use BULK COLLECT to grab the records but with only 100 rows an arraysize of 100 would be reasonable so ... a single fetch and write with a single FORALL.

With a larger number of records, tens of thousands or more, I'd still suggest an array size somewhere in the 100 - 500 records but the only true arbiter of the fetch size is testing.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)