用于更新超过1000 000行的Pl/Sql脚本

Question

我有一个表：

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

该程序应按优先顺序从特定组织的工资中扣除国旗。上面的结果如下。结果：

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

我为此创建了Pl/sql块，但在一百万条记录上速度太慢了。做这件事最快的方法是什么？

Answer 1

此处不需要PL/SQL。SQL有足够的能力做到这一点，并且应该足够快：

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;

澄清一下：USING子句中的查询是通过使用窗口函数计算组织名称内的移动总额来获得更新的工资。然后我们将其合并到原始表中。

Answer 2

要加快速度，请尝试在PL/SQL代码中使用BULK COLLECT和FORALL选项，或者如上所述，在SQL中执行。