Pl/Sql Script for update more than 1 000 000 rows

Question

I have a table:

id| name | organisation_name|flag  |priority|salary
1 | Mark | organisation 1   |null  |1       |100.00
2 | Inna | organisation 1   |null  |2       |400.00
3 | Marry| organisation 1   |null  |3       |500.00
4 | null | organisation 1   |250.00|null    |null
5 | Grey | organisation 2   |null  |1       |600.00
6 | Holly| organisation 2   |null  |2       |400.00
8 | null | organisation 2   |150.00|null

The procedure should deduct the flag from the salary of a particular organization by priority. The result for this above is below. Result:

id| name | organisation_name|flag  |priority|salary
1 | Mark | organisation 1   |null  |1       |0.00
2 | Inna | organisation 1   |null  |2       |250.00
3 | Marry| organisation 1   |null  |3       |500.00
4 | null | organisation 1   |250.00|null    |null
5 | Grey | organisation 2   |null  |1       |450.00
6 | Holly| organisation 2   |null  |2       |400.00
8 | null | organisation 2   |150.00|null

I created Pl/sql block for this, but is so slow on one million records. What is the fastest way to do this?

Answer 1

No need for PL/SQL here. SQL has sufficient capabilities to do this and should be fast enough:

MERGE INTO orgs o
USING (SELECT o.id,
              greatest(o.salary - greatest(0, f.flag - nvl(sum(o.salary) over (partition by o.organisation_name order by o.priority rows between unbounded preceding and 1 preceding), 0)), 0) as salary
         FROM orgs o
         LEFT JOIN (SELECT organisation_name, flag FROM orgs WHERE flag IS NOT NULL) f
                ON (f.organisation_name = o.organisation_name)
        WHERE o.priority IS NOT NULL) f
   ON (f.id = o.id)
 WHEN MATCHED THEN UPDATE SET o.salary = f.salary;

For clarification: the query in USING clause is obtaining updated salaries by using window function to calculate moving total within the organisation name. Then we merge it into original table.

Answer 2

To speed it up try using BULK COLLECT and FORALL options in your PL/SQL code, or as mentioned above, do it in SQL.