SQL & PL/SQL

Hi all,

I would like your opinions please.

This may sound straight forward but I'm desperately trying to improve the performance of a PL/SQL routine. Within the routine there is some processing that definitely requires PL/SQL (so I can't just use SQL) but the general purpose of the procedure is to take and existing set of balances for each account and convert them to a new style of balances with different categories and insert them back into the balance table.

Currently there are over 6000000 rows in the balance table and the vast majority of the new style balances are a SUM of some of the old style balances.

I have referred to some PL/SQL performance literature and have come across articles explaining the benefits of BULK COLLECT and FORALL which I will experiment with.

In general, would performance be better if I used the SUM and GROUP BY to transform the balances or should I load all 6000000 rows into a PL/SQL table and LOOP through the totals and create the balances as I go along? (Or something else perhaps?)

Any opinions and suggestions will be gratefully received.

Thanks in advance all.